Public Class SpectaclesSelection
Private Sub SpectaclesSelection_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 CustomerTable" '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
ListBoxOutput.Items.Clear()
While dr.Read
If Not IsDBNull(dr.Item("Surname")) Then 'INSERTS INFORMATION INTO
TitleString = dr.Item("Surname") ' LISTBOX
ListBoxOutput.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 ListBoxOutput_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBoxOutput.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)
AddressTextBox.Text = ListBoxOutput.Text
SQLString = "SELECT * FROM CustomerTable "
SQLString += "INNER JOIN EyeTestTable ON CustomerTable.CustomerID = EyeTestTable.CustomerID "
SQLString += "Where '" & AddressTextBox.Text & "'"
SQLString += "= Surname"
Try
conn.Open()
If ConnectionState.Open.ToString = "Open" Then
cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)
dr = cmd.ExecuteReader()
If dr.HasRows Then
dr.Read()
If Not IsDBNull(dr.Item("Street")) Then
AddressTextBox.Text = dr.Item("Street").ToString
End If
If Not IsDBNull(dr.Item("Town")) Then
Address2TextBox.Text = dr.Item("Town").ToString
End If
If Not IsDBNull(dr.Item("County")) Then
Address3TextBox.Text = dr.Item("County").ToString
End If
If Not IsDBNull(dr.Item("RightEyeMeasurement")) Then
RightEyeTextBox.Text = dr.Item("RightEyeMeasurement").ToString
End If
If Not IsDBNull(dr.Item("LeftEyeMeasurement")) Then
LeftEyeTextBox.Text = dr.Item("LeftEyeMeasurement").ToString
End If
If Not IsDBNull(dr.Item("DateOfTest")) Then
DateTextBox.Text = dr.Item("DateOfTest").ToString
End If
End If
End If
Catch ex As Exception
End Try
End Sub
Private Sub ListBoxOutput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBoxOutput.Click
Button2.Enabled = True
GroupBox3.Enabled = False
End Sub
Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Me.Close()
End Sub
Private Sub ExitToolStripMenuItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
Me.Close()
End Sub
Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter
End Sub
Private Sub GroupBox2_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox2.Enter
End Sub
Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Address2TextBox.TextChanged
End Sub
Private Sub OrderButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OrderButton.Click
Dim myValue As Integer
Dim myValue1 As Integer
Dim myValue2 As Integer
Dim myValue3 As Integer
Dim totalCost As Integer
Dim deposit As Integer
myValue = 50
Select Case GPTextBox.Text
Case "Glass"
myValue1 = 30
Case "Plastic"
myValue1 = 15
End Select
Select Case ScratchTextBox.Text
Case "Yes"
myValue2 = 10
Case "No"
myValue2 = 0
End Select
Select Case UVTextBox.Text
Case "Yes"
myValue3 = 15
Case "No"
myValue3 = 0
End Select
totalCost = myValue + myValue1 + myValue2 + myValue3
deposit = totalCost * 0.2
TextBox1.Text = totalCost
TextBox2.Text = deposit
TextBox3.Text = totalCost - deposit
Button3.Enabled = True
Button4.Enabled = True
myAddNew(8) 'CALLS ADDNEW METHOD WHEN ADD BUTTON IS CLICKED
End Sub
Private Sub myAddNew(ByVal CusRef As Integer)
Dim ConnectionString As String
Dim SQLString As String
Dim whichButtonDialogResult As DialogResult
Dim dbCommand As System.Data.OleDb.OleDbCommand
Dim Connection As System.Data.OleDb.OleDbConnection
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
ConnectionString += "Source=" & "Opticians.accdb "
Connection = New System.Data.OleDb.OleDbConnection(ConnectionString)
SQLString = "INSERT INTO SpecSalesTable (FrameID, DateOfSale, GlassOrPlastic, ScratchCoating, UVFilter, TotalCost, DepositPaid) "
SQLString += "Values ('" & StockTextBox.Text & "',#" & Date.Today & "#,'" & GPTextBox.Text & "','" & ScratchTextBox.Text & "','" & UVTextBox.Text & "','" & TextBox1.Text & "','" & TextBox2.Text & "')"
whichButtonDialogResult = MessageBox.Show("Are You Sure You Want To Place This Order?", "Add Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
If whichButtonDialogResult = DialogResult.Yes Then
Connection.Open()
If CBool(ConnectionState.Open) Then
dbCommand = New System.Data.OleDb.OleDbCommand(SQLString, Connection)
Try
dbCommand.ExecuteNonQuery()
MessageBox.Show("Order placed, SpecSalesTable Updated! ")
Catch ex As Exception
MessageBox.Show(" Error placing order... ") 'IF ERROR DISPLAYS MESSAGE
End Try
End If
End If
Connection.Close()
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub IDTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
GroupBox3.Enabled = True
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim txtFile As String = IO.File.ReadAllText("template.txt")
txtFile = txtFile.Replace("[date]", "07/03/2010")
txtFile = txtFile.Replace("[left eye]", LeftEyeTextBox.Text)
txtFile = txtFile.Replace("[right eye]", RightEyeTextBox.Text)
txtFile = txtFile.Replace("[material]", GPTextBox.Text)
txtFile = txtFile.Replace("[UVF]", UVTextBox.Text)
txtFile = txtFile.Replace("[Scratch]", ScratchTextBox.Text)
IO.File.WriteAllText("NewOrder.txt", txtFile)
Process.Start("NewOrder.txt")
End Class
