ASP.NET SqlDataReader Incorrect syntax near

Question

I've had an ASP.NET page that had worked for quite a while, up until recently. The page contains a single text box (TextBox1) and a submit button. When you input (or scan) a number into the field and submit it, if the record exists in the database and hasn't been submitted before, it adds a date/time stamp to another column and gives the user feedback that it's been recorded. If the record exists and already had a date/time stamp, it doesn't change anything but gives the user feedback that the record already has been input or scanned. If the record doesn't exist, it gives the user feedback that there is no such record.

This all worked fine when I was inputting numerical values. Now, the numeric values have changed to alphanumeric and I'm getting and error. Anytime I input a value that is alphanumeric, I get an

Incorrect syntax near 'x'

error that refers to line 35:

using(SqlDataReader reader = command.ExecuteReader())

My entire code from my aspx.cs file is below. Any suggestions are greatly appreciated!

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    private string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection connection = new SqlConnection(GetConnectionString()))
        {
            try
            {
                connection.Open();

                string sql = @"SELECT PRODUCT_ID from PRODUCTS where PRODUCT_ID = " + TextBox1.Text + " and DATE is null";

                using(SqlCommand command = new SqlCommand(sql, connection))
                {
                    using(SqlDataReader reader = command.ExecuteReader())
                    {
                        if(reader.HasRows)
                        {
                            string sql2 = @"UPDATE [products] SET date=@Value2 where PRODUCT_ID=@Value1";
                            using (SqlCommand command2 = new SqlCommand(sql2, connection))
                            {
                                command2.Parameters.AddWithValue("@Value1", TextBox1.Text);
                                command2.Parameters.AddWithValue("@Value2", DateTime.Now);
                                command2.ExecuteNonQuery();
                            }
                            pageBody.Attributes.Add("bgcolor", "#9aff8e");
                            Label1.Text = "Item " + TextBox1.Text + " Recorded!";
                            TextBox1.Text = "";
                        }
                        else
                        {
                            reader.Close();
                            string sql3 = @"SELECT PRODUCT_ID from PRODUCTS where PRODUCT_ID = " + TextBox1.Text + "";

                            using(SqlCommand command3 = new SqlCommand(sql3, connection))
                            {
                                using(SqlDataReader reader2 = command3.ExecuteReader())
                                {
                                    if (reader2.HasRows)
                                    {
                                        pageBody.Attributes.Add("bgcolor", "#fbff8e");
                                        Label1.Text = "Item " + TextBox1.Text + " Already Shipped!";
                                        TextBox1.Text = "";
                                    }
                                    else
                                    {
                                        pageBody.Attributes.Add("bgcolor", "#ff8e8e");
                                        Label1.Text = "Item " + TextBox1.Text + " Not Found!";
                                        TextBox1.Text = "";
                                    }
                                }
                            }
                        }
                    }
                }
            }
            finally
            {
                if(connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }
    }
}

The error should be caused by this line [string sql = @"SELECT PRODUCT_ID from PRODUCTS where PRODUCT_ID = " + TextBox1.Text + " and DATE is null";]. If ProductID.Text is one string like 'X', the 'where' clause should be [PRODUCT_ID='XXX' instead of Product_ID=XXX] — Sphinx
– Sphinx, Commented Feb 20, 2018 at 16:59
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Feb 20, 2018 at 18:15

Faruq · Accepted Answer · 2018-02-20 17:06:15Z

4

First of all: Never do string concatenation for SQL with user input. It opens up risk for Sql Injection which can destroy your database.

The error is due to the change in datatype of PRODUCT_ID from number to string. Add ' to fix the error.

string sql = @"SELECT PRODUCT_ID from PRODUCTS where PRODUCT_ID = '@Value1' and DATE is null";
using(SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@Value1", TextBox1.Text);
    ... 
}

answered Feb 20, 2018 at 17:06

Faruq

1,5311 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

G_S · Accepted Answer · 2018-02-20 18:03:10Z

2

I hope since you are inputing a alphanumeric field, you have to use. (Note the quotes beside textbox text )

string sql = @"SELECT PRODUCT_ID from PRODUCTS where PRODUCT_ID = '" + TextBox1.Text + "' and DATE is null";

As you are saying its a alphanumeric field, you have to search your product_id by enclosing it as a string.

(Assuming datatype of PRODUCT_ID in your table is varchar. If your datatype is not VARCHAR, you might still see an error )

And yes, As @Faruq mentioned, make sure to update your code to use command parameters to avoid SQL injections.

edited Feb 20, 2018 at 18:03

answered Feb 20, 2018 at 16:59

G_S

7,1302 gold badges27 silver badges54 bronze badges

1 Comment

scedsh8919 Over a year ago

That did it! I could've swore I tried that, but I must've been misplacing the quotes. Thanks a lot!

JBrooks · Accepted Answer · 2018-02-20 17:02:16Z

0

Change:

PRODUCT_ID = " + TextBox1.Text + "

TO:

PRODUCT_ID = '" + TextBox1.Text + "'

You need to quote the text, so abc should be 'abc' when it gets to the database.

answered Feb 20, 2018 at 17:02

JBrooks

10k2 gold badges31 silver badges32 bronze badges

1 Comment

scedsh8919 Over a year ago

That did it! I could've swore I tried that, but I must've been misplacing the quotes. Thanks a lot!

Collectives™ on Stack Overflow

ASP.NET SqlDataReader Incorrect syntax near

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related