locked
Update Statement Syntax Error RRS feed

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

    Friday, April 23, 2010 9:36 AM

Answers

  • 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 & "'"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 23, 2010 10:13 AM

All replies

  • User614805505 posted

    Dear MattAskham,

    What is the error message?

    Friday, April 23, 2010 9:50 AM
  • User-1360095595 posted

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

    Edit: Better yet, used parameterized queries.

    Friday, April 23, 2010 9:52 AM
  • 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

    Friday, April 23, 2010 9:53 AM
  • 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.

    Friday, April 23, 2010 10:00 AM
  • 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 & "'"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 23, 2010 10:13 AM
  • 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

    Friday, April 23, 2010 6:34 PM
  • 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.

    Friday, April 23, 2010 7:10 PM