Click here to Skip to main content
15,847,264 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
I am using MS Access as a DataBase,
whenever I'm trying to update using given below statements, it is throwing Syntax error in UPDATE.
C#
OleDbConnection OCon = new OleDbConnection();
                OCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Database.mdb";
                string query;
                
                OleDbCommand cmdUpdate = new OleDbCommand();
                OCon.Open();
                query = "Update LIKey SET Desc='" + txtDesc.Text + "',Example='" + txtExample.Text + "' WHERE LIKeys=@LIKeys";
                cmdUpdate.Parameters.Clear();
                cmdUpdate.CommandText = query;
                cmdUpdate.CommandType = CommandType.Text;
                cmdUpdate.Parameters.Add("@LIKeys", OleDbType.LongVarChar);
                cmdUpdate.Parameters["@LIKeys"].Value = txtLIKey.Text;

                cmdUpdate.Connection = OCon;

                cmdUpdate.ExecuteNonQuery();
                OCon.Close();

Please tell me If I'm wrong.

Thanks
Posted
Updated 22-Jan-12 21:25pm
v3
Comments
thatraja 23-Jan-12 2:26am    
What's the error message?
santosh.giri 23-Jan-12 2:50am    
It is throwing "Syntax Error in UPDATE statement"

Why some of your SQL parts are built on runtime and some are Parameters ? Please convert all of them to parameters and also use Parameters.AddWithValue and try again. For a good example on update method read this page :

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access[^]

Hope it helps.
 
Share this answer
 
Comments
santosh.giri 23-Jan-12 3:07am    
I tried with this one

string Conn;

Conn= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Database.mdb";
OleDbConnection OCon = new OleDbConnection(Conn);
string query;

query = "Update LIKey SET Desc=?,Example=? WHERE LIKeys=?";
OleDbCommand cmdUpdate = new OleDbCommand(query,OCon);

cmdUpdate.Parameters.Clear();

cmdUpdate.CommandType = CommandType.Text;
cmdUpdate.Parameters.AddWithValue("Desc", txtDesc.Text);
cmdUpdate.Parameters.AddWithValue("Example", txtExample.Text);
cmdUpdate.Parameters.AddWithValue("LIKeys", txtLIKey.Text);
OCon.Open();
cmdUpdate.ExecuteNonQuery();
OCon.Close();


But still the same error :(
try as
C#
 query = "Update LIKey SET Desc=@desc,Example=@exp WHERE LIKeys=@LIKeys";
               // cmdUpdate.Parameters.Clear();
                cmdUpdate.CommandText = query;
                cmdUpdate.Connection = OCon;
                cmdUpdate.CommandType = CommandType.Text;
cmdUpdate.Parameters.AddWithValue("desc",txtDesc.Text);
cmdUpdate.Parameters.AddWithValue("exp",txtExample.Text);
cmdUpdate.Parameters.AddWithValue("LIKeys",txtLIKey.Text);
cmdUpdate.ExecuteNonQuery();
 
Share this answer
 
v2
Comments
santosh.giri 23-Jan-12 3:08am    
Already tried Patel!
Same error received.
uspatel 23-Jan-12 6:41am    
comment cmdUpdate.Parameters.Clear();
and then try,hope it works.
else
run result of debugged query in MS access. and see
santosh.giri 23-Jan-12 10:06am    
sorry frndz it was due to my mistake, DESC was the column name and it was taking as a SORTING format..
I was using Desc as a Column name in MS ACCESS and while using in SQL Query system was assiming as sorting variable in SQL and because of this it was throwing SYNTAX in UPDATE error.


OleDbConnection OCon = new OleDbConnection();
OCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Database.mdb";
string query;

OleDbCommand cmdUpdate = new OleDbCommand();
OCon.Open();
<pre lang="c#">query = "Update LIKey SET Desc='" + txtDesc.Text + "',Example='" + txtExample.Text + "' WHERE LIKeys=@LIKeys";</pre>
<pre lang="c#">// DESC IS A INBUILT TYPE IN SQL </pre>
cmdUpdate.Parameters.Clear();
cmdUpdate.CommandText = query;
cmdUpdate.CommandType = CommandType.Text;
cmdUpdate.Parameters.Add("@LIKeys", OleDbType.LongVarChar);
cmdUpdate.Parameters["@LIKeys"].Value = txtLIKey.Text;

cmdUpdate.Connection = OCon;

cmdUpdate.ExecuteNonQuery();
OCon.Close();
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900