SQL Program UPDATE Record Error

Question

I'm working in a small SQL Database program. The programs just there to view, edit and update the database records. Everything is working remarkably well considering I've never tried something like this before. I've managed to get the Add Records, Refresh Records and Delete Records functions working flawlessly. However, I've hit a little bump when trying to UPDATE a selected record.

To clarify, the SQL Table is displayed in a list view, from this list view the end-user can select a particular-record and either edit or delete it.

The edit button opens a new form window with text fields which are automatically filled with the current information of that record.

The code for the edit record form is:

Private Sub frmEdit_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    intDB_ID_Selected = CInt(frmMain.lvRec.SelectedItems(0).Text)
    Call dispCaption()
    Call dispInfo()     'Display the info of the selected ID


End Sub

Private Sub dispInfo()
    SQL = "Select * from PersonsA " & _
          "where Members_ID=" & intDB_ID_Selected & ""

    With comDB
        .CommandText = SQL
        rdDB = .ExecuteReader
    End With
    If rdDB.HasRows = True Then
        rdDB.Read()

        Me.midtxt.Text = rdDB!Members_ID.ToString.Trim
        Me.gttxt.Text = rdDB!Gamer_Tag.ToString.Trim
        Me.sntxt.Text = rdDB!Screenname.ToString.Trim
        Me.fntxt.Text = rdDB!First_Name.ToString.Trim
        Me.lntxt.Text = rdDB!Last_Name.ToString.Trim
        Me.dobtxt.Text = rdDB!DoB.ToString.Trim
        Me.dobtxt.Text = rdDB!DoB.ToString.Trim
        Me.emailtxt.Text = rdDB!E_Mail_Address.ToString.Trim
        Me.teamptxt.Text = rdDB!Position.ToString.Trim
        Me.ugctxt.Text = rdDB!Cautions.ToString.Trim
        Me.recordtxt.Text = rdDB!Record.ToString.Trim
        Me.eventatxt.Text = rdDB!Event_Attendance.ToString.Trim
        Me.Mstattxt.Text = rdDB!Member_Status.ToString.Trim

    End If
    rdDB.Close()
End Sub

Private Sub dispCaption()

End Sub

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
    Call disControl()
    'Validation
    If invalidUpdateEntry() = True Then
        Call enaControl()
        Exit Sub
    End If
    'Prompt the user if the record will be updated
    If MsgBox("Are you sure you want to update the selected record?", CType(MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2 + MsgBoxStyle.Question, MsgBoxStyle), "Update") = MsgBoxResult.Yes Then
        'Update query
            SQL = "Update PersonsA" & _
                "SET Members_ID='" & Me.midtxt.Text.Trim & "'," & _
                  "Gamer_Tag='" & Me.gttxt.Text.Trim & "'," & _
                  "Screenname='" & Me.sntxt.Text.Trim & "'," & _
                  "First_Name='" & Me.fntxt.Text.Trim & "'," & _
                  "Last_Name='" & Me.lntxt.Text.Trim & "'," & _
                  "DoB='" & Me.dobtxt.Text.Trim & "'," & _
                  "E_Mail_Address='" & Me.emailtxt.Text.Trim & "'," & _
                  "Position='" & Me.teamptxt.Text.Trim & "'," & _
                  "U_G_Studio='" & Me.ugptxt.Text.Trim & "'," & _
                  "Cautions='" & Me.ugctxt.Text.Trim & "'," & _
                  "Record='" & Me.recordtxt.Text.Trim & "'," & _
                  "Event_Attendance='" & Me.eventatxt.Text.Trim & "'," & _
                  "Member_Status='" & Me.Mstattxt.Text.Trim & "'" & _
                "WHERE Members_ID='" & intDB_ID_Selected & "'"
        Call execComDB(SQL)     'Execute the query
        Me.Close()
        '*** Refresh the list
        SQL = "Select * from PersonsA "
        Call frmMain.dispRec(SQL)
        '--- End of refreshing the list
        Exit Sub
    Else
        Call enaControl()
    End If
End Sub

As I've said, I've been able to do everything else using an extremely similar method, but when I try to UPDATE the record I get an error saying

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Incorrect syntax near 'Members_ID'.

I know it's this line that's the problem

"WHERE Members_ID='" & intDB_ID_Selected & "'"
            Call execComDB(SQL)     'Execute the query

But referencing 'intDB_ID_Selected' has always worked before, and it's been set-up on the update form records load as intDB_ID_Selected = CInt(frmMain.lvRec.SelectedItems(0).Text)

I know this is a huge thread but if anyone could steer me in the right direction WITHOUT telling me to re-write the entire statement I'd be forever grateful.

EDIT1: I fixed the comma before the WHERE clause, but I'm still getting the same error.

Answer 1

On any error like this, use debugging provided with Visual Studio. Inspect the value of SQL, paste into MS SQL Management Studio - it has syntax highlight, and you should be able to spot the error easily.

To prevent further issues (including SQL injection vulnerability), separate this query into an embedded resource, and use parameters. Then it's easy to view, maintain (you can copy/paste between SQL Mgmt Studio and VS), and ultimately use it in code.

A side note, you don't need to use Call in VB.NET, just put a method name with parenthesis.

Answer 2

Missing a space between

 "Update PersonsA " & _
 "SET Members_ID= ....

and (as already pointed out) a comma not needed before the WHERE

Said that, do a favor to yourself and to your users. Do not use string concatenation to build a sql command. Use always a parameterized query.

Just as an example

SQL = "Update PersonsA SET Members_ID=@id, Gamer_Tag=@tag, Screenname=@screen," & _
      "First_Name=@fname,Last_Name=@lname,DoB=@dob,E_Mail_Address=@email," & _
      "Position=@pos,U_G_Studio=@studio,Cautions=@caution,Record=@rec," & _
      "Event_Attendance=@event, Member_Status=@stat " & _
      "WHERE Members_ID=@id"
SqlCommand cmd = new SqlCommand(SQL, connection)
cmd.Parameters.AddWithValue("@id", Me.midtxt.Text.Trim)
..... so on for the other parameters defined above ....
cmd.ExecuteNonQuery();

Answer 3

Change "Member_Status='" & Me.Mstattxt.Text.Trim & "'," & _

to "Member_Status='" & Me.Mstattxt.Text.Trim & "'" & _

Looks like it was just an extra rogue comma!