Syntax error in update statement

问题:

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?

回答1:

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

Hope this helps,

Bill

回答2:

Wow. Can we say... SQL Injection?

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

回答3:

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

回答4:

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.

回答5:

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.

回答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.