[RESOLVED] OleDB UPDATE instruction error

[gabrielSbrug]

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"

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

[techgnome]

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:

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

[gabrielSbrug]

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?

[jmcilhinney]

I wonder whether Action is a reserved word. Try wrapping that in brackets and see if that makes a difference.

[jmcilhinney]

By the way, how exactly are you storing those dates and times? Please tell me that you're not storing them as text.

[gabrielSbrug]

You were right jmcilhinney, Action is a really reserved word... as I put the brackets around it, worked perfectly...

Thanks man..