CODEWITHSUNDEEP
c#sql-server
Sin Oscuras
Sin Oscuras

Reputation: 33

SqlException: Incorrect syntax near the keyword 'AND'

I'm making a management program with C# & SQL Server 2008. I want to search records using Blood Group, District & Club Name wise all at a time. This is what is making prob:

    SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Table2 
    WHERE @Blood_Group =" + tsblood.Text + "AND @District =" + tsdist.Text + 
    "AND  Club_Name =" + tscname.Text, Mycon1);

Can anyone tell me what is the correct syntax? Tnx in advance. :)

Upvotes: 3

Views: 4629

Answers (2)

Ian Boyd
Ian Boyd

Reputation: 256891

You forgot an AND (and possible an @ in front of Club_Name?):

String CRLF = "\r\n";

String sql = String.Format(
      "SELECT * FROM Table2" + CRLF+
      "WHERE @Blood_Group = {0}" + CRLF+
      "AND @District = {1} " + CRLF+
      "AND Club_Name = {2}", 
      SqlUtils.QuotedStr(tsblood.Text), 
      SqlUtils.QuotedStr(tsdist.Text),
      SqlUtils.QuotedStr(tscname.Text));

SqlDataAdapter sda = new SqlDataAdapter(sql, Mycon1);

Upvotes: 1

Darin Dimitrov
Darin Dimitrov

Reputation: 1039110

The correct syntax is to use parametrized queries and absolutely never use string concatenations when building a SQL query:

string query = "SELECT * FROM Table2 WHERE BloodGroup = @BloodGroup AND District = @District AND Club_Name = @ClubName";
using (SqlDataAdapter sda = new SqlDataAdapter(query, Mycon1))
{
    sda.SelectCommand.Parameters.AddWithValue("@BloodGroup", tsblood.Text);
    sda.SelectCommand.Parameters.AddWithValue("@District", tsdist.Text);
    sda.SelectCommand.Parameters.AddWithValue("@ClubName", tscname.Text);
    ...
}

This way your parameters will be properly encoded and your code not vulnerable to SQL injection attacks. Checkout bobby tables.

Also notice how I have wrapped IDisposable resources such as a SqlDataAdapter into a using statement to ensure that it is properly disposed even in case of an exception and that your program will not be leaking unmanaged handles.

Upvotes: 7

Related Questions