Update command using MS ACCESS as a Database.

Question

5.00/5 (1 vote)

See more:

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 22-Jan-12 20:23pm

santosh.giri

Updated 22-Jan-12 20:25pm

thatraja

v3

Add a Solution

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"

3 solutions

Solution 3

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();

Posted 24-Jan-12 6:14am

santosh.giri

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)

Amir Mahfoozi · Accepted Answer · 2012-01-22T20:54:00

Solution 1

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.

Posted 22-Jan-12 20:54pm

Amir Mahfoozi

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 :(

uspatel · Accepted Answer · 2012-01-22T20:57:00

Solution 2

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();

Posted 22-Jan-12 20:57pm

uspatel

Updated 23-Jan-12 0:39am

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..