Syntax error in UPDATE statement

Question

0.00/5 (No votes)

See more:

.NET3.5

I get an exception telling me I have a syntax error in my UPDATE statement, but I can't find it.

   Dim con1 As New OleDbConnection
        Dim cmd As New OleDbCommand

        con1.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;
Data Source =C:\db.mdb"
        cmd.Connection = con1
        cmd.CommandText = "UPDATE tblArtikujt SET 
(Grupi=@Grupi, Pershkrimi=@Pershkrimi, Cmimi=@Cmimi) 
WHERE Id = " & ida & " "
        cmd.Parameters.AddWithValue("@Grupi", TextBox2.Text)
        cmd.Parameters.AddWithValue("@Pershkrimi", TextBox3.Text)
        cmd.Parameters.AddWithValue("@Cmimi", TextBox4.Text)

        con1.Open()
        cmd.ExecuteNonQuery()
        con1.Close()
        MsgBox("Record added succesfully")

Posted 17-Dec-09 19:58pm

Alabamaaaa

Updated 18-Dec-09 1:13am

Rajesh R Subramanian

v10

Add a Solution

6 solutions

Solution 1

Well, your first issue is obviously that your code sucks. How do you know for SURE based on how useless your variable names are, that TextBox2 has the value for 'Grupi' ? Assuming you do know that, why is it that you don't care about security in your website ? I can erase your entire database if I want, if I have access to your update page.

Finally, if someone types quotes into the textbox, what do YOU think that will do to the SQL ? Oh, I see. You don't know SQL well enough to even USE quotes. When your SQL won't work, a good first step, is to get the string out of the debugger, and put it directly into the Management Console, to get a better error message. If you put single quotes around your strings, your code will probably work, but it will still be illegible, not secure, and prone to break if users use single quotes in the text they enter.

Posted 17-Dec-09 20:07pm

Christian Graus

Solution 2

wrote:
Dim strUpdate As String = ("UPDATE tblArtikujt SET [Grupi]=" & TextBox2.Text & ", [Pershkrimi]=" & TextBox3.Text & ", [Cmimi]=" & TextBox4.Text & ", WHERE [Id]=" & ida & "")

The above is the query used. The error i found is a comma before where clause.

Dim strUpdate As String = ("UPDATE tblArtikujt SET [Grupi]=" &
 TextBox2.Text & ", [Pershkrimi]=" & TextBox3.Text & ", 
[Cmimi]=" & TextBox4.Text & " WHERE [Id]=" & ida & "")

It should work.

In case there is still a Issue, follow the Christian advice:

Christian wrote:
When your SQL won't work, a good first step, is to get the string out of the debugger, and put it directly into the Management Console, to get a better error message. If you put single quotes around your strings, your code will probably work, but it will still be illegible, not secure, and prone to break if users use single quotes in the text they enter.

Posted 17-Dec-09 20:15pm

Aman Bhullar

Solution 3

I see your variable names are still insane, but I am pleased you did some research on paramaterised queries. Your SQL looks OK, did you take my advice and generate the SQL string and run it in Management Studio to see exactly what the error is ?

Why do you append your SQL with a space ? If ida is a numeric id, you don't need quotes ( otherwise, this is the error ), but you don't need a space, either.

Posted 17-Dec-09 22:11pm

Christian Graus

Solution 5

Why dont you use parameters for Id.. while using it for each other parameters? :confused:

If Id is a Character field, you need to add Quotes.

Where Id='" & ida & "'"

Something like this.

:thumbsup:

Posted 17-Dec-09 23:10pm

Abhishek Sur

Solution 6

By the way the problem was at parenthesis.

The correct code is:

cmd.CommandText = "UPDATE tblArtikujt SET Grupi=@Grupi, Pershkrimi=@Pershkrimi, Cmimi=@Cmimi WHERE Id =" &ida&" "

Posted 18-Dec-09 16:25pm

Alabamaaaa

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)

Vinayaka Krishna Shenoy · Accepted Answer · 2009-12-18T19:02:00

Hello,

Yes indeed you have an syntax error in your update statement

UPDATE tblArtikujt SET
(Grupi=@Grupi, Pershkrimi=@Pershkrimi, Cmimi=@Cmimi)
WHERE Id = " & ida & " "

Please remove the braces after the SET statement - It will work

UPDATE tblArtikujt SET
Grupi=@Grupi, Pershkrimi=@Pershkrimi, Cmimi=@Cmimi
WHERE Id = " & ida & " "

This will work!!

Regards,
-Vinayak