Click here to Skip to main content
15,900,511 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 18-Dec-09 1:13am
v10

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
 
Share this answer
 
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.
 
Share this answer
 
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.
 
Share this answer
 
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.
 
Share this answer
 
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:
 
Share this answer
 
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&" "
 
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