CODEWITHSUNDEEP
c#sqldatadapter
Mhmt
Mhmt

Reputation: 769

Why I get Incorrect syntax near '=' error

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();

Upvotes: 0

Views: 737

Answers (4)

Savaratkar
Savaratkar

Reputation: 2084

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*.

Upvotes: 0

King King
King King

Reputation: 63317

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());

Upvotes: 3

p.s.w.g
p.s.w.g

Reputation: 149020

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.

Upvotes: 5

Simon Whitehead
Simon Whitehead

Reputation: 65079

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();

Upvotes: 2

Related Questions