如何检查MySQL中字段中的特定值是否存在

Question

我正在寻找可以帮助我检查MySQL中的特定列并返回其值（如果它已经存在）的代码。我正在开发 ForgotPassword 模块，因此当用户单击"忘记密码"时，会出现一个表单，要求用户输入他/她的用户名。一旦他/她完成，它将检查系统以查看输入的用户名是否存在。我在堆栈溢出上找到了一些代码：

Private Sub btnCheckUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckUser.Click
    If IsUserExist(userName:=True) Then
        MsgBox("user exists")
    Else
        MsgBox("user does not exists")
    End If
End Sub
Private Function IsUserExist(ByVal userName As String) As Boolean
    Dim query As String
    Dim returnValue As Boolean = False
    query = "SELECT username FROM dbase.tblusers WHERE username = @username "
    Using conn As New MySqlConnection("server=localhost; userid=root; password=root; database=dbase")
        Using cmd As New MySqlCommand()
            With cmd
                .Connection = conn
                .CommandText = query
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@username", txtUsername.Text)
            End With
            Try
                conn.Open()
                If CInt(cmd.ExecuteScalar()) > 0 Then
                    returnValue = True
                End If
            Catch ex As MySqlException
                MsgBox(ex.Message)
                returnValue = False
            Finally
                conn.Close()
            End Try
        End Using
    End Using
    Return returnValue
End Function

但是这段代码给了我一个错误Conversion from string "username" to type 'Integer' is not valid If CInt(cmd.ExecuteScalar()) > 0 Then.

Answer 1

MySqlCommand.ExecuteScalar返回查询返回的第一行的第一列。这意味着（对于您的实际查询）一个 NULL（如果用户名不存在）或具有相同用户名的字符串作为 where 条件的参数传递。在任何情况下都不是整数。
所以你只需要检查返回的对象是否为 null（VB.NET 中没有任何内容）。

Dim result = cmd.ExecuteScalar()
if result IsNot Nothing then
  ... user exists....

解决您问题的另一种方法可能是返回包含您的用户名的行数的 COUNT

query = "SELECT COUNT(*) FROM dbase.tblusers WHERE username = @username"

然后，转换为返回值 ExecuteScalar 的整数将起作用

最后，关于语法If IsUserExist(userName:=True) Then。

这可能只是因为您将项目的"选项严格配置"设置为"关闭"。
使用此配置，布尔值True将自动转换为 IsUserExist 中参数所需的数据类型。因此，此函数接收等于文字字符串"True"username参数。当您尝试在表中搜索一些真实的用户名时，这并不真正有用。
似乎您需要在按钮单击代码中的某个位置获取该值，可能来自文本框。

Private Sub btnCheckUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckUser.Click
    Dim userName = txtUsername.Text
    If IsUserExist(userName) Then
        MsgBox("user exists")
    Else
        MsgBox("user does not exists")
    End If
End Sub

当然，现在你应该使用 IsUserExist 中收到的变量userName来初始化参数

With cmd
   .Parameters.AddWithValue("@username", userName)
   ....

Answer 2

Private Sub btnCheckUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCheckUser.Click
    If IsUserExist(txtUsername.Text) Then #Use value from textbox on your form, use '#' for comments
        MsgBox("user exists")
    Else
        MsgBox("user does not exists")
    End If
End Sub
Private Function IsUserExist(ByVal userName As String) As Boolean
    Dim query As String
    Dim returnValue As Boolean = False
    query = "SELECT username FROM dbase.tblusers WHERE username = @username "
    Using conn As New MySqlConnection("server=localhost; userid=root; password=root; database=dbase")
        Using cmd As New MySqlCommand()
            With cmd
                .Connection = conn
                .CommandText = query
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@username", userName) # I think here should user value from function's parameter, not from textbox
            End With
            Try
                conn.Open()
                If CInt(cmd.ExecuteScalar()) > 0 Then
                    returnValue = True
                End If
            Catch ex As MySqlException
                MsgBox(ex.Message)
                returnValue = False
            Finally
                conn.Close()
            End Try
        End Using
    End Using
    Return returnValue
End Function