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.
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.