MySQL DELETE 记录在 vb 中不起作用

Question

我正在尝试从使用 MySQL 和 Visual Basic 6 的数据库中删除选定的记录，但即使我没有遇到错误，它也不起作用。这是我的连接代码以及按钮单击代码

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

按钮点击代码

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

Answer 1

由于 UserGroup 是数字，因此您应该使用

sql = "DELETE FROM tbluserinfo WHERE UserGroup = " & Txt(3).Text

您还可以在调用查询之前验证 Txt(3).Text。如果它为空，那么您最终将得到以下查询，该查询将引发错误。

DELETE FROM tbluserinfo WHERE UserGroup =

您还应该检查以确保 Txt(3).Text 是数字。在 IsNumeric() 内部添加 Trim() 将捕获空白字符串，因为它不是数字。

If Not IsNumeric(Trim(Txt(3).Text)) Then
    'Tell the user and don't run the query
End If