Imports System.Collections.ObjectModel
Imports System.Data.OleDb
Private CurrentCustomerID As Integer = -1
Private Sub frmCustomer_Load(sender As Object, e As EventArgs) Handles MyBase.Load
DatDOB.MinDate = Now.AddYears(-25)
DatDOB.MaxDate = Now.AddYears(-15)
panSearchList.Visible = False
dgvCustomer.DefaultCellStyle.ForeColor = Color.Black
dgvCustomer.DefaultCellStyle.BackColor = Color.White
Private Sub ButNew_Click(sender As Object, e As EventArgs) Handles btnNew.Click
lblCustomerID.Text = "<automatically generated>"
DatDOB.Value = Now.AddYears(-18)
txtDrivingLicense.Text = ""
txtPreviousVehicle.Text = ""
lstCurrentVehicle.Items.Clear()
LstPreviousVehicle.Items.Clear()
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles ButSave.Click
If txtFirstname.Text.Length < 1 Then
MessageBox.Show("You must enter a first name for this Customer", "Save Customer", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
ElseIf txtSurname.Text.Length < 1 Then
MessageBox.Show("You must enter a Surname for this Customer", "Save Customer", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
ElseIf txtDrivingLicense.Text.Length < 1 Then
MessageBox.Show("You must enter a Driving License Number for this Customer", "Save Customer", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtDrivingLicense.Focus()
ElseIf txtPhoneNumber.Text.Length < 1 Or txtPhoneNumber.Text.Length > 11 Then
MessageBox.Show("You must enter a Phone number with 1-11 numbers for this Customer", "Save Customer", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
ElseIf txtPostcode.Text.Length < 6 Then
MessageBox.Show("You must enter a valid PostCode for this Customer", "Save Customer", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
ElseIf DatDOB.Value > Now.AddYears(-15) Or DatDOB.Value < Now.AddYears(-25) Then
MessageBox.Show("Invalid date of birth, please re-enter.", "Save Customer", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Dim SQLCmd As New OleDbCommand
If CurrentCustomerID = -1 Then
SQLCmd.CommandText = "Insert into Customer (SurName, FirstNames, DateOfBirth, PostCode, LicenseNumber, Title, PreviousVehicle, PhoneNumber) " &
"Values (@Surname, @FirstNames, @DateOfBirth, @PostCode, @LicenseNumber, @Title, @PreviousVehicle, @PhoneNumber )"
SQLCmd.Parameters.AddWithValue("@Surname", txtSurname.Text)
SQLCmd.Parameters.AddWithValue("@FirstNames", txtFirstname.Text)
SQLCmd.Parameters.AddWithValue("@DateOfBirth", DatDOB.Value.Date)
SQLCmd.Parameters.AddWithValue("@PostCode", txtPostcode.Text)
SQLCmd.Parameters.AddWithValue("@LicenseNumber", txtDrivingLicense.Text)
SQLCmd.Parameters.AddWithValue("@Title", cmbTitle.Text)
SQLCmd.Parameters.AddWithValue("@PreviousVehicle", txtPreviousVehicle.Text)
SQLCmd.Parameters.AddWithValue("@PhoneNumber", txtPhoneNumber.Text)
SQLCmd.CommandText = "Select @@Identity"
CurrentCustomerID = SQLCmd.ExecuteScalar
lblCustomerID.Text = CurrentCustomerID
SQLCmd.CommandText = "Update Customer " &
"Set Surname = @Surname, " &
"FirstNames = @FirstNames, " &
"DateOfBirth = @DateOfBirth, " &
"PostCode = @PostCode, " &
"LicenseNumber = @LicenseNumber, " &
"PreviousVehicle = @PreviousVehicle, " &
"PhoneNumber = @PhoneNumber " &
"where CustomerID = @currentCustomerID"
SQLCmd.Parameters.AddWithValue("@Surname", txtSurname.Text)
SQLCmd.Parameters.AddWithValue("@FirstNames", txtFirstname.Text)
SQLCmd.Parameters.AddWithValue("@DateOfBirth", DatDOB.Value.Date)
SQLCmd.Parameters.AddWithValue("@PostCode", txtPostcode.Text)
SQLCmd.Parameters.AddWithValue("@LicenseNumber", txtDrivingLicense.Text)
SQLCmd.Parameters.AddWithValue("@Title", cmbTitle.Text)
SQLCmd.Parameters.AddWithValue("@PreviousVehicle", txtPreviousVehicle.Text)
SQLCmd.Parameters.AddWithValue("@PhoneNumber", txtPhoneNumber.Text)
SQLCmd.Parameters.AddWithValue("@CustomerID", CurrentCustomerID)
Private Sub ButSave_Click(sender As Object, e As EventArgs) Handles ButSave.Click
MessageBox.Show("Customer details saved successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
Private Sub ButClearSearch_Click(sender As Object, e As EventArgs) Handles btnClearSearch.Click
panSearchList.Visible = False
Private Sub ButClose_Click(sender As Object, e As EventArgs)
Private Sub ButFindCustomer_Click(sender As Object, e As EventArgs) Handles btnFindCustomer.Click
If IsNumeric(TxtSearchID.Text) Then
DisplayCustomer(TxtSearchID.Text)
ElseIf txtSearchName.Text.Length > 0 Then
SearchSurname(txtSearchName.Text)
Private Sub SearchSurname(Surname As String)
lstCustomer.Items.Clear()
Dim SQLCmd As New OleDbCommand
.CommandText = "Select * " &
"Where Surname Like @SurnameSearch"
.Parameters.AddWithValue("@SurnameSearch", "%" & txtSearchName.Text & "%")
Dim rs As OleDbDataReader = .ExecuteReader()
Dim DisplayValue As String = rs("Firstnames") & " " & rs("Surname") & " (" & rs("DateOfBirth") & ")"
Dim currentCustomer As New ListBoxData(DisplayValue, rs("CustomerID"))
lstCustomer.Items.Add(currentCustomer)
Dim NewCustomerRow As New DataGridViewRow()
NewCustomerRow.CreateCells(dgvCustomer)
NewCustomerRow.SetValues({rs("Surname"), rs("FirstNames"), rs("PostCode")})
NewCustomerRow.Tag = rs("CustomerID")
dgvCustomer.Rows.Add(NewCustomerRow)
If lstCustomer.Items.Count = 1 Then
panSearchList.Visible = True
MessageBox.Show(lstCustomer.Items.Count)
panSearchList.Visible = False
If lstCustomer.Items.Count = 0 Then
MessageBox.Show("There are no customers of this surname")
lstCustomer.SelectedIndex = 0
If dgvCustomer.Rows.Count > 1 Then
panSearchList.Visible = True
panSearchList.Visible = False
If dgvCustomer.Rows.Count = 0 Then
MessageBox.Show("There are no customers of this surname")
dgvCustomer.Rows(0).Selected = True
Private Sub DisplayCustomer(CustomerID As Integer)
Dim foundCustomer As Boolean = False
Dim SQLCmd As New OleDbCommand
SQLCmd.CommandText = "Select * From Customer Where CustomerID = @SearchID"
SQLCmd.Parameters.AddWithValue("SearchID,", CustomerID)
Dim RS As OleDbDataReader = SQLCmd.ExecuteReader
CurrentCustomerID = RS("CustomerID")
cmbTitle.SelectedItem = RS("Title")
lblCustomerID.Text = RS("CustomerID")
txtSurname.Text = RS("Surname")
txtFirstname.Text = RS("Firstnames")
DatDOB.Value = RS("DateOfBirth")
txtPostcode.Text = RS("PostCode")
txtDrivingLicense.Text = RS("LicenseNumber")
txtPreviousVehicle.Text = RS("PreviousVehicle")
txtPhoneNumber.Text = RS("PhoneNumber")
MessageBox.Show("Could Not find Customer " & CustomerID, "", MessageBoxButtons.OK)
Private Sub panSearchList_Paint(sender As Object, e As PaintEventArgs) Handles panSearchList.Paint
Private Sub lstCustomer_SelectedIndexChanged(sender As Object, e As EventArgs) Handles lstCustomer.SelectedIndexChanged
If lstCustomer.SelectedItem IsNot Nothing Then
Dim SelectedCustomer As ListBoxData = lstCustomer.SelectedItem
DisplayCustomer(SelectedCustomer.Identifier)
Private Sub DisplayVehicle(CustomerID As Integer)
lstCurrentVehicle.Items.Clear()
LstPreviousVehicle.Items.Clear()
Dim SQLCmd As New OleDbCommand
.CommandText = "Select * From Vehicle, Vehicles " &
"Where Vehicle.VehicleID = Vehicles.VehicleID And CustomerID = @CustomerID"
.Parameters.AddWithValue("@StudentID", CustomerID)
Dim rs As OleDbDataReader = .ExecuteReader()
If rs("CurrentVehicle") = True Then
lstCurrentVehicle.Items.Add(rs("Vehicle Name") & " (" & rs("StartDate") & " - )")
LstPreviousVehicle.Items.Add(rs("Vehicle Name") & " (" & rs("StartDate") & " - " & rs("EndDate") & ")")
Private Sub Button2_Click(sender As Object, e As EventArgs)
Dim SQLCmd As New OleDbCommand
SQLCmd.CommandText = "Insert into Vehicle (CustomerID, VehicleID, CurrentVehicle) " &
"Values (@CustomerID, @VehicleID, @CurrentVehicle)"
SQLCmd.Parameters.AddWithValue("@CustomerID", CurrentCustomerID)
SQLCmd.Parameters.AddWithValue("@VehicleID", CID)
SQLCmd.Parameters.AddWithValue("@CurrentVehicle", True)
Private Sub dgvCustomer_SelectionChanged(sender As Object, e As EventArgs) Handles dgvCustomer.SelectionChanged
If dgvCustomer.SelectedRows.Count > 0 Then
Dim SelectedTag As Integer = dgvCustomer.SelectedRows(0).Tag
DisplayCustomer(SelectedTag)
Private Sub txtSurname_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtSurname.KeyPress, txtFirstname.KeyPress
If Not Char.IsLetter(e.KeyChar) And e.KeyChar <> "-" And e.KeyChar <> "'" And e.KeyChar <> " " Then
If Asc(e.KeyChar) <> Keys.Back Then
Private Sub txtPostcode_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtPostcode.KeyPress, txtDrivingLicense.KeyPress, txtPreviousVehicle.KeyPress
If Not Char.IsLetterOrDigit(e.KeyChar) And e.KeyChar <> "-" And e.KeyChar <> "'" And e.KeyChar <> " " Then
If Asc(e.KeyChar) <> Keys.Back Then
Private Sub txtPhoneNumber_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtPhoneNumber.KeyPress
If Not Char.IsDigit(e.KeyChar) And e.KeyChar <> "-" And Asc(e.KeyChar) <> Keys.OemQuotes And e.KeyChar <> " " Then
If Asc(e.KeyChar) <> Keys.Back Then
Private Sub btnClose1_Click(sender As Object, e As EventArgs) Handles btnClose1.Click