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

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!

Syntax error in update statment

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Syntax error in update statment

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us