How to check if a record exists in an Access datab

问题:

I'm trying a new approach for a project that I'm working on and I'm just starting to learn about Access Databases. I using VB.net and my question is: How do you see if a record exists in the table of the database. I thought I had it understood but that is not the case. I'm creating a login and I want it to check if the Username that they typed in exists before it tries to compare what you typed with what's in the database. I see alot of questions on how to do this...but not for VB.net and MS Access

Here's my code:

Imports System.Data.OleDb
Public Class LoginForm1
    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Public myConnection As OleDbConnection = New OleDbConnection
    Public dr As OleDbDataReader
    Dim Errors As String
    Public Sub AccessAccountDatabase()
        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        dataFile = "C:\Users\Richard\Documents\Visual Studio 2010\Projects\CybSol Journal Database\CybSol Journal Database\cgi-bin\Data.mdb"
        connString = provider & dataFile
        myConnection.ConnectionString = connString
        Errors = ""
        Try
            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM Accounts WHERE Username='" & UsernameTxt.Text & "' AND Password='" & PasswordTxt.Text & "'"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            dr.Read()

            If UsernameTxt.Text = dr("Username").ToString AndAlso PasswordTxt.Text = dr("Password").ToString Then
                Dim Welcome As String = "SELECT * FROM Accounts WHERE Real_Name=" & "Username"
                MsgBox("Welcome back " & dr("Real_Name") & "!")
            Else
                MsgBox("Login Failure")
            End If
            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub OkayBtn_Click(sender As System.Object, e As System.EventArgs) Handles OkayBtn.Click
        AccessAccountDatabase()
    End Sub
End Class

So now my question is... How do you get it to check if a record exists in the database, because when you type in the correct information (The correct username and password that exists in the database) it says welcome and all. But when you type in the wrong Username and/or Password it doesn't work. Without the "Try Catch" statement the program just freezes. With the try catch it states this:

System.InvalidOperationException: No data exists for the row/column.
   at System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)
   at System.Data.OleDb.OleDbDataReader.GetValue(Int32 ordinal)
   at System.Data.OleDb.OleDbDataReader.get_Item(String name)
   at CybSol_Journal_Database.LoginForm1.AccessAccountDatabase() in c:\users\richard\documents\visual studio 2010\Projects\CybSol Journal Database\CybSol Journal Database\LoginForm1.vb:line 36

Addition information: line 36 is this: If UsernameTxt.Text = dr("Username").ToString AndAlso PasswordTxt.Text = dr("Password").ToString Then

回答1:

First problem:

PASSWORD is a reserved keyword in Access. You should encapsulate in square brackets:

"SELECT * FROM Accounts WHERE Username='" & UsernameTxt.Text & _
"' AND [Password]='" & PasswordTxt.Text & "'" 

Second problem:

NEVER use string concatenation to create sql text. ALWAYS use parameters

 str = "SELECT * FROM Accounts WHERE Username=? AND [Password]=?"   
 Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)   
 cmd.Parameters.AddWithValue("user", UserNameTxt.Text)
 cmd.Parameters.AddWithValue("pass", PasswordTxt.Text)
 dr = cmd.ExecuteReader   

Why? look here what could happen if you concatenate strings from user input

Third problem: Test if your command returns rows

 If dr.Read() Then
    ......

 End if  

回答2:

I added some Using statements so you don't have to manually close the connections. Also, I parameterized the SQL statement to prevent SQL Injection.

 Public Class LoginForm1
      Dim provider As String
      Dim dataFile As String
      Dim connString As String
      'Public myConnection As OleDbConnection = New OleDbConnection
      'Public dr As OleDbDataReader
      Dim Errors As String
      Public Sub AccessAccountDatabase()
        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
        dataFile = "C:\Users\Richard\Documents\Visual Studio 2010\Projects\CybSol Journal Database\CybSol Journal Database\cgi-bin\Data.mdb"
        connString = provider & dataFile
        myConnection.ConnectionString = connString
        Errors = ""
        Try
          Using myConnection As OleDbConnection = New OleDbConnection(connString)
            myConnection.Open()
            Dim str As String
            str = "SELECT * FROM Accounts WHERE Username=@USER AND [Password]=@PWD "
            Using cmd As OleDbCommand = New OleDbCommand(str, myConnection)
              cmd.Parameters.AddWithValue("@USER", UsernameTxt.Text)
              cmd.Parameters.AddWithValue("@PWD", PasswordTxt.Text)
              Using dr As OleDbDataReader = cmd.ExecuteReader
                If dr.HasRows Then
                  dr.Read()
                  If UsernameTxt.Text = dr("Username").ToString AndAlso PasswordTxt.Text = dr("Password").ToString Then
                    Dim Welcome As String = "SELECT * FROM Accounts WHERE Real_Name=" & "Username"
                    MsgBox("Welcome back " & dr("Real_Name") & "!")
                  Else
                    MsgBox("Login Failure")
                  End If
                Else
                  MsgBox("Login Failure")
                End If
              End Using
            End Using
          End Using
        Catch ex As Exception
          MsgBox(ex.ToString)
        End Try


      End Sub

      Private Sub OkayBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OkayBtn.Click
        AccessAccountDatabase()
      End Sub
    End Class

回答3:

You're on the right track. The OleDbDataReader.Read returns a boolean indicating whether or not it successfully read an existing row. Therefore, you can check to see if it returned True before trying to read the record. For instance:

If dr.Read() Then
    If UsernameTxt.Text = dr("Username").ToString AndAlso PasswordTxt.Text = dr("Password").ToString Then
    Dim Welcome As String = "SELECT * FROM Accounts WHERE Real_Name=" & "Username"
        MsgBox("Welcome back " & dr("Real_Name") & "!")
    Else
        MsgBox("Login Failure")
    End If
End If

Also, I feel I should at least mention that storing a password in plain text is never a good idea.

回答4:

You don't have to check for the username and password in your code again since if does not match in the database, no rows will be returned.

You can simply do

dr = cmd.ExecuteReader
If dr.HasRows Then
   //it matched
Else
   //it didn't match. could not log in
End If

Your approach is below if you still want to keep it but it's not necessary

dr = cmd.ExecuteReader
If dr.HasRows Then

    dr.Read()

    If UsernameTxt.Text = dr("Username").ToString AndAlso PasswordTxt.Text = dr("Password").ToString Then

    Else

    End If

End If

回答5:

Use the Read() method on your DataReader (note that this keeps your connection to the database open and you'll be unable to execute any other commands on the database while your DataReader is still Reading.

If String.Compare(dr("Username").ToString(), UsernameTxt.Text, true) AndAlso String.Compare(dr("Password").ToString(), PasswordTxt.Text.ToString() Then
    ' The username and password for the record match
    ' the input from the login form
    ProcessLogin()
Else
    ' Invalid username or password, send an error
End If