Incorrect syntax near '='. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception

Question

i have problem, when count the quantity column value where its email, the email i got from session["email"], before i do try and catch there error at int count = (int)cmd.ExecuteScalar();, this their error message "Incorrect syntax near '='. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception". i need to count number of quantity from my table. data type for menu_quantity is float.

 protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["email"] != null)
        {
            A();
            //lbltotalitemcart.Text = A().ToString();
        }
        else
        {
            lbltotalitemcart.Text = "login email first";
        }

    }

 public int A()
    {
        String email = Request.QueryString["email"];
        string stmt = "SELECT COUNT(menu_quantity) FROM cart Where email=" + email + "";
        try
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.
           ConnectionStrings["connectionString"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(stmt, con))
                {
                    con.Open();
                    int count = (int)cmd.ExecuteScalar();
                    return count;
                }
            }
        }
        catch (Exception e)
        {
            lbltotalitemcart.Text = e.ToString();
            return 0;
        }
    }

`email` is probably a string so you need `email = '" + email + "'...`. But instead of solving this have a look at parameterized queries so not to be in risk of sql injections — Gilad Green, Oct 30 '18 at 17:33
Extremely relevant to the SQL Injection issue. https://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/ — UnhandledExcepSean, Oct 30 '18 at 17:36
i got the answer, change to string stmt = "SELECT COUNT(menu_quantity) FROM cart Where email=@email"; and cmd.Parameters.AddWithValue("@email", Session["email"].ToString()); — Ariff Naj, Oct 30 '18 at 17:39

Ashkan Mobayen Khiabani · Accepted Answer · 2018-10-30T17:40:37.940

5

you are missin ' before and after email:

SELECT COUNT(menu_quantity) FROM cart Where email='" + email + "'";

However its not a good way of executing a query. instead, Use SqlCommand.Parameters:

string stmt = "SELECT COUNT(menu_quantity) FROM cart Where email=@email"
 using (SqlConnection con = new SqlConnection(ConfigurationManager.
           ConnectionStrings["connectionString"].ConnectionString))
 {
       SqlCommand cmd = new SqlCommand(stmt, con)
       cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
       con.Open();
       int count = (int)cmd.ExecuteScalar();
       return count;
 }

edited Oct 30 '18 at 17:40

answered Oct 30 '18 at 17:34

Ashkan Mobayen Khiabani

33,575
33
102
171

1

I personally feel that an answer for this question without showing the use of parameterized queries is a bad answer – Gilad Green Oct 30 '18 at 17:36
1

Someone with your rep should know the answer is to use parameters. – LarsTech Oct 30 '18 at 17:36
I edited my answer ;) – Ashkan Mobayen Khiabani Oct 30 '18 at 17:40

score 3 · Answer 2 · answered Oct 30 '18 at 17:34

3

I'm not super familiar with ASP syntax. But my gut SQL knowledge is telling me you're missing quotes around the email in the query itself.

answered Oct 30 '18 at 17:34

pinksharpii

527
1
8
18

1

While this is absolutely the cause, direction should provided to correcting the SQL Injection issue as well. – UnhandledExcepSean Oct 30 '18 at 17:34

Incorrect syntax near '='. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception

2 Answers2