ODBC select statement to get a boolean

I'm trying to check whether a Username and Password exist in my MySQL database and if so I need to return true, otherwise false. This is what I have atm:

myconn.Open()

Dim selectSQL As String = "SELECT * 
                             FROM member 
                            WHERE Username = " & objMember.Username & " 
                              AND Password= " & objMember.Password
Dim cmd As New OdbcCommand(selectSQL, myconn)

cmd.ExecuteNonQuery()

If cmd.Parameters.Count = 1 Then
    Return True
Else
    Return False
End If

myconn.Close()
myconn.Dispose()

All I get is 0, even though the Username and Password exist! Or perhaps I'm wrong with my coding?

SOLUTION

myconn.Open()

Dim count As Integer = 0

Dim selectSQL As String = "SELECT COUNT(*)
                             FROM member 
                            WHERE Username = ? 
                              AND Password= ?"
Dim cmd As New OdbcCommand(selectSQL, myconn)

cmd.Parameters.AddWithValue("LidLoginnaam", objLid.LidLoginnaam)
cmd.Parameters.AddWithValue("LidWachtwoord", objLid.LidWachtwoord)

count = Convert.ToInt32(cmd.ExecuteScalar())

If count = 1 Then
    Return True
Else
    Return False
End If
开发者_JAVA百科
myconn.Close()
myconn.Dispose()

Do not use string concatenation to build your SQL queries, use parameters instead.

http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcparameter.aspx

Dim count as Integer = 0

Try
    Dim sql As String = "SELECT COUNT(*) FROM member WHERE Username = @username AND Password = @password"
    Dim cmd As New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@username", objMember.Username)
    cmd.Parameters.AddWithValue("@password", objMember.Password)
    count = Convert.ToInt32(cmd.ExecuteScalar())
Catch ex As Exception
    Console.WriteLine(ex.Message)
End Try

Return (count > 0)

If you don't use the data retrieved from your query, then just use ExecuteScalar to get the number of records that matched your Username and Password.

http://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.executescalar.aspx

This basically returns TRUE if count > 0 (meaning there is a record that matched the Username and Password).

Also check out the distinction between the different command execution methods here: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand(v=vs.71).aspx. You are using ExecuteNonQuery for retrieving records which is incorrect for this purpose.

Hope this helps.

Ignoring the SQL Injection risks, strings in SQL need to be within single quotes:

Dim selectSQL As String = "SELECT m.* 
                             FROM MEMBER m
                            WHERE m.username = '" & objMember.Username & "'
                              AND m.password = '" & objMember.Password & "' "

From a performance perspective, if you aren't interested in the contents of the query then don't return more information than you need to:

Dim selectSQL As String = "SELECT 1
                             FROM MEMBER m
                            WHERE m.username = '" & objMember.Username & "'
                              AND m.password = '" & objMember.Password & "' "

The query will be faster -- if any of the MEMBER columns are BLOB, you could be waiting for a while.

I think you're missing the single quotation in the query:

Dim selectSQL As String = "SELECT * 
                             FROM member 
                            WHERE Username = '" & objMember.Username & "' 
                              AND Password= '" & objMember.Password & "'"

OdbcCommand's Parameter property is used for accessing the collection of parameters to be used when executing a query (more info). It doesn't help you find out how many rows are contained in the query's result set.

If your only concern is determining whether or not the requested member is in the database, change the start of your query to SELECT COUNT(*) FROM..... This revised query will return a single value indicating how many records match your query's criteria.

Execute this query by calling ExecuteScalar. The return value of this method will be the value returned by the above query--the number of rows matching your query's criteria. If the value equals zero, you know that no matching member exists in the database. Use this value instead of "cmd.Parameters.Count" in your "if" statement.

Hope this helps,
Ben