How to Properly format Update syntax on OledbCommand?

c# ms-access-2016

Solution 1:

Simple UPDATE queries typically look like:

UPDATE table 
SET column1 = @parameter1, column2 = @parameter2 ...
WHERE columnX = @parameterX ...

It looks like you've adapted an INSERT VALUES statement, which isn't the right syntax

OLEDB isn't fussy whether you use ? or @xyz for your parameter placeholders, but you'll have to AddWithValue (or equivalent) as many placeholders as you have used, and in the same order. Unlike other providers you cannot reuse named parameters. This means for a statement like this:

UPDATE person
SET name = @n, age = @a
WHERE id = @i

Your C# will need something like:

command.Parameters.AddWithValue("thename", nameTextbox.Text);      //will be used as the value for @n
command.Parameters.AddWithValue("theage", ageNumericUpDown.Value); //will be used as the value for @a
command.Parameters.AddWithValue("theid", idVariable);              //will be used as the value for @i

3 parameters added; I deliberately made the names different to highlight that they don't matter. The number and order of addition is critical. You cannot do this:

UPDATE person
SET birthname = @n, currentname = @n
WHERE id = @i

//not enough parameters AND the wrong order. You cannot re-use @n unlike on other DBs
command.Parameters.AddWithValue("@i", ageNumericUpDown.Value);
command.Parameters.AddWithValue("@n", nameTextbox.Text);

_{"Use @xyz if you want, but assume they're all just ?s"}

Giving parameters a nice name is useful if you want to change the values and run the query again:

command.Parameters["theid"].Value = idVariable+1;

How to Properly format Update syntax on OledbCommand?

Solution 1:

Related

Recent Posts