Visual basic 2010, retrieving values from access database and displaying them.

[NealMorris]

Hi, everyone. First of all Im a novice at this so please dont be too harsh.

I currently have been assigned a project for college.. Basically theyve requested I design an automated registration application that makes use of an loop, an array, functions and procedures and connecting to a database.


Ive already designed my login form with no problems whatsoever and everything works exactly how I need it to.

Now, on the main form my issue is reading a value from my access database and reading it. (This is a search function to retrieve already added students in the database) I thought that the best way to find them would be through the unique key which is their ID number, similar to social security in the US.


I will post a screenshot of my form as soon as I verify my account, beneath and the source code is as follows:


Importing system data from database
Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class frmMain
Dim bln As Boolean
Creating global variables
Private lngDateOfBirth() As Long
Private strName() As String = {}
Private strSurname() As String = {}
Private lngIDNum() As Long
Private strDOB() As String = {}
Private strAddress(0, 4) As String
Private dblPrice As Double
Private connectionString As String
Private ID As Decimal

Public Sub Connection()
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String = "Provider=Microsoft.ace.oledb.12.0;"
Dim dbSource = "Data Source=db1.accdb"
connectionString = dbProvider & dbSource

End Sub
Public Function GetStudent() As DataSet
Dim query As String = "SELECT ID FROM Details"
Dim cmd As New OleDbCommand(query)
Return FillDataSet(cmd, "ID" & "firstName" & "Surname")
End Function

Private Function FillDataSet(ByVal cmd As OleDbCommand, ByVal tableName As String) As DataSet
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String = "Provider=Microsoft.ace.oledb.12.0;"
Dim dbSource = "Data Source=db1.accdb"
connectionString = dbProvider & dbSource
con.ConnectionString = connectionString
cmd.Connection = con
Dim adapter As New OleDbDataAdapter(cmd)
Dim ds As New DataSet()
Try
con.Open()
adapter.Fill(ds, tableName)
Catch ex As Exception
MessageBox.Show("Could not establish connection to DB!", "I/O Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
con.Close()
End Try

Return ds
End Function
Sub CollectData()
Dim lngID As Decimal = CDec(txtID.Text)
Dim lngDOB As Date = CDate(txtDOB.Text)
Dim bursary As Boolean = False
If chkBursary.Checked = True Then
bursary = True
End If
Dim course As String
Dim campus As String
Dim level As Integer
Dim add1 As String
Dim add2 As String
Dim city As String
ID = Val(InputBox("Please enter student ID number.", "Search"))
If ID = 0 Then
MessageBox.Show("Please enter a valid value in the inputbox.", "Error", MessageBoxButtons.OK)
Me.Show()
Exit Sub
End If
Select Case cmbCampus.SelectedIndex
Case 0
campus = "Stellenbosch"
Case 1
campus = "Worcester"
Case 2
campus = "Good Wood"
Case 3
campus = "Cape Town"
End Select
Select Case cmbCourse.SelectedIndex
Case 0
course = "Tourism"
Case 1
course = "IT and computer science"
Case 2
course = "Marketing"
End Select
If radL2.Checked Then
level = 2
ElseIf RadL3.Checked Then
level = 3
ElseIf radL4.Checked Then
level = 4
End If
End Sub
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Changing selected items on the combo boxes
DataGridView1.Hide()
cmbCampus.SelectedIndex = 0
cmbCourse.SelectedIndex = 0
End Sub

Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radMale.CheckedChanged

End Sub

Private Sub Enter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click
If IO.File.Exists("db1.accdb") Then
bln = True
Else : bln = False
End If
If bln = False Then
MessageBox.Show("Please be advised that the DataBase is not available.", "I/O error", MessageBoxButtons.OK)
Exit Sub
End If
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
close
Me.Close()
End Sub

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
Clearing all fields
txtCity.Clear()
txtDOB.Clear()
txtID.Clear()
txtLine1.Clear()
txtLine2.Clear()
txtName.Clear()
txtSurname.Clear()
txtPostCode.Clear()
Unchecking controls
radFemale.Checked = False
radMale.Checked = False
chkBursary.Checked = False
Resets index count
cmbCampus.SelectedIndex = 0
cmbCourse.SelectedIndex = 0
End Sub

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
If IO.File.Exists("db1.accdb") Then
bln = True
Else : bln = False
End If
If bln = False Then
MessageBox.Show("Please be advised that the DataBase is not available.", "I/O error", MessageBoxButtons.OK)
Exit Sub
End If

Call Connection()
Call GetStudent()
End Sub
End Class



Any help with displaying the data, or any other help would be greatly appreciated! Id also like to hear your opinions on where I could make use of an array, whether my code is up to scratch.

Thank you for reading this long segment, and thanks in advance for the help. Duly appreciated.

Continue reading...