I was using trying to save details of checkout user in a shopping cart, but when i tried to execute last query this exception came

Question

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'S'.
Unclosed quotation mark after the character string ')'.'

This exception was originally thrown at this call stack:
[External Code]
test_1.PlaceOrder.Button1_Click(object, System.EventArgs) in PlaceOrder.aspx.cs
[External Code]

code:-
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt;
dt = (DataTable)Session["buyitems"];

        for (int i = 0; i <= dt.Rows.Count - 1; i++)
        {
            String updatepass = "insert into orderdetails(orderid,sno,medicineid,medicinename,mrp,quantity,dateoforder) values('" + Label3.Text + "'," + dt.Rows[i]["sno"] + "," + dt.Rows[i]["medicineid"] + ",'" + dt.Rows[i]["medicinename"] + "'," + dt.Rows[i]["mrp"] + "," + dt.Rows[i]["quantity"] + ",'" + Label4.Text + "')";
            String mycon1 = "Data Source=ABHI;Initial Catalog=trial;Integrated Security=True";
            SqlConnection s = new SqlConnection(mycon1);
            s.Open();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = updatepass;
            cmd1.Connection = s;
            cmd1.ExecuteNonQuery();
            s.Close();

        }
        saveaddress();
        Response.Redirect("PlacedSuccessfully.aspx");

    }
}

Answer 1

The error message is very clear. The string built SQL has a syntax error. Use the debugger to review the value of updatepass. Usually the error is very easy to spot once you take the time to look. You can also execute the SQL in Management Studio.

Lastly, a parameter query is the standard practice. Concatenated SQL opens up a SQL injection vulnerability.

Answer 2

Hi @Abhishek Prasad ,

It is better to use SqlCommand with parameters.
Check it out below.

I don't know your data. Same with your target table structure, and column data types.
That's why you need to double check data types in SqlDbType, and their lengths.

c#

String query = ""insert into orderdetails (orderid,sno,medicineid,medicinename,mrp,quantity,dateoforder) VALUES (@orderid,@sno,@medicineid,@medicinename,@mrp,@quantity,@dateoforder)";  
  
using(SqlCommand command = new SqlCommand(query, connection))  
{  
 command.Parameters.Add("@orderid", SqlDbType.Int).Value = Label3.Text;  
 command.Parameters.Add("@sno", SqlDbType.VarChar, 30).Value = dt.Rows[i]["sno"];  
 command.Parameters.Add("@medicineid", SqlDbType.Int).Value = dt.Rows[i]["medicineid"];  
 command.Parameters.Add("@medicinename", SqlDbType.VarChar, 30).Value = dt.Rows[i]["medicinename"];  
 command.Parameters.Add("@mrp", SqlDbType.Int).Value = dt.Rows[i]["mrp"];  
 command.Parameters.Add("@quantity", SqlDbType.Int).Value = dt.Rows[i]["quantity"];  
 command.Parameters.Add("@dateoforder", SqlDbType.DateTime).Value = Label4.Text;  
  
 connection.Open();  
 int result = command.ExecuteNonQuery();  
  
 // Check Error  
 if(result < 0)  
 Console.WriteLine("Error inserting data into Database!");  
}  

Answer 3

you code allows sql injection and is a security risk. it should use parameters, rather than dynamically build sql. converting the code should fix your issue of building invalid sql statements

Answer 4

Thanks to all Professionals . It means Alo