Include textbox value in SQL Query

Question

I am trying to search a GridView using a textbox and search button. I think I need a query something like

SELECT employeeID, name, position, hourlyPayRate 
FROM dbo.employee 
WHERE name LIKE 'textBox1.text+'

I have made the query using the query designer in visual studio 2013.

I then have an event handler like this

private void btnSearch_Click(object sender, EventArgs e)
{            
    this.employeeTableAdapter.FillBy(this.personnelDataSet.employee); 
}

I am sure that the problem is in the query but I just don't know how to include the value of the textbox into the query.

Answer 1

To just change your query, it should look like:

string textboxValue = textbox1.Text;
string query = "SELECT employeeID, name, position, hourlyPayRate " +
               "FROM dbo.employee " +
               "WHERE name LIKE '" + textboxValue + "'";

But this is vulnerable to SQL injection, you should use a SqlCommand with parameters:

string commandText = "SELECT employeeID, name, position, hourlyPayRate " +
                     "FROM dbo.employee WHERE name LIKE '%'+ @Name + '%'";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    //Create a SqlCommand instance
    SqlCommand command = new SqlCommand(commandText, connection);
    //Add the parameter
    command.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = textbox1.Text;

    //Execute the query
    try
    {
        connection.Open();
        command.ExecuteNonQuery();
    }
    catch
    {
        //Handle exception, show message to user...
    }
    finally
    {
        connection.Close(); 
    }
}

Update:

To execute this code on the click of a button, place the code here (Make sure youi have a button with name YourButton):

private void YourButton_Click(object sender, EventArgs e)
{
    //Place above code here
}

Update2:

You should have a connection string to use in the SqlConnection, this string might look something like:

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

Here, you have to replace the values that start with my with your own values. More info/examples on connectionstrings for SQL Server:

• SQL Server connection strings