0

Hi everyone Could someone please help me, I need to loop through all the records in a column in my access database, Currently my program only views the first record in the column and does not go any further, I tried a While loop but I get a NullException error and it says there is no data in my column, her is my code thus far.

Imports System.Data.OleDb
Imports System.Security
Imports System.Security.Principal.WindowsIdentity
Public Class form_Login
    Dim dbReader As OleDbDataReader
    Dim dbConnect As New OleDb.OleDbConnection
    Dim dbCommand As OleDbCommand
    Dim Line As String = Environment.NewLine
    Dim ValidUser As Boolean
    Dim CurrentUser As String = GetCurrent.Name
    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        MessageBox.Show(CurrentUser)
        dbReader = dbCommand.ExecuteReader
        dbReader.Read()
        If dbReader("Abnum") = CurrentUser Then
            ValidUser = True
        End If
        MessageBox.Show(dbReader("Abnum"))
        If ValidUser = True Then
            Dim mainForm As New form_MainMenu
            btnLogin.Enabled = False
            mainForm.Show()
            Me.Hide()
        Else
            lblMessage.Text = "You are not a Registered User for this Application"
        End If
    End Sub
    Private Sub form_Login_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            dbConnect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Database\WorkWatchMain.mdb"
            dbConnect.Open()
            dbCommand = New OleDbCommand("SELECT * FROM Table_Login", dbConnect)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

Abnum is my column name, if you were wondering, and don't worry about the connection, that works perfectly.

1
Contributor
1
Reply
8
Views
7 Years
Discussion Span
Last Post by Animal Mother
0

O.K don't worry got it to work on my own. Here is the final code, I'm not going to get into the details because the variables have been named in such a way that the code should be self explanatory.

Imports System.Data.OleDb
Imports System.Security
Imports System.Security.Principal.WindowsIdentity
Public Class form_Login
    Dim dbReader As OleDbDataReader
    Dim dbConnect As New OleDb.OleDbConnection
    Dim dbCommand As OleDbCommand
    Dim Line As String = Environment.NewLine
    Dim ValidUser As Boolean = False
    Dim CurrentUser As String = GetCurrent.Name
    Dim dbDataSet As DataSet = New DataSet("Table_Login")
    Dim dbAdapter As OleDb.OleDbDataAdapter
    Dim intDbRecords As Integer
    Dim dbRow As DataRow
    Dim dbTable As DataTable
    Dim sql As String
    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        dbReader = dbCommand.ExecuteReader
        dbReader.Read()
        dbTable = dbDataSet.Tables("Table_Login")
        For Each dbRow In dbTable.Rows
            If dbRow("Abnum").ToString = CurrentUser.ToString Then
                ValidUser = True
                Exit For
            End If
        Next
        If ValidUser = True Then
            If dbRow("Tiepe").ToString = "Manager" Then
                Dim mainForm As New form_MainMenu
                btnLogin.Enabled = False
                mainForm.Show()
                Me.Hide()
            ElseIf dbRow("Tiepe").ToString = "Agent" Then
                Dim agentForm As New form_AgentWork
                btnLogin.Enabled = False
                agentForm.Show()
                Me.Hide()
            ElseIf dbRow("Tiepe").ToString = "Teller" Then
                Dim tellerForm As New form_TellerWork
                btnLogin.Enabled = False
                tellerForm.Show()
                Me.Hide()
            ElseIf dbRow("Tiepe").ToString = "Mandate Holder" Then
                Dim mainForm As New form_MainMenu
                btnLogin.Enabled = False
                mainForm.Show()
                Me.Hide()
            End If
        Else
            lblMessage.Text = "You are not a Registered User for this Application" + Line + "Contact Your Manager or Mandate Holder" + Line + "Exit this Application now"
            PicBoxError.Visible = True 
        End If
    End Sub
    Private Sub form_Login_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            dbConnect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Database\WorkWatchMain.mdb"
            dbConnect.Open()
            dbCommand = New OleDbCommand("SELECT * FROM Table_Login", dbConnect)
            sql = "SELECT * FROM Table_Login"
            dbAdapter = New OleDb.OleDbDataAdapter(sql, dbConnect.ConnectionString)
            dbAdapter.Fill(dbDataSet, "Table_Login")
        Catch ex As Exception
            MessageBox.Show(ex.Message + Line + "Main Database Not Found" + Line + "Check form_AccessMaintenance source code" + Line + "Database Path", "Critical Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
End Class

This code was written to check usernames in a database and give access to diffrent forms in the application depending on their job type.

Hope this helps.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.