syntax error during update limt or update top(1) c# & access

Question

I have syntax error in this code

string JSS_connetionString011 = null;
OleDbConnection JSS_connection011;
OleDbDataAdapter JSS_oledbAdapter011 = new OleDbDataAdapter();
string JSS_sql011 = null;
JSS_connetionString011 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/je_salestoredb.mdb;Persist Security Info=True;Jet OLEDB:Database Password=JEPTUSJSSJes";
JSS_connection011 = new OleDbConnection(JSS_connetionString011);
JSS_sql011 = "update product set Siv_Sales_Invoice_NO = '" + textBox1.Text + "' , prod_Status = '" + JSS_product_Status + "' , prod_SALED_ftrCalc = '" + textBox10.Text + "' , piv_SALED_DATE = '" + dateTimePicker1.Text + "' , sivd_ID = '" + textBox3.Text + "' , prod_ROOM ='" + JSS_product_Warhousee + "' where( prod_COMPANY = '" + comboBox4.Text + "' and prod_MODEL = '" + comboBox5.Text + "' and prod_Status = '" + JSS_Ready_For_Sale + "' ORDER BY prod_COMPANY LIMIT 1 )";
JSS_connection011.Open();
JSS_oledbAdapter011.UpdateCommand = JSS_connection011.CreateCommand();
JSS_oledbAdapter011.UpdateCommand.CommandText = JSS_sql011;
JSS_oledbAdapter011.UpdateCommand.ExecuteNonQuery();


Syntax error (missing operator) in query expression '( prod_COMPANY = 'NSN' and prod_MODEL = '606' and prod_Status = 'true' ORDER BY prod_COMPANY LIMIT 1 )'.

There are many records with the same conditions of the query sentence, but each of them have different id.

Any Ideas?

Are you sure you can use `ORDER BY` or `LIMIT` in `UPDATE` statement? Have you ever check it's syntax? — Soner Gönül, Mar 08 '15 at 12:38
i get the idea from here http://stackoverflow.com/questions/1513206/update-multiple-rows-using-limit-in-mysql — SunEye, Mar 08 '15 at 12:41
I suggest the very *first* thing you do is start using parameterized SQL instead of putting the values directly into your SQL. Currently you have a gaping wide [SQL Injection Attack](http://bobby-tables.com) waiting to happen. That may fix the problem in itself, but if not it will at least be more readable and secure. — Jon Skeet, Mar 08 '15 at 12:41

score 0 · Accepted Answer · answered Mar 08 '15 at 12:54

Okay, a couple of things:

One, you can't use ORDER BY or LIMIT in an UPDATE statement in an Access database. The example you linked to is for MySQL, a different database.

Two: What Jon Skeet says is absolutely true: you need to use parameterized SQL rather than putting values directly in SQL.

Three: Given that ORDER BY and LIMIT are not valid in Access update statements, if you replace this bit of code:

+ JSS_Ready_For_Sale + "' ORDER BY prod_COMPANY LIMIT 1 )";

... with this:

+ JSS_Ready_For_Sale + "')";

... the syntax error, or at least that syntax error, may go away.

You might also want to be put a space between the WHERE and the (. I don't know that that will count as a syntax error, but it might.

ETA: ALSO! If you only want to update one record, the one with a specific ID (which I infer from your question text) you will need to specify that record ID in the WHERE clause.

If you records don't have unique IDs, you have a very big problem that you need to fix. A unique ID is absolutely required if you want to be able to update a specific record.

score 0 · Answer 2 · answered Mar 08 '15 at 14:30

0

You will also need a properly format string expression for the date:

piv_SALED_DATE = #" + dateTimePicker1.Value.ToString("yyyy'/'MM'/'dd") + "# ,

and in Access SQL you will use "Select Top n .." to select the first n values.

answered Mar 08 '15 at 14:30

Gustav

53,498
7
29
55

syntax error during update limt or update top(1) c# & access

2 Answers2