Syntax error in update statement

Question

Hi

code:

string query1 = @"UPDATE global_mapping set escape_id = " + 
  dataGridView1.Rows[i].Cells[2].Value + ",function_id = " + 
  dataGridView1.Rows[i].Cells[3].Value + ",function_name = '" + 
  dataGridView1.Rows[i].Cells[4].Value + "',parameter_name = '" + 
  dataGridView1.Rows[i].Cells[5].Value + "',parameter_validity = '" + 
  dataGridView1.Rows[i].Cells[6].Value + "',statusparameter_id = " + 
  dataGridView1.Rows[i].Cells[7].Value + ",acb_datatype = '" + 
  dataGridView1.Rows[i].Cells[8].Value + "',data_type_id = " + 
  dataGridView1.Rows[i].Cells[9].Value + ",bit_size = " + 
  dataGridView1.Rows[i].Cells[10].Value + ",validity_status ='" + 
  dataGridView1.Rows[i].Cells[11].Value + "',validity_func = '" + 
  dataGridView1.Rows[i].Cells[12].Value + "'WHERE global_mapping.parameter_id =" + 
  dataGridView1.Rows[i].Cells[1].Value + "";
OleDbCommand cmd1 = new OleDbCommand(query1, conn);
cmd1.ExecuteNonQuery();

code ends:

When I execute the above code I get an error stating "Syntax error in Update statement". Can someone please tell me how to resolve this?

Answer 1

Wow. Can we say... SQL Injection?

Try using Parameters. Not only will you protect yourself, but your SQL will become MUCH more readable.

Answer 2

Never use string concatenation for building SQL queries. Use SQL parameters.

Answer 3

Yikes! Please provide the final query1 value and try to format it so we can get a better picture of it. My guess is a missing ' or something.

Answer 4

I'd say you're missing some quotes in there but your code is such a pig-sty I can't tell. If you won't fix your code then at the minimum give us a dump of query1 so we can read your actual query.

And use parameters or stored procedures like the previous responses said. All it takes is one of your variables to get overwritten with something nasty and your server will be wide open to anyone deleting your tables or worse.

Even if this is a local "safe" database you should unlearn your bad habits now.

Answer 5

Hi,

It looks like you need to add a space before your WHERE clause.

Hope this helps,

Bill

Answer 6

Put Console.WriteLine(query1) before OleDbCommand cmd1 = new OleDbCommand(query1, conn);

See the value of query1 printed to console window.
Does the SQL Statement look OK? I guess not - you will now be able to find a field which is non-numeric and is blank in the grid.

And, use parameters as others have said.