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
.
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)
....