Member Avatar for dru987

i have a very simple access DB and im tryin to update the records but the update never works. im using asp.net/vb.net and i've include the execute in a try/catch block and it always prints the sql string (the catch). here is my code, perhaps i have an error that ive missed:

SQL = "UPDATE members " & _
         "SET username = '" & txtUsername.Text.Trim() & "', " & _
         "password = '" & txtPassword1.Text.Trim() & "', " & _
         "first_name = '" & txtFirstName.Text.Trim() & "', " & _ 
         "last_name = '" & txtLastName.Text.Trim() & "', " & _
         "email = '" & txtEmail.Text.Trim() & "'" & _
         "WHERE first_name = " & Session("first_name") & _
         "AND last_name = " & Session("last_name") & ""

' Check if connection to DB is already open
If MyConn.State = ConnectionState.Closed Then
    MyConn.Open()
End If

Dim MyCmd As New OleDbCommand(SQL, MyConn)

' Try/Catch...in case DB error
Try
    MyCmd.ExecuteNonQuery()
    MyConn.Close()
    MyConn.Equals("")

    Response.Redirect("default.aspx")

Catch ex As Exception
    profile_error.Text = SQL 
    profile_error.Style("Color") = "red"
End Try

im also adding a user ID soon and will be changing the where to check for the unique ID.

  • 3 Contributors
  • 2 Replies
  • 217 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by weilander

Dani AI

Generated

Most likely cause: the generated SQL is malformed. Common culprits in this thread are missing spaces when concatenating, string values inserted without surrounding quotes (causing "syntax error" or "type mismatch" from Access), and the odd use of MyConn.Equals("") which does nothing — it does not close or release the connection. First/last name is not a reliable unique key; switching the WHERE to a numeric ID will prevent accidental multi-row updates.

Checklist to diagnose and fix:

  • Log both the SQL string and the exception message/stack (ex.Message / ex.ToString()) so the exact error is known. As suggested, dumping the SQL is useful.
  • Verify Session values are not Nothing and have the expected types. Null or non-quoted strings will break the SQL.
  • Avoid building SQL by concatenation. Use parameterized commands to handle embedded apostrophes and types.
  • Wrap table/column names that might be reserved (like password) with square brackets: [password].
  • Replace MyConn.Equals("") with proper disposal (Using blocks or Close/Dispose).
  • Prefer an ID-based WHERE clause rather than first/last name.

Safe pattern (VB.NET / OleDb for Access):

Dim sql As String = "UPDATE [members] SET [username]=?, [password]=?, [first_name]=?, [last_name]=?, [email]=? WHERE [id]=?"
Using cn As New System.Data.OleDb.OleDbConnection(connString)
    Using cmd As New System.Data.OleDb.OleDbCommand(sql, cn)
        cmd.Parameters.AddWithValue("p1", txtUsername.Text.Trim())
        cmd.Parameters.AddWithValue("p2", txtPassword1.Text.Trim())
        cmd.Parameters.AddWithValue("p3", txtFirstName.Text.Trim())
        cmd.Parameters.AddWithValue("p4", txtLastName.Text.Trim())
        cmd.Parameters.AddWithValue("p5", txtEmail.Text.Trim())
        cmd.Parameters.AddWithValue("p6", CInt(Session("userID")))
        Try
            cn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            profile_error.Text = ex.Message & " | SQL: " & sql
            profile_error.Style("Color") = "red"
        End Try
    End Using
End Using

Note: 's missing-space hint points to the exact kind of syntax error that occurs when fragments are concatenated without spaces. Using parameters and the Using pattern addresses that class of problems and improves security and reliability.

Member Avatar for Estuardo

G'd evening Dru!
The sintax seems to be fine, but we don't know the values you are sending nor their types. I would sugest to store (just for tests purposes) the sql string in a varible and then write its content in the inmediate window.
Good luck
Estuardo

Member Avatar for weilander

What is the error-message?

Maybe you have to put an extra spcace before the last line (" AND last_name = " & Session("last_name") & "")

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.