Syntax Error with UPDATE command in SQL

Question

Does anything look wrong with this statement? I can't find anything wrong with it...

UPDATE AccountInfo
SET First Name = 'Test', Last Name = 'Account', Street = 'Street', State = 'State', ZipCode = 55555
WHERE id = 1

I'm writing a VB program, and I'm implementing an edit feature. This is the problem code.

        Public Function updateAccountInfo(ByVal cp As CPerson, ByVal fName As String, ByVal lName As String, ByVal address As String, ByVal state As String, ByVal zip As Integer) As Boolean
    Dim sql, sql2 As String
    sql = "UPDATE AccountInfo SET First Name = '" & fName & "', Last Name = '" & lName & "', Street = '" & address & _
        "', State = '" & state & "', ZipCode = " & zip & " WHERE id = " & cp.returnIDOnlyNumber
    sql2 = "UPDATE Accounts SET First Name = '" & fName & "', Last Name = '" & lName & "' WHERE id = " & cp.returnIDOnlyNumber
    Return do_command(sql, sql2)
End Function

Private Function do_command(ByVal sql As String, ByVal sql2 As String) As Boolean
        Dim command As OleDbCommand
        Try
            conn.Open()
            command = New OleDbCommand(sql, conn)
            command.ExecuteNonQuery()
            conn.Close()
            conn.Open()
            command = New OleDbCommand(sql2, conn)
            command.ExecuteNonQuery()
            conn.Close()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

I guess ZipCode is string.

i486
– i486

2014-12-17 12:44:18 +00:00
Commented Dec 17, 2014 at 12:44 — i486
– i486, Commented Dec 17, 2014 at 12:44
Are you using mySql?

SMA
– SMA

2014-12-17 12:44:56 +00:00
Commented Dec 17, 2014 at 12:44 — SMA
– SMA, Commented Dec 17, 2014 at 12:44

Gordon Linoff · Accepted Answer · 2014-12-17 12:45:05Z

5

Yes, you have space in the names. If you really have them, then you need to escape them, perhaps with square braces:

UPDATE AccountInfo
    SET [First Name] = 'Test', 
        [Last Name] = 'Account',
        Street = 'Street',
        State = 'State',
        ZipCode = '55555'
WHERE id = 1;

It is better to have names with no spaces. Also, the zip code should be stored as a string, not a number, otherwise you will lose leading zeros.

answered Dec 17, 2014 at 12:45

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brendan Long Over a year ago

I haven't had issues using spaces in INSERT statements.

Brendan Long Over a year ago

Never mind. Looks like taking out the spaces fixed it. That and I changed the zip to String. I forgot zip codes can start with 0's.

Collectives™ on Stack Overflow

Syntax Error with UPDATE command in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related