sql - OleDbDataReader 说它正在读取的表中没有数据 (VB.NET)

Question

我的 OleDb 访问数据库连接正确地计算了数据库表中的行(条目)数，但是当涉及到使用 for 循环读取其中一个字段的每个实例时，我得到的错误是在获取表中第一个条目的用户名 (teacher_id/student_id) 使用 databaseReader，databaseReader(1) 实例中没有数据。

错误:
System.Data.dll 中出现类型为“System.InvalidOperationException”的未处理异常附加信息:行/列不存在数据。

(顺便说一句，我知道还没有密码验证)

Imports System.Data
Imports System.Data.OleDb
Public Class LoginForm
    Const databaseLocation As String = "C:\Users\Patrick\Documents"
    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)     Handles OKButton.Click
        Dim enteredUsername As String = UsernameTextBox.Text
        Dim enteredPassword As String = PasswordTextBox.Text
        Login(enteredUsername, enteredPassword)
    End Sub

    Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CancelButton.Click
        Me.Close()
    End Sub

    Sub Login(ByRef username As String, ByRef password As String)
        Dim isUsernameVerified As Boolean
        'This is the admin's username and password, and should not be shown to any other users
        If username = "adminentry" And password = "iamanadmin" Then
            MenuForm.Show()
            Me.Close()
        End If
        isUsernameVerified = VerifyUsernameAndPassword(username, password)
        If isUsernameVerified = True Then
            MenuForm.Show()
            Me.Close()
        Else
            LoginComboBox.Text = "Username or password not found"
        End If

    End Sub

    Function VerifyUsernameAndPassword(ByRef usernameForVerification As String, ByRef passwordForVerification As String)
        'This string tells the connection where the database is, and which provider to use
        Const connectionString As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & databaseLocation & "\ProjectDatabase.accdb")
        Dim sqlQuery As String
        Dim verified As Boolean
        'database connection required in order to verify username and password inputs
        Using connectionToDatabase As New OleDbConnection(connectionString)
            Dim numberOfEntries As Integer
            'Open the database connection
            connectionToDatabase.Open()
            If LoginComboBox.SelectedItem = "Staff login" Then
                'sql code for counting number of staff ID's
                sqlQuery = "SELECT COUNT(*) AS sqlQuery FROM Teacher_Table"
            Else
                'sql code for counting number of student ID's
                sqlQuery = "SELECT COUNT(*) AS sqlQuery FROM Student_Table"
            End If

            Dim getNumberOfEntriesCommand As New OleDbCommand(sqlQuery, connectionToDatabase)
            'executes counting sql code
            numberOfEntries = getNumberOfEntriesCommand.ExecuteScalar

            If LoginComboBox.SelectedItem = "Staff login" Then
                'sql code for getting staff ID's
                sqlQuery = "SELECT teacher_id FROM Teacher_Table"
            Else
                'sql code for getting student ID's
                sqlQuery = "SELECT student_id FROM Student_Table"
            End If

            Dim loginVerificationCommand As New OleDbCommand(sqlQuery, connectionToDatabase)
            'executes verification sql code
            Dim databaseReader As OleDbDataReader = loginVerificationCommand.ExecuteReader()

            For i = 1 To numberOfEntries
                If databaseReader(i) = usernameForVerification Then
                    verified = True
                End If
            Next
        End Using
        If verified = True Then
            Return True
        Else
            Return False
        End If
    End Function

End Class

Answer 1

这是数据读取器的错误模型。数据读取器公开当前行，并允许您在行中移动。 databaseReader(i) 将引用当前行中的字段 i，而不是您的代码认为的第 i 行。

使用数据阅读器的标准方法是

Do While databaseReader.Read
    'Do something with the current row
    If databaseReader(0) = usernameForVerification Then
        verified = True
        Exit Loop
    End If
Loop
databaseReader.Close

(我没有使用过 VB.Net，如果语法有误，请见谅)

您最好构建包含要搜索的值的查询。例如:

Select teacher_id From Teacher_Table Where teacher_id = ?

您需要了解参数绑定(bind)才能使用它。