C# Incorrect syntax near '1'

Question

I saw below error, when I keyed Test 1 into sNtxtbox. Any help would be appreciated

Incorrect syntax near '1'

private void flexOpen_Click(object sender, RoutedEventArgs e)
    {
        using (SqlConnection sqlConSN = new SqlConnection(SNconnectionstring))
        {
            sqlConSN.Open();
            String sqlSelectQuerySN = ("Select * FROM tblSN WHERE SerialNumber = " + sNtxtbox.Text);
            SqlCommand sqlCmdSNLoad = new SqlCommand(sqlSelectQuerySN, sqlConSN);
            SqlDataReader drSNReader = sqlCmdSNLoad.ExecuteReader();
            if (drSNReader.Read())
            {
                SNtest1.Text = (drSNReader["Test1"].ToString());
                SNtest2.Text = (drSNReader["Test2"].ToString());
                SNtest3.Text = (drSNReader["Test3"].ToString());    
                SNtest4.Text = (drSNReader["Test4"].ToString());
                SNtest5.Text = (drSNReader["Test5"].ToString());
                SNtest6.Text = (drSNReader["Test6"].ToString());
                SNtest7.Text = (drSNReader["Test7"].ToString());
                SNtest8.Text = (drSNReader["Test8"].ToString());

PLEASE don't do this way! This is invitation for sql injection. And here the problem is you need to concatenate qoutes in the query. — Sankar, Jun 14 '18 at 06:33
1. Post your code & exception as formatted text not as screenshot. 2. Your SQL is invalid — Freggar, Jun 14 '18 at 06:34
if your column is from data type string it should be `WHERE SerialNumber = '1'` — styx, Jun 14 '18 at 06:36
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Jun 14 '18 at 07:29

ProgrammingLlama · Answer 1 · 2018-06-14T06:53:05.150

Assuming your database field is a string, you should rewrite your code like so:

String sqlSelectQuerySN = ("Select * FROM tblSN WHERE SerialNumber = @serialNumber");
SqlCommand sqlCmdSNLoad = new SqlCommand(sqlSelectQuerySN, sqlConSN);
sqlCmdSNLoad.Parameters.Add("@serialNumber", System.Data.SqlDbType.VarChar, 255).Value = sNtxtbox.Text;

Note that you should replace VarChar with the DB field type. I've assumed that you're using a VarChar. If you're using an NVarChar, for example, you should change it to reflect your database schema. Also, replace 255 with the length of your VarChar.

You don't need to pass the length, but not passing the length means that .NET will assume the length of the database field is the same as the length you're passing. So SQL server will see a query with a serial number of length 5, as different to a query with a serial number of length 6 because they'll be passed through as VarChar(5) and VarChar(6) respectively. Each will have its own execution plan, which means it can't leverage caching to speed up your query. See here and here for more information.

This will solve your problem and have the added benefit of protecting you from SQL injection by using parameterized queries.

thank you very much. it fixed the problem! – user9939859 Jun 18 '18 at 01:23 — user9939859, Jun 18 '18 at 01:23

Mandar Dhadve · Answer 2 · 2018-06-14T07:26:46.080

1

Replace this

String sqlSelectQuerySN = ("Select * FROM tblSN WHERE SerialNumber = " + sNtxtbox.Text);

To this:

SqlCommand sqlcmd = ("Select * FROM tblSN WHERE SerialNumber=@SerialNumber",sqlConSN);
sqlcmd.Parameters.AddWithValue("@SerialNumber",sNtxtbox.Text);

edited Jun 14 '18 at 07:26

answered Jun 14 '18 at 06:43

Mandar Dhadve

371
2
12

You shouldn't use `AddWithValue` with strings because you can get as many execution plans for as many strings you pass, which means your queries will be slower. See [here](http://blogs.microsoft.co.il/egoldin/2014/07/02/ado-net-sql-parameters-avoid-addwithvalue-for-strings/) and [here](http://www.dbdelta.com/addwithvalue-is-evil/). – ProgrammingLlama Jun 14 '18 at 06:50

Vince Alfred Perez · Answer 3 · 2018-06-17T15:21:33.017

-1

String sqlSelectQuerySN = "Select * FROM tblSN WHERE SerialNumber = '" + sNtxtbox.Text + "'";

But I suggest you to use parameters like the one listed above..

edited Jun 17 '18 at 15:21

answered Jun 14 '18 at 07:04

Vince Alfred Perez

19
2

C# Incorrect syntax near '1'

3 Answers3