ExecuteNonQuery is not running

Question

foreach (GridViewRow g1 in GridView1.Rows)
{
      SqlCommand cmd = new SqlCommand("INSERT INTO Order VALUES(@buyersName, @deliveryAddress, @productID, @productName, @category, CONVERT(VARBINARY(MAX), @image), @price, @paymentMode, @holderName)", con);

      cmd.Parameters.AddWithValue("@buyersName", Label2.Text);
      cmd.Parameters.AddWithValue("@deliveryAddress", TextBox1.Text);
      cmd.Parameters.AddWithValue("@productID", g1.Cells[0].Text);
      cmd.Parameters.AddWithValue("@productName", g1.Cells[1].Text);
      cmd.Parameters.AddWithValue("@category", g1.Cells[2].Text);
      cmd.Parameters.AddWithValue("@image", g1.Cells[3].Text);
      cmd.Parameters.AddWithValue("@price", g1.Cells[4].Text);
      cmd.Parameters.AddWithValue("@paymentMode", checkRadioButton());
      cmd.Parameters.AddWithValue("@holderName", TextBox2.Text);

      int r = cmd.ExecuteNonQuery();
 }

When I run this code, it is showing an error that there is a syntax error near "Order". checkRadioButton() is returning the label of the selected RadioButton.

Check where `Order` table is stored under which schema. It can be `dbo` or something else — Souvik Ghosh, Nov 16 '17 at 08:16
`ORDER` is a reserved word. Put it into square brackets `[Order]` — MatSnow, Nov 16 '17 at 08:17

score 3 · Answer 1 · answered Nov 16 '17 at 08:18

you can't have expression like convert() within the VALUE ()

Change to use

INSERT INTO [Order] (column name, ...) 
select @buyersName, convert() ,...

by the way you should explicitly specify the column name in the INSERT clause or in future when you add a column to the table, your query will break

also why are you using reserved name as table name ?

MatSnow · Answer 2 · 2017-11-16T10:09:01.017

Contrary to the statement in the other answer, it should be possible to CONVERT within the VALUES-section. But there are multiple flaws or things that could be improved:

ORDER is a reserved word in sql server, put it in square brackets: [Order]
Don't use AddWithValue otherwise sql server infers the datatype, which could be problematic. Use Add instead. See here for more information.
You can convert the value of g1.Cells[3].Text to a byte-array (byte[]) before setting the parameter value. For conversion to byte[] see here.
Specify the columns in your query, to not break it when table changes in future

Change your code like following (column-names and datatypes may vary):

SqlCommand cmd = new SqlCommand(@"INSERT INTO [Order] (buyersName, deliveryAddress, productID, productName, category, image, price, paymentMode, holderName) 
                                  VALUES(@buyersName, @deliveryAddress, @productID, @productName, @category, @image, @price, @paymentMode, @holderName)", con);

cmd.Parameters.Add("@buyersName", SqlDbType.VarChar).Value = Label2.Text;
cmd.Parameters.Add("@deliveryAddress", SqlDbType.VarChar).Value = TextBox1.Text;
cmd.Parameters.Add("@productID", SqlDbType.VarChar).Value = g1.Cells[0].Text;
cmd.Parameters.Add("@productName", SqlDbType.VarChar).Value = g1.Cells[1].Text;
cmd.Parameters.Add("@category", SqlDbType.VarChar).value = g1.Cells[2].Text;
cmd.Parameters.Add("@image", SqlDbType.VarBinary).Value =  g1.Cells[3].Text; //convert g1.Cells[3].Text to a byte array
cmd.Parameters.Add("@price", SqlDbType.Money) = g1.Cells[4].Text;
cmd.Parameters.Add("@paymentMode", SqlDbType.VarChar).Value = checkRadioButton();
cmd.Parameters.Add("@holderName", SqlDbType.VarChar).Value = TextBox2.Text;
int r=cmd.ExecuteNonQuery();

ExecuteNonQuery is not running

2 Answers2