Why I get Incorrect syntax near '=' error

Question

I tried bind a label from datatable

I get this error

  Incorrect syntax near '='.

at this line

 da.Fill(dt);

My code : Page_Load

LbLID.Text =this.Page.Request.QueryString["DI"].ToString();

 SqlConnection con = new SqlConnection("Data Source=local;Initial Catalog=DB;User 
 ID=sa;Password="pass);
 SqlDataAdapter da = new SqlDataAdapter("select * from Table1 where ID= " + 
 LbLID.Text.Trim(), con);

 System.Data.DataTable dt = new System.Data.DataTable();
 da.Fill(dt);
 lblS1.Text = dt.Rows[0][4].ToString();
 lblS1.DataBind();

Answer 1

You can't break normal string literals across multiple lines, also your closing quote is misplaced:

SqlConnection con = new SqlConnection("Data Source=local;Initial Catalog=DB;User ID=sa;Password=pass");

Or use a verbatim literal, which you can break across multiple lines:

SqlConnection con = new SqlConnection(
    @"Data Source=local;
      Initial Catalog=DB;
      User ID=sa;
      Password=pass");

That said, your code is vulnerable to SQL injection attacks. For your own sake, and the sake of your users, you really should use parameterized queries instead of concatenating your SQL queries like that.

Here's a quick example:

using(var con = new SqlConnection(...))
{
    var cmd = new SqlCommand("select * from Table1 where ID = @ID", con);
    con.Open();
    cmd.Parameters.AddWithValue("@ID", LbLID.Text.Trim());
    var da = new SqlDataAdapter(cmd);
    var dt = new DataTable();
    da.Fill(dt);
    lblS1.Text = dt.Rows[0][4].ToString();
    lblS1.DataBind();
}

Some other tips: You should avoid using select * queries, since your database schema might change, and that would break any existing code. It would be better to select only the column you're interested in and make a simple call to ExecuteScalar.

Answer 2

Try this:

SqlDataAdapter da = new SqlDataAdapter("select * from Table1 where ID ='" + 
                        LbLID.Text.Trim() + "'", con);

However note that it a very bad code which is vulnerable to sql injection.

So you should try this:

var com = new SqlCommand("SELECT * FROM Table1 WHERE ID=@id", con);
com.Parameters.AddWithValue("id",LBLID.Text.Trim());
var da = new SqlDataAdapter(com);

Or shorter:

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Table1 WHERE ID=@id", con);
da.SelectCommand.AddWithValue("id",LBLID.Text.Trim());

Answer 3

Its a SQL error. You aren't passing in a valid ID.

It's one of two things.

Option A: Your ID is a string. In which case.. you need to use single quotes:

SqlDataAdapter da = new SqlDataAdapter("select * from Table1 where ID= '" + LbLID.Text.Trim() + "'", con);

Option B: Your LbLId is wrong.. you're checking for ["DI"] .. when I think it should be ["ID"]:

LbLID.Text =this.Page.Request.QueryString["ID"].ToString();

Answer 4

I do not see any problem in the code at da.Fill(). But I see another issue at the following statement:

SqlConnection con = new SqlConnection("Data Source=local;Initial Catalog=DB;User 
 ID=sa;Password="pass);

...Password="pass); - rather is should be

...Password=" + pass);

OR

...Password=pass");

I am wondering you are not getting an undefined variable error for pass*.