Syntax error in update statment

Question

0.00/5 (No votes)

See more:

Hi. i am trying to execute an update statement in asp.net webform and ms-access db but it says syntax error in update statment. thanks for help. below is code:

        OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|DB.accdb;");
          connection.Open();
int kid = MYVALUE;
int no = MYVALUE;
string fid= MYVALUE;
            cmd = new OleDbCommand("update mytable set no=@no where FID=@fid and KID=@kid",connection);
            cmd.Parameters.AddWithValue("@no", no);
            cmd.Parameters.AddWithValue("@fid", fid);
            cmd.Parameters.AddWithValue("@kid", kid);
            cmd.ExecuteNonQuery();
            connection.Close();

What I have tried:

everything seams ok but it show syntax error for update.

Posted 5-Aug-24 20:58pm

majid saidi

Add a Solution

Comments

0x01AA 6-Aug-24 3:05am

And the exact error message is?

[no name] 6-Aug-24 3:52am

You are setting all three parameters to the same value; is that what you meant to do?

2 solutions

Solution 2

'OleDb' does not support named parameters, it requires positional parameters. See MS Learn | OleDbCommand.Parameters Property[^].

Your SQL query should use '?' instead of named parameters like '@no', '@fid', and '@kid'. Your code will then look like -

NET

using System.Data.OleDb;

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|DB.accdb;");
connection.Open();

int kid = MYVALUE;
int no = MYVALUE;
string fid = MYVALUE;

string query = "UPDATE mytable SET no = ? WHERE FID = ? AND KID = ?";
using (OleDbCommand cmd = new OleDbCommand(query, connection))
{
    //Add ALL parameters in the order they appear in your query...
    cmd.Parameters.AddWithValue("?", no);
    cmd.Parameters.AddWithValue("?", fid);
    cmd.Parameters.AddWithValue("?", kid);

    cmd.ExecuteNonQuery();
}

connection.Close();

Posted 6-Aug-24 7:16am

Andre Oosthuizen

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2024-08-05T22:06:00

Solution 1

"NO" is a keyword in Access: List of reserved words in Access - Microsoft 365 Apps | Microsoft Learn[^] - it's used for "not equal to" apparently, though I've never heard of it.

Wrap it in square brackets and the problem should go away:

cmd = new OleDbCommand("update mytable set [no]=@no where FID=@fid and KID=@kid",connection);

Posted 5-Aug-24 22:06pm

OriginalGriff

Comments

majid saidi 18-Aug-24 12:50pm

Thanks. It works.

OriginalGriff 19-Aug-24 0:42am

You're welcome!