2

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.

Improve this question
3
  • Why do people insist on using DataTable etc? Think of the kittens! Also: never ever concatenate input... Commented May 15, 2014 at 9:26
  • 1
    yes, very bad joo joo. Your uni lecturers deserve to be mocked. Leave the Jews alone, though ;p Commented May 15, 2014 at 9:31
  • haha I was just sounding it out! Commented May 15, 2014 at 9:53

5 Answers 5

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

save it me from writing..was just finishing
Thanks for the reply. I would like to learn good habits here so I will try to use the SqlCommand with parameters. How would I execute the query in the button event handler?
I changed my code already, place it in the event of the button you prefer.
@Abbas correct me if I am wrong. Position should be [Position] in the query.
You should probably open a new question regarding this, including the code and the exception + message that is thrown. This exception is off-topic regarding to your original question. I'll be glad to help you further there.
|
1

As makambi already said. You shouldnt do it like that because you are opening doors for SQL injection. Rather use parametrized queries like this:

SqlCommand cmd = new SqlCommand("SELECT employeeID, name, position, hourlyPayRate FROM dbo.employee WHERE name LIKE @name", connection);

cmd.Parameters.Add("@name", textBox1.Text);
connection.Open();
cmd.ExecuteNonQuery();

or use a stored proceedures.

Improve this answer

Comments

1

As a minor aside; here's an altenative approach that shows how to do it avoiding the overhead of DataTable etc, and with simple but safe and efficient parameterization and materialization:

public class Employee {
    public int EmployeeID {get;set;}
    public string Name {get;set;}
    public string Position {get;set;}
    public decimal HourlyPayRate {get;set;}
}
...
// uses the "dapper" tool to provide the Query<T> extension method;
// freely available from NuGet: PM> Install-Package Dapper
var employees = connection.Query<Employee>(@"
SELECT employeeID, name, position, hourlyPayRate 
FROM dbo.employee 
WHERE name LIKE @pattern",
    new { pattern = "%" + textBox1.Text + "%" }).ToList();

grid.DataSource = employees;
Improve this answer

Comments

0

The where clause should be WHERE name LIKE textBox1.text + 'rest of query'

But as makambi mentions, the way your'e building the query open you up to sql injection

Improve this answer

1 Comment

what do you mean by 'rest of query' that would be the end would it not?
0

If I understood right, you manually build your sql query. It's a bad practice to do it with plain string concatenation because it makes possible SQL injections, instead you should use SqlCommand syntax and just add SqlParameters in it.

   using (var sqlConnection = new SqlConnection("connection"))
   {
       var commandText = "SELECT employeeID, name, position, hourlyPayRate FROM dbo.employee WHERE name LIKE @name";

       using (var sqlCommand = new SqlCommand(commandText, sqlConnection))
       {
             sqlCommand.Parameters.Add("@salary", SqlDbType.Money).Value = textBox1.text;
       }
   }

It's not actually tested, and it might not work as it is, but it describes and idea. You just add parameters with sql type you need, please see more examples here: When should "SqlDbType" and "size" be used when adding SqlCommand Parameters?

Improve this answer

1 Comment

I think this should better be a comment

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.