Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
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:

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 22:44pm
Edited 7-Dec-12 22:45pm
v2
Comments
__TR__ at 8-Dec-12 4:19am
   
Try using comboBox1.SelectedItem.Text instead of comboBox1.SelectedItem.ToString().
Same applies for comboBox2.
jonlink01 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

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
 
string query = "UPDATE teeth SET teethmodel=@teethmodel, noteeth=@noteeth, qty=@qty, [date]=@date WHERE teethmodel=@teethmodel and noteeth=@noteeth";
  Permalink  
Comments
jonlink01 at 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 at 8-Dec-12 5:39am
   
:)
Rate this: bad
good
Please Sign up or sign in to vote.

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):
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();
  Permalink  
Comments
jonlink01 at 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.
Krunal Rohit at 8-Dec-12 4:22am
   
Its correct...
jonlink01 at 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.
Krunal Rohit at 8-Dec-12 4:35am
   
mail me your entire code.. kunalrohit92@gmail.com
jonlink01 at 8-Dec-12 4:59am
   
i have sent all the information on you email address.
 
looking forward for your prompt response.
 
Thanks

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 8,478
1 OriginalGriff 6,516
2 Peter Leow 3,567
3 Zoltán Zörgő 3,386
4 Richard MacCutchan 2,397


Advertise | Privacy | Mobile
Web02 | 2.8.150123.1 | Last Updated 8 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100