How to check whether particular value in field in MySQL exists

Question

I'm looking for code that will help me check a particular column in MySQL and return its value if it already exists. I'm working on ForgotPassword module, so when the user clicks "Forgot Password", a form appears that will ask the user to input his/her username. Once he's/she's done, it will check the system to see if the entered username exists. I found some code here on Stack Overflow:

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

But this code gives me an error Conversion from string "username" to type 'Integer' is not valid at If CInt(cmd.ExecuteScalar()) > 0 Then.

`If IsUserExist(userName:=True) Then` Is it VB??? I never encountered such construction. In that string you should use `username` variable Like `IsUserExist(TextEdit1.Text)` or something like — Mark Zucchini, Dec 31 '14 at 10:08
@MarkZucchini, Well it doesnt give an error. And i never encountered dat construction before too. Do u know how can I fix dat? :) — Fvcundo, Dec 31 '14 at 10:12
In code, there are string `.Parameters.AddWithValue("@username", txtUsername.Text)` it seems that this takes a username value from txtUsername textbox, so userName as parameter doesn't matter. Change this line as `.Parameters.AddWithValue("@username", userName)` — Mark Zucchini, Dec 31 '14 at 10:14
And yes, in btnCheckUser() use proper value, not `userName:=True` — Mark Zucchini, Dec 31 '14 at 10:17
@MarkZucchini note that the `:=` is named argument syntax; it's used to rearrange arguments in calls and/or link terse argument values with explanatory parameter names. In the original code it was needless and linked with a completely bogus value, but it has legitimate use in VB. — Nathan Tuggy, Jan 25 '15 at 03:58

Steve · Accepted Answer · 2015-01-01T11:57:04.853

MySqlCommand.ExecuteScalar returns the first column of the first row returned by your query. This means (for your actual query) a NULL (if the username doesn't exist) or a string with the same username passed as parameter for the where condition. In any case not an integer.
So you just need to check if the object returned is null(Nothing in VB.NET).

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

Another approach at your problem could be getting back a COUNT of the number of rows that contains your username

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

and then, the conversion to an integer of the return value of ExecuteScalar, will work

Finally, about the syntax If IsUserExist(userName:=True) Then.

This probably works just because you have the Option Strict configuration of your project set to Off.
With this configuration the boolean value True is automatically converted in the datatype expected by the parameter in IsUserExist. So this function receives the parameter username equals to the literal string "True". Not really useful when you try to search some real username in your table.
It seems that you need to get that value somewhere in your buttonclick code, probably from a TextBox.

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

Of course, now you should use the variable userName received in IsUserExist to intialize the parameter

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

,how can enable d option strict sir? BTW, it worked. :) TY so much. I replaced the query from "SELECT username" to "SELECT COUNT(*). — Fvcundo, Dec 31 '14 at 10:30
In the link above is explained the reasons and the effects of Option Strict and various methods to change it. At the beginning t could be difficult, but Option Strict On pays off on the long run — Steve, Dec 31 '14 at 10:33

score 0 · Answer 2 · edited Jan 27 '17 at 02:28

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

Yes it worked. :) I believe my IF BLOCK doesnt function well when I put "IsUserExist(userName:=True)". TY so much. now i can move on. — Fvcundo, Dec 31 '14 at 10:33

How to check whether particular value in field in MySQL exists

2 Answers2