CODEWITHSUNDEEP
sqldatabasevb.netoledbms-access-2010
Patrick Thorpe
Patrick Thorpe

Reputation: 380

OleDbDataReader says that there is no data in the table it is reading from (VB.NET)

My OleDb access database connection correctly counts the number of rows (entries) in a table in the database, but when it comes to reading every instance of one of the fields using a for loop, I get the error that when getting the username (teacher_id/student_id) of the first entry in the table using databaseReader, there is no data in the databaseReader(1) instance.

The error:
An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll Additional information: No data exists for the row/column.

(And btw I am aware that there is no password verification yet)

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

Upvotes: 1

Views: 955

Answers (1)

Laurence
Laurence

Reputation: 10976

This is the wrong model for a data reader. A data reader exposes a current row, and lets you move around the rows. databaseReader(i) would refer to field i in the current row, not row i as your code thinks.

The standard way to use a data reader is 

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

(I've not used VB.Net, so apologies if I've got some syntax wrong)

You'd be much better building queries that include the value to search for. For example:

Select teacher_id From Teacher_Table Where teacher_id = ?

You'd need to learn about parameter binding to utilize this.

Upvotes: 2

Related Questions