Syntax Problem when using UPDATE against ACCESS 2007 using Visual Studio 2010.

[Learner VBNET]

Dim newpassword As String = Trim(Me.TextBox5.Text)
Dim SQLstring As String = "UPDATE Users SET Password ='" & newpassword & "' where ID = " & UserID
conn2 = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data source = c:\users\kevin\documents\voter enrolment database.accdb;Persist Security Info=False;")
Dim cmd As OleDbCommand
conn2.Open()
cmd = New OleDbCommand(SQLstring, conn2)
cmd.ExecuteNonQuery()
conn2.Close()
when debugging the line cmd.ExecuteNonQuery() indicates a SYNTAX error with the UPDATE SQL| statement. Please HELP. Thanks.

 

[jmcilhinney]

Please keep each thread to a single topic and each topic to a single thread. Post each question once only to the most appropriate forum.

If we're to diagnose the issue, it would be useful to see what the actual SQL code is once built. That said, you shouldn't be using string concatenation to build SQL statements. ALWAYS use parameters to insert values into SQL code and many such problems will never occur. Follow the Blog link in my signature and check out my post on ADO.NET parameters.

 

[JuggaloBrotha]

Dim newpassword As String = Trim(Me.TextBox5.Text)
Dim SQLstring As String = "UPDATE Users SET Password ='" & newpassword & "' where ID = " & UserID
conn2 = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data source = c:\users\kevin\documents\voter enrolment database.accdb;Persist Security Info=False;")
Dim cmd As OleDbCommand
conn2.Open()
cmd = New OleDbCommand(SQLstring, conn2)
cmd.ExecuteNonQuery()
conn2.Close()
when debugging the line cmd.ExecuteNonQuery() indicates a SYNTAX error with the UPDATE SQL| statement. Please HELP. Thanks.

How do you update (or set) a text field in a query? If you don't know, then you've got some simple reading to do: SQL UPDATE Statement

 

[Learner VBNET]

Thanks. I have included a Msgbox statement before the conn2.open() statement showing the SQLstring of the SQL UPDATE statement. I have included an attachment that indicates what the Msgbox displayed. Many thanks.

 

[JuggaloBrotha]

Thanks. I have included a Msgbox statement before the conn2.open() statement showing the SQLstring of the SQL UPDATE statement. I have included an attachment that indicates what the Msgbox displayed. Many thanks.

No problem, now the next question is: why aren't you using a parametrized query?

 

[Learner VBNET]

OK...I do have some learning to do(as my username indicates I am a learner) and I have followed your link and READ the contents. I am still in need of some assistance. So if you know what is wrong with the code posted then PLEASE LET ME KNOW. Thanks.

Simple answer to your last question is....because thats the way I have coded it. Parametrized : for me it is parameter driven. Its accepts to parameters : newpassword(defined as string) and Userid(defined as long). Definitions below :

Dim ds2 As New DataSet()
dataAdapter2.Fill(ds2, "USERS")
Dim dataTable2 As DataTable = ds2.Tables("USERS")
Dim UserID As Long = CLng(dataTable2.Rows(0)(0))
Dim newpassword As String = Trim(Me.TextBox5.Text)
Dim SQLstring As String = "UPDATE Users SET Password ='" & newpassword & "' where ID = " & UserID
conn2 = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0 ;Data source = c:\users\kevin\documents\testdatabase.accdb;Persist Security Info=False;")
Dim cmd As OleDbCommand
Msgbox(SQLstring)
conn2.Open()
cmd = New OleDbCommand(SQLstring, conn2)
cmd.ExecuteNonQuery()
msgbox(SQLstring)
conn2.Close()

when debugging the line cmd.ExecuteNonQuery() indicates a SYNTAX error with the UPDATE SQL| statement. Please HELP. Thanks.

Here is the Design View of the Access Table : Users

 

[JuggaloBrotha]

I dont see anything wrong with your query:
Dim SQLstring As String = "UPDATE Users SET Password ='" & newpassword & "' where ID = " & UserID
because it outputs this in your screenshot of the messagebox:
UPDATE Users SET Password ='Passwordnew' where ID = 2

I was asking about why you're not using a parametrized query because those are less prone to SQL injection because the query is sent to the DB with the place holders and then the values are passed to the DB afterwards. Here's how you could turn your current query into a parametrized one:

Dim newPassword As String = Me.TextBox5.Text.Trim
Dim UserID As Long
Dim cmd As OleDbCommand
conn2 = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0 ;Data source = c:\users\kevin\documents\testdatabase.accdb;Persist Security Info=False;")
If newPassword <> String.Empty Then
    Dim ds2 As New DataSet()
    Try
        'Get the UserID
        dataAdapter2.Fill(ds2, "USERS")
        UserID = Convert.ToInt64(ds2.Tables(0).Rows(0)("ID"))        
        cmd = New OleDbCommand("UPDATE Users SET Password = @NewPassword Where ID = @UserID;", conn2)

        'Add the values to the parameters collection to send to the DB
        cmd.Parameters.Add(New OleDb.OleDbParameter("@NewPassword", newPassword))
        cmd.Parameters.Add(New OleDb.OleDbParameter("@UserID", UserID))

        conn2.Open() 'Open the connection
        cmd.ExecuteNonQuery() 'Push it to the DB
        MessageBox.Show("New Password successfully set")
    Catch ex As Exception
        'Something went wrong
        Messagebox.Show(ex.ToString)
    Finally
        'Success or not, make sure it's closed
        If conn2.State <> ConnectionState.Closed Then conn2.Close()
    End Try
End If

I also cleaned things up and added a Try/Catch block around the code that could error here. There's still a few things that can use improvement, like having the connection string stored in the app settings and using a central connection to the DB and whatnot, but those aren't the point here.

 

[Learner VBNET]

Thanks for the code...in the process of trying it out. However VS 2010 is complainingg about this line :
If conn2.ConnectionState <> ConnectionState.Closed Then conn2.Close()

It shows the following error message :
Error 1 'ConnectionState' is not a member of 'System.Data.OleDb.OleDbConnection'.

Thank you guys for your valued assistance. I will read the articles indicated to find out further about parametrized SQL statements.

GREAT SITE!

Edit:
Ok...I have change the line to the following and its gone thru. THANK YOU BROTHA!....REALLY APPRECIATE IT.

If conn2.State <> ConnectionState.Closed Then conn2.Close()

 

[JuggaloBrotha]

Sorry, that was a typo (I just used Windows Notepad to type out all that code) it's: If conn2.State <> ConnectionState.Closed Then conn2.Close()

I updated my previous post with that fix too.

 

[Learner VBNET]

Hi. I am stgill getting a similar message SYNTAX error in UPDATE statement. Will get a screen shot of the actual error messages posted shortly.

 

[Learner VBNET]

Here is the error messages obtained using the code supplied by yourselves :

 

[JuggaloBrotha]

Ok, I created a new project and created a new database (access 2003 DB but that doesnt matter), I have a form with 2 textboxes (IDTextBox and PasswordTextBox) and an update button, here's the form's code which works:

Imports System.Data.OleDb
Public Class Form1

    Private m_Connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb;Persist Security Info=False;")

    Private Sub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
        Dim newPassword As String = NewPasswordTextBox.Text.Trim
        Dim UserID As Integer
        Dim cmd As OleDbCommand
        If newPassword <> String.Empty AndAlso Integer.TryParse(IDTextBox.Text.Trim, UserID) Then
            Dim ds2 As New DataSet()
            Try
                cmd = New OleDbCommand("UPDATE Users SET [Password]=@NewPassword WHERE [ID]=@UserID;", m_Connection)

                'Add the values to the parameters collection to send to the DB
                cmd.Parameters.Add(New OleDb.OleDbParameter("@NewPassword", newPassword))
                cmd.Parameters.Add(New OleDb.OleDbParameter("@UserID", UserID))

                m_Connection.Open() 'Open the connection
                cmd.ExecuteNonQuery() 'Push it to the DB
                MessageBox.Show("New Password successfully set")
            Catch ex As Exception
                'Something went wrong
                MessageBox.Show(ex.ToString)
            Finally
                'Success or not, make sure it's closed
                If m_Connection.State <> ConnectionState.Closed Then m_Connection.Close()
            End Try
        End If
    End Sub
End Class

Just tweak it to what you need, if you're still stuck you can always zip and upload your project to a post here (delete the bin and obj folders before zipping)

 

[Learner VBNET]

Screenshot of Error messages using code supplied

Thanks Brotha!. Its now working the only thing I changed in your original code was to add the square brackets to the SQL Update (that change everything)....now I at least know something about adding parameters to an SQL statement;

cmd = New OleDbCommand("UPDATE Users SET [Password] = @NewPassword Where [ID] = @UserID;", conn2)

 

[JuggaloBrotha]

And it all works out in the end...

 

[jmcilhinney]

The reason you're getting that error message is that "Password" is a reserved word in Access (or more specifically in Jet SQL). Your column name is not being interpreted as an identifier, hence the syntax error. You should escape any identifiers that are reserved words or contain illegal characters, e.g. space, in brackets. Some people like to wrap all identifiers in brackets just to be safe.