1,105,633 Community Members

i got syntax error in update statement

Member Avatar
amf101
Newbie Poster
9 posts since Dec 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Guys i always get an error in this...it says "Syntax error in Update Statement"
please help...

Sub updateaccount(ByVal Username As String, ByVal Password As String, ByVal FName As String, ByVal MName As String, ByVal LName As String, ByVal Restrict As String, ByVal FullName As String)
        connect = New ADODB.Connection
        rs = New ADODB.Recordset
        Dim rss As New ADODB.Recordset
        connect.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source=C:\Users\Spongebob\Desktop\DataBase1.mdb"
        connect.Open()

        Dim sql1, sql As String

        sql = "Select AUsername from AdminAccount"
        rs.Open(sql, connect, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockPessimistic)

        If Username = "" Or Password = "" Or FName = "" Or MName = "" Or LName = "" Then
            MsgBox("Input asked values.")
        Else
            sql1 = "UPDATE AdminAccount where (AUsername = '" & Trim(Username) & "') set (APassword = '" & Trim(Password) & "', AFName ='" & Trim(FName) & "', AMName ='" & Trim(MName) & "', ALName ='" & Trim(LName) & "', Restrict? ='" & Trim(Restrict) & "', AFullName ='" & Trim(FullName) & "')"
            rss.Open(sql1, connect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockPessimistic)
            MsgBox("Change(s) of the Account " & Username & " was saved!")
        End If

    End Sub
Member Avatar
Pgmer
Practically a Posting Shark
881 posts since Apr 2008
Reputation Points: 50 [?]
Q&As Helped to Solve: 158 [?]
Skill Endorsements: 3 [?]
Featured
 
0
 

Before executing take the query and run in sql server so u will come to know where the syntax is going wrong..

Member Avatar
BitBlt
Practically a Posting Shark
895 posts since Feb 2011
Reputation Points: 442 [?]
Q&As Helped to Solve: 163 [?]
Skill Endorsements: 16 [?]
Featured
 
0
 

I'm fairly certain that you have to put the "SET" clause before the "WHERE" clause. That's fairly standard SQL syntax, even MSAccess SQL.
Here's some sample syntax that you can adapt to your specific situation:

UPDATE MyTable 
SET MyTable.MyNumeric = 100
WHERE (((MyTable.MyCharacter)="One")) 
OR (((MyTable.MyCharacter)="Two"));

One other little thing...on line 16, there appears to be a superfluous question-mark after the column name "Restrict". Probably has nothing to do with your syntax problem, but you might want to check into it anyway.

Hope this helps! Good luck!

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: