I am trying to delete selected record from my database which uses MySQL and Visual Basic 6 but its not working even no error I am getting in it. Here is my code for connection and also for button click

Public rs As New ADODB.Recordset
Public cn As New ADODB.Connection
Public sql As String
Dim dbpath As String

Sub main()
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection

With rs
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
End With

dbpath = "DSN=RetailPOS;"

On Error GoTo errhandler
With cn
    .Open dbpath
    '.CursorLocation = adUseClient
    MDIMAIN.Show
End With
'cn.Open dbpath
  Exit Sub

errhandler:
  MsgBox "Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description, _
  vbCritical
End Sub

button click code

Private Sub RmvBtn_Click()
    If rs.State = 1 Then rs.Close
    sql = "DELETE FROM tbluserinfo WHERE UserGroup = '" & Txt(3).Text & "'"
    cn.Execute (sql)
    MsgBox "The selected user was successfully deleted!", vbInformation + vbOKOnly, ShowProductLabel
End Sub

When this happens to me (does not matter which language) I test my SQL command string at the command prompt. I can't know which SQL you are using but almost all have a terminal interface.

In your IDE, break at link 4 of the button click code and get the sql string. Test it directly against your SQL.

This article has been dead for over six months. Start a new discussion instead.