database 如何检查 Access 数据库中是否存在记录

How to check if a record exists in an Access database

提问by Richard Paulicelli

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.netand 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 andMS Access

我正在为我正在从事的项目尝试一种新方法，而且我刚刚开始了解 Access 数据库。我使用VB.net，我的问题是：如何查看数据库表中是否存在记录。我以为我明白了，但事实并非如此。我正在创建一个登录名，我希望它在尝试将您输入的内容与数据库中的内容进行比较之前检查他们输入的用户名是否存在。我看到很多关于如何执行此操作的问题...但不适用于 VB.net和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:

所以现在我的问题是......你如何让它检查数据库中是否存在记录，因为当你输入正确的信息（数据库中存在的正确用户名和密码）时，它会说欢迎和所有。但是当您输入错误的用户名和/或密码时，它不起作用。如果没有“Try Catch”语句，程序就会冻结。使用 try catch 它说明了这一点：

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

补充信息：第36行是这样的： If UsernameTxt.Text = dr("Username").ToString AndAlso PasswordTxt.Text = dr("Password").ToString Then

回答by Steve

First problem:

第一个问题：

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

PASSWORD 是Access 中的保留关键字。您应该封装在方括号中：

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

Second problem:

第二个问题：

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

永远不要使用字符串连接来创建 sql 文本。始终使用参数

 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 herewhat could happen if you concatenate strings from user input

为什么？看看这里如果你从用户输入连接字符串会发生什么

Third problem: Test if your command returns rows

第三个问题：测试你的命令是否返回行

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

 End if  

回答by Holger Brandt

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

我添加了一些Using语句，因此您不必手动关闭连接。此外，我参数化了 SQL 语句以防止 SQL 注入。

 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

回答by Steven Doggart

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

你在正确的轨道上。将OleDbDataReader.Read返回一个布尔值指示是否成功地读取现有行。因此，您可以True在尝试读取记录之前检查它是否返回。例如：

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.

另外，我觉得我至少应该提到以纯文本形式存储密码从来都不是一个好主意。

回答by codingbiz

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

回答by user6233060

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.

在您的 DataReader 上使用 Read() 方法（请注意，这会使您与数据库的连接保持打开状态，并且当您的 DataReader 仍在读取时，您将无法在数据库上执行任何其他命令。

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