Syntax error in UPDATE statement

Question

0.00/5 (No votes)

See more:

Hi All,

I am preparing my collage project in which I am using c sharp as front end and ms access 2003 as back end.

Now while submitting a modification query I am getting a below error:

“{System.Data.OleDb.OleDbErrorCollection} ” “Syntax error in UPDATE statement.”

Below is my code which i used:

C#

OleDbConnection con = new OleDbConnection();
                con.ConnectionString = @"Provider=Microsoft.jet.OLEDB.4.0;Data Source=C:\amardeep\db1.mdb;User Id=admin;Password=;";
                con.Open();
                string query = "UPDATE teeth SET teethmodel=@teethmodel, noteeth=@noteeth, qty=@qty, date=@date WHERE teethmodel=@teethmodel and noteeth=@noteeth";
                OleDbCommand cmd = new OleDbCommand(query, con);
                cmd.Parameters.AddWithValue("@teethmodel", comboBox1.SelectedItem.ToString());
                cmd.Parameters.AddWithValue("@noteeth", comboBox2.SelectedItem.ToString());
                cmd.Parameters.AddWithValue("@qty", g.ToString());
                cmd.Parameters.AddWithValue("@date", textBox4.Text);
                int x = cmd.ExecuteNonQuery();
                con.Close();
                MessageBox.Show("your data has been Updated");

Below is my access design

Field name: Datatype
teethmodel: text
noteeth: text
qty: text
date: text

Like to inform you that I have used similar code earlier and it worked fine…but this time its not working.

Also like to inform you that query for inserting and deletion is working fine on this database design.

Could you please look into this and advise how can I over come this problem.

Thanks for your help

Posted 7-Dec-12 21:44pm

jonlink01

Updated 7-Dec-12 21:45pm

Krunal Rohit

v2

Add a Solution

Comments

__TR__ 8-Dec-12 4:19am

Try using comboBox1.SelectedItem.Text instead of comboBox1.SelectedItem.ToString().
Same applies for comboBox2.

jonlink01 8-Dec-12 5:04am

Hi TR,

VS2010 did not allows me to use this syntax, it gave me below error:

Error 1 'object' does not contain a definition for 'text' and no extension method 'text' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)

could you please suggest me any other alternate solution.

2 solutions

Solution 1

I'd like to suggest you that, if you're dealing with StoreProcedure then only use cmd.Parameters, otherwise for inline query (like you've written for UPDATE) you can directly set the values...
Now let me correct your query by this way (I'll not write entire query):

C#

string UpdateQuery = "UPDATE teet SET teethmodel='"+comboBox1.SelectedItem.ToString()+"',noteeth='"+comboBox2.SelectedItem.ToString()+"' WHERE teethmodel='"+comboBox1.SelectedItem.ToString()+"'";
cmd.CommandText=UpdateQuery;
cmd.Connection=con;
cmd.ExecuteNonQuery();

Posted 7-Dec-12 22:01pm

Krunal Rohit

Comments

jonlink01 8-Dec-12 4:19am

Hi Rohit,

Thank you for writing :-)

However its still giving me the same error “{System.Data.OleDb.OleDbErrorCollection} ” “Syntax error in UPDATE statement."
below is the code which i have written:

OleDbConnection con = new OleDbConnection();
con.ConnectionString = @"Provider=Microsoft.jet.OLEDB.4.0;Data Source=C:\amardeep\db1.mdb;User Id=admin;Password=;";
con.Open();
string UpdateQuery = "UPDATE teeth SET teethmodel='" + comboBox1.SelectedItem.ToString() + "',noteeth='" + comboBox2.SelectedItem.ToString() + "',qty='" + g.ToString() + "',date= '" + textBox4.Text + "' WHERE teethmodel='" + comboBox1.SelectedItem.ToString() + "'";
OleDbCommand cmd = new OleDbCommand(UpdateQuery, con);
cmd.CommandText = UpdateQuery;
cmd.Connection = con;
cmd.ExecuteNonQuery();

can you please look into this and advise.

[no name] 8-Dec-12 4:22am

Its correct...

jonlink01 8-Dec-12 4:32am

Rohit i understand that code is correct. but its still giving the same error.

i think there is some problem in database. my database look like below:
Field name: Datatype
teethmodel: text
noteeth: text
qty: text
date: text

Could you please advise.

[no name] 8-Dec-12 4:35am

mail me your entire code.. kunalrohit92@gmail.com

jonlink01 8-Dec-12 4:59am

i have sent all the information on you email address.

looking forward for your prompt response.

Thanks

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)

Menon Santosh · Accepted Answer · 2012-12-07T23:11:00

Solution 2

this error is because you are using the keyword 'date' so if you want to use date keyword then your query should be

C#

string query = "UPDATE teeth SET teethmodel=@teethmodel, noteeth=@noteeth, qty=@qty, [date]=@date WHERE teethmodel=@teethmodel and noteeth=@noteeth";

Posted 7-Dec-12 23:11pm

Menon Santosh

Comments

jonlink01 8-Dec-12 5:34am

Waaaoo Thank you soooo much....bro you make my day.

error has now resolved. i cant even thought about this error. :-)

Menon Santosh 8-Dec-12 5:39am

:)