What is the reason for my SqlException: Incorrect syntax near '='?

Question

This is my code:

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "data source = LAPTOP-ULT25NKH; database = college;integrated security = True";

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;

    cmd.CommandText = "select * from teacher where tID = " + textBox1.Text + "";

    DataSet DS = new DataSet();
    SqlDataAdapter DA = new SqlDataAdapter(cmd);
    DA.Fill(DS);

    dataGridView1.DataSource = DS.Tables[0];
}

but I get this exception:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Incorrect syntax near '='."

Add a try catch and see what exactly the exception gives the details — Vivek Nuna, Oct 03 '21 at 19:18
What exactly is the value of TextBox1.Text? What is the data type of that tID column? — Hans Keﬆing, Oct 03 '21 at 19:42
SQL injection is not just about *security* but also about *correctness*, it would prevent such syntax errors. Side note: you need `using` blocks for the connection and adapter objects. You can use `DataTable.Load` and obviate the need for a `DataSet` — Charlieface, Oct 03 '21 at 20:08

Brendan Lesniak · Answer 1 · 2021-10-04T16:30:17.930

3

Ensure you are properly santizing inputs and using prepared statements; to start down the line for you, try:

cmd.CommandText = "SELECT * FROM teacher WHERE tID = @tID;"
SqlParameter idParam = new SqlParameter("@tID", SqlDbType.NVarChar , 0);
idParam.Value = textBox1.Text;
cmd.Parameters.Add(idParam);
cmd.Prepare();

edited Oct 04 '21 at 16:30

answered Oct 03 '21 at 19:34

Brendan Lesniak

2,271
4
24
48

score 0 · Answer 2 · answered Oct 03 '21 at 19:37

There are lot of issues in your existing code, I’m mentioning few points brlow.

Please move the connection string to some config file, it’s easy to maintain there.
When you have DataAdapter you don’t need to explicitly open the connection, it does that for you internally.
Please avoid * in select query, mention the columns with alias and use parameterized query to pass the parameters. Or your can write stored procedure and call it. So that I if I’m future you need to modify query, there will be no code change.
If you need to open the connection, please close it or your can use using.
You can add breakpoint and see the value of your query and if you copy this query value and run in sql server directly . This is one way to find the error in the query.

What is the reason for my SqlException: Incorrect syntax near '='?

2 Answers2