Update Statement Syntax Error

Question

User-1890142112 posted

I'm writing an update statement to update a user's password in the linked Access Database when they input (and confirm the input) of the new password in a textbox on my webpage.

The SQL statement that is being generated is - UPDATE tblUser SET [Password]=[chuck68] WHERE UserID=0000009

With tblUser being the correct name for the table, Password being the correct name for the field I wish to change and UserID being the linked field to lookup the password from.

The entirety of my code is as follows: (with the necessary import statement at the top of the class).

 Protected Sub ChangePasswordButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChangePasswordButton.Click

        Dim sessionUserID As String

        sessionUserID = Session("UserID")

        Dim strSQL As String

        strSQL = "UPDATE tblUser SET [Password]=" & NewPasswordTextBox.Text & " WHERE UserID=" & sessionUserID

        Dim RenewDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0816979.mdb"))

        Dim RenewCommand As New OleDbCommand(strSQL, RenewDataConn)

        RenewDataConn.Open()

        RenewCommand.ExecuteNonQuery()

        RenewDataConn.Close()

    End Sub

 Protected Sub ChangePasswordButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChangePasswordButton.Click

        Dim sessionUserID As String

        sessionUserID = Session("UserID")

        Dim strSQL As String

        strSQL = "UPDATE tblUser SET [Password]=" & NewPasswordTextBox.Text & " WHERE UserID=" & sessionUserID

        Dim RenewDataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/0816979.mdb"))

        Dim RenewCommand As New OleDbCommand(strSQL, RenewDataConn)

        RenewDataConn.Open()

        RenewCommand.ExecuteNonQuery()

        RenewDataConn.Close()

    End Sub

Any help would be greatly appreciated.

Answer 1

User614805505 posted

Dear MattAskham,

What is the error message?

Answer 2

User-1360095595 posted

You probably need single quotes to surround the password textbox's value.

Edit: Better yet, used parameterized queries.

Answer 3

User-1327195235 posted

hi,

use following line

strSQL = "UPDATE tblUser SET [Password]='" & NewPasswordTextBox.Text & "' WHERE UserID=" & sessionUserID

I think you forget to use ' (single quote) before and after the password value

Answer 4

User-1890142112 posted

Thanks for the quick responses.

The error message (even after adding single quotation marks around the password value) is:

Data type mismatch in criteria expression.

Answer 5

User-1890142112 posted

Thanks again for the help everyone but I finally figured it out,

I needed to put the sessionUserID variable inside single quotation marks too as such:

     strSQL = "UPDATE tblUser SET [Password]='" & NewPasswordTextBox.Text & "' WHERE UserID='" & sessionUserID & "'"

Answer 6

User-1199946673 posted

strSQL = "UPDATE tblUser SET [Password]='" & NewPasswordTextBox.Text & "' WHERE UserID='" & sessionUserID & "'"

 

If you're writing SQL statemements like this you could end up in a lot of trouble! Let me guess, to login you're using something like:

"SELECT * FROM tblUser WHERE username = '" & UsernameTextBox.Text & "' AND [Password] = '" & PasswordTextBox.Text & "'"

I think you should read this:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET

Answer 7

User-1890142112 posted

I've heard bits and pieces about SQL injection but no fully understood the benefits of protecting against it until now.

The project I'm working on is only a university one at the moment so I think it's generally a safe assumption to assume that the markers will not look too deep into the possibility of such an attack.

It's something I will bear in mind when next writing similar code for practical use, thanks for the heads up.