如何检查Access数据库中是否存在记录 [英] How to check if a record exists in an Access database

问题描述

我正在为一个正在开发的项目尝试新的方法，我刚刚开始学习Access数据库。我使用 VB.net ，我的问题是：你如何看待数据库表中是否存在记录。我以为我有它的理解，但不是这样。我创建一个登录，我希望它检查他们键入的用户名是否存在，在尝试比较您输入的数据与数据库中的内容。我看到很多关于如何做到这一点的问题...但不是VB.net 和 MS Access

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

这里是我的代码：

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

现在我的问题是...如何检查数据库中是否存在记录，因为当您输入正确的信息（正确的用户名和密码存在于数据库中）它说欢迎和所有。但是，当您输入错误的用户名和/或密码，它不工作。没有Try Catch语句，程序只是冻结。使用try catch它说明：

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

添加信息：第36行是： Text = dr（Username）ToString AndAlso PasswordTxt.Text = dr（Password）。ToString Then

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

推荐答案

第一个问题：

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

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

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

第二个问题：

不要使用字符串连接创建sql文本。 ALWAYS使用参数

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  

这篇关于如何检查Access数据库中是否存在记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！