Check if record exist in SQL database

[henryz_box]

I found this along my way of trying to learn how to use databases

1."SELECT usname, password FROM userinfo "  & 
2." WHERE usname = '" & textbox1.Text & "'" &
3."   AND [password] = '" & textbox2.Text   & "'"

I would like to know how I would execute code based on the result. I've put this code directly below the cmd select. I'm a newby so be nice. Using VB.Net 2010 and SQL database on win7 system. Thanks for your help.

If UserName = TxtUserName.Text and password = TxtPassword.Text Then
    Do this bit of code
Else
    Do this bit if code
End If

I get an error that Username has not been declared

[airhalynn101]

if you want to use the retrieved data from your query, you should use datareader first.

Dim dr As SqlClient.SqlDataReader
dr.Read()

If I understand your question correctly, you'd want to try this for your code:

Dim com As New SqlClient.SqlCommand
Dim dr As SqlClient.SqlDataReader

dr = com.ExecuteReader
dr.Read()

If dr!usname = TxtUserName.Text Then
     'some codes
Else
     'some codes
EndIf

See if this works.

[Reverend Jim]

You don't need a datareader. All you need to know is if you have a record that matches the username and password.

cmd.CommandText = "SELECT COUNT(*) FROM userinfo" &
                  " WHERE usname =     '" & textbox1.Text & "'" &
                  "   AND [password] = '" & textbox2.Text & "'"

If cmd.ExecuteScalar <> 0 Then
    'user is valid
Else
    'user is not valid
End If

But you should really use parameterized queries

[henryz_box]

Here is the actual Code for the sub that checks if a record exist.

        Using con As New MySqlConnection(SQLConnect)
            con.Open()

            Using cmd As MySqlCommand = con.CreateCommand

                cmd.CommandText = "SELECT last_name,first_name FROM player_log " & " WHERE last_name =  ' " & TxtLastName.Text & " ' " & " AND [first_name] =  ' " & TxtFirstName.Text & " ' "

                Dim com As New MySqlCommand
                Dim dr As MySqlDataReader

                dr = com.ExecuteReader
                dr.Read()

                If dr!last_name Is TxtLastName.Text And dr!first_name Is TxtFirstName.Text Then
                    'For testing
                    MsgBox("Name exist")
                Else
                    MsgBox("Name does NOT exist")
                End If

            End Using
        End Using

I'm getting "Connection must be valid and open" error at
dr = com.ExecuteReader

[Reverend Jim]

I would code it as

Private Function UserExists(fname As String, lname As String) As Boolean

    Dim con As New MySqlConnection(SQLConnect)
    con.Open()

    Dim cmd As MySqlCommand = con.CreateCommand
    cmd.CommandText = "SELECT COUNT(*) FROM player_log" &
                      " WHERE last_name  = '" & lname & "'" &
                      "   AND first_name = '" & fname & "'"

    UserExists = cmd.ExecuteScalar > 0
    MsgBox(IIf(UserExists, "User exists", "User does not exist"))

    con.Close()

End Function

and call it as

If UserExists(txtFirstName.Text, txtLastName.Text) Then

I'm assuming your objects for MySql are correct. I use MS SQL.

[henryz_box]

I'm getting this error

Option Strict On disallows operands of type Object for operator '<>'. Use the 'Is' operator to test for object identity.

Here: cmd.ExecuteScalar > 0

Also how would I Call this fuction. I tried Call Private Function UserExists and got errors with that.

[henryz_box]

Know how to call the fuction. Now about that other error.

[henryz_box]

Whew!!! Had to use this.

UserExists = Convert.ToInt32(cmd.ExecuteScalar()) > 0

Seems to be working, still needs futher testing.
Thanks for pointing me in the right direction. I was truely lost. So many thanks to you. You are the GREATEST!!!!

[henryz_box]

OK....OK... I GIVE!

If cmd.ExecuteScalar <> 0 Then

Still is not working properly. I get 0 even if the record exist. I know it has to be something I'm missing. Thanks in advance for your help.