[RESOLVED] OleDB UPDATE instruction error
Hi, i'm trying to update some data to a table in a access db, but the VB2008 keeps on giving me the same error. I can't see what is wrong because i've used this same code in other form of the project and it worked...
The error I get is: "Syntax error in the UPDATE instruction"
Code:
Dim Actions As String = ""
Dim data_atual As DateTime = Today
Dim hora_atual As DateTime = TimeOfDay
Dim con As OleDbConnection
Dim sql As String
con = New OleDbConnection("Provider=Microsoft.Jet.oledb.4.0;Data Source=C:\Users\Sbrug\Desktop\db_chamados.mdb;")
Dim cmd As OleDbCommand
sql = "UPDATE Chamados SET Action='" & Actions & "', HoraClose='" & hora_atual & "', DataClose='" & data_atual & "' WHERE Codigo=" & Code
con.Open()
cmd = New OleDbCommand(sql, con)
Try
If cmd.ExecuteNonQuery() Then
MsgBox("Dados salvos com sucesso!")
Else
MsgBox("Falha ao tentar salvar os dados, verifique se existe qualquer anormalidade nos dados inseridos.")
End If
con.Close()
Catch ex As OleDbException
MsgBox(ex.Message, MsgBoxStyle.Critical, "Oledb Error")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
End Try
Obs.: The variable 'Code' has a value and it matches with with the table register, so I thin it is not the problem.
Hope you guys can help me....
Re: OleDB UPDATE instruction error
when ever you get one of these errors, the best thing to do is to print out the SQL you've built... make sure it is in fact correct.
Also, because you're using concatenation to build the SQL, you could be running into problems if one of your values contains a ' mark... not to mention it's a SQL Injection vulnerability.
You may want to consider using a parameterized query... somethign like this:
Code:
sql = "UPDATE Chamados SET Action=?, HoraClose=?, DataClose=? WHERE Codigo=?"
cmd = New OleDbCommand(sql, con)
cmd.Parameters.AddWithValue("Action", Actions)
cmd.Parameters.AddWithValue("HoraClose", hora_atual)
cmd.Parameters.AddWithValue("DataClose", data_atual)
cmd.Parameters.AddWithValue("Codigo", Code)
Try
If cmd.ExecuteNonQuery() Then
MessageBox.Show("Dados salvos com sucesso!")
Else
-tg
Re: OleDB UPDATE instruction error
I've printed out the sql in a msgbox to search for erros, but apparently there were none, it stayed like this:
UPDATE Chamados SET Action='ECO', HoraClose='08:47:30', DataClose='10/02/2012' WHERE Codigo=5
I've also tried the parametrized query, but the same error continues to appear:
"Syntax error in the UPDATE instruction"
Does someone know what could it be?
Re: OleDB UPDATE instruction error
I wonder whether Action is a reserved word. Try wrapping that in brackets and see if that makes a difference.
Re: OleDB UPDATE instruction error
By the way, how exactly are you storing those dates and times? Please tell me that you're not storing them as text.
Re: OleDB UPDATE instruction error
You were right jmcilhinney, Action is a really reserved word... as I put the brackets around it, worked perfectly...
Thanks man..