Public Class ReceiptSpectacles
Private Sub ReceiptSpectacles_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DisplayList(5) 'LOADS DISPLAYLIST METHOD
End Sub
Private Sub DisplayList(ByVal CusRef As Integer)
Dim ConnectionString As String
Dim SQLString As String
Dim TitleString As String
Dim conn As System.Data.OleDb.OleDbConnection
Dim dr As System.Data.OleDb.OleDbDataReader
Dim cmd As System.Data.OleDb.OleDbCommand
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data "
ConnectionString += "Source=" & "Opticians.accdb "
conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
SQLString = "SELECT * FROM SpecSalesTable" 'SELECTS ALL INFORMATION
Try ' FROM DATABASE
conn.Open()
If ConnectionState.Open Then
cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
dr = cmd.ExecuteReader()
If dr.HasRows Then
FramesListBox.Items.Clear()
While dr.Read
If Not IsDBNull(dr.Item("FrameID")) Then 'INSERTS INFORMATION INTO
TitleString = dr.Item("FrameID") ' LISTBOX
FramesListBox.Items.Add(TitleString)
End If
End While
End If
dr.Close()
End If
Catch
MessageBox.Show("Error accessing database") 'IF ERROR ENCOUNTERED SHOWS MESSAGE
End Try
conn.Close()
End Sub
Private Sub FramesListBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FramesListBox.SelectedIndexChanged
Dim ConnectionString As String
Dim SQLString As String
Dim cmd As System.Data.OleDb.OleDbCommand
Dim conn As System.Data.OleDb.OleDbConnection
Dim dr As System.Data.OleDb.OleDbDataReader
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & "Opticians.accdb "
conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
TextBox1.Text = FramesListBox.Text
SQLString = "SELECT * FROM SpecSalesTable "
SQLString += "INNER JOIN CustomerTable ON SpecSalesTable.CustomerID = CustomerTable.CustomerID "
SQLString += "Where '" & TextBox1.Text & "'"
SQLString += "= FrameID"
Try
conn.Open()
If ConnectionState.Open.ToString = "Open" Then
cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
dr = cmd.ExecuteReader()
[I]If dr.HasRows Then[/I]
dr.Read()
If Not IsDBNull(dr.Item("FrameID")) Then
TextBox1.Text = dr.Item("FrameID").ToString
End If
If Not IsDBNull(dr.Item("CustomerID")) Then
TextBox2.Text = dr.Item("CustomerID").ToString
End If
If Not IsDBNull(dr.Item("Surname")) Then
TextBox3.Text = dr.Item("Surname").ToString
End If
If Not IsDBNull(dr.Item("Firstname")) Then
TextBox3.Text = dr.Item("Firstname").ToString
End If
End If
End If
Catch ex As Exception
End Try
End Sub