Khalid Al-anesi Khalid Al-anesi - 6 months ago 13
SQL Question

How to make a search query for two values in SQL Server

I made a C# form to make a search on two values in one table. My table is called

customers
with string
ID
and string
cust_name
.

I need to make a search query that looks for the textbox Text either is found in
ID
or in
cust_name
, so I made this SQL query when textChanged sends this method

search(txt_search.Text);
SqlDataAdapter searchAdapter;

private void search(string id)
{
searchAdapter = new SqlDataAdapter(@"Select * from Customers
where cust_ID like '%' '" + id + "' '%' or
cust_name like '%' '" + id + "' '%'", User.connection);
}


Please help me make it right..

Answer

As usual, use a parameterized query. Your error is in the concatenation of the string parts that makes your query. And it is a common situation that something is not as it should be. In your particular case there are some spaces that mess up the syntax. Anyway parameters allow a clearer query text, avoid Sql Injection and parsing errors.

private void search(string id)
{
    string cmdText = @"Select * 
                       from Customers 
                       where cust_ID like @id or 
                             cust_name like @id";
    searchAdapter = new SqlDataAdapter(cmdText, User.connection);
    searchAdapter.SelectCommand.Parameters.Add("@id", SqlDbType.NVarChar).Value = "%" + id + "%";

    ... remainder of the code that uses the searchAdapter....
}
Comments