Search button

This is my code can you help me with the code for the search button

Public Class frmTrainer

    Dim Mode As String
    Dim path As String = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mandlak\Desktop\Swazi Nkosi\CPISAT9910280197089.accdb"

    Private Function GetNextTrainerNo()
        Dim TrainerNo As Integer


        Try
            Dim connection As New OleDb.OleDbConnection
            Dim command As New OleDb.OleDbCommand
            Dim table As New Data.DataTable


            connection.ConnectionString = path
            connection.Open()


            command.Connection = connection
            command.CommandText = "SELECT TOP 1 Trainer_No FROM Trainer ORDER BY Trainer_No DESC"


            table.Load(command.ExecuteReader())
            connection.Close()

            If table.Rows.Count > 0 Then
                If Integer.TryParse(table(0)("Trainer_No").ToString(), TrainerNo) Then

                    TrainerNo = TrainerNo + 1

                End If
            Else
                TrainerNo = 221
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)

        End Try

        Return TrainerNo

    End Function
    Private Function GetScaleID()
        Dim ScaleID As Integer


        Try
            Dim connection As New OleDb.OleDbConnection
            Dim command As New OleDb.OleDbCommand
            Dim table As New Data.DataTable


            connection.ConnectionString = path
            connection.Open()


            command.Connection = connection
            command.CommandText = "SELECT TOP 1 Scale_ID FROM Trainer ORDER BY Trainer_No DESC"


            table.Load(command.ExecuteReader())
            connection.Close()

            If table.Rows.Count > 0 Then
                If Integer.TryParse(table(0)("Scale_ID").ToString(), ScaleID) Then



                End If
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)

        End Try

        Return ScaleID

    End Function

    Private Function GetMonthlySalary()

        Dim MonthlySalary As Integer


        Try
            Dim connection As New OleDb.OleDbConnection
            Dim command As New OleDb.OleDbCommand
            Dim table As New Data.DataTable


            connection.ConnectionString = path
            connection.Open()


            command.Connection = connection
            command.CommandText = "SELECT Monthly_Salary FROM Trainer ORDER BY Trainer_ID DESC"


            table.Load(command.ExecuteReader())
            connection.Close()

            If table.Rows.Count > 0 Then
                If Integer.TryParse(table(0)("Monthly_Salary").ToString(), MonthlySalary) Then


                End If
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)

        End Try

        Return MonthlySalary

    End Function

    Private Sub GetTrainers()
        txtID.Text = ""
        txtName.Text = ""
        txtSurnamr.Text = ""
        txtContactNo.Text = ""
        txtClassType.Text = ""
        txtmonthlysalary.Text = ""
        txtScaleID.Text = ""
        txtTrainerNo.Text = ""

        Try
            Dim connection As New OleDb.OleDbConnection
            Dim command As New OleDb.OleDbCommand
            Dim table As New Data.DataTable


            connection.ConnectionString = path
            connection.Open()


            command.Connection = connection
            command.CommandText = "SELECT* FROM Trainer ORDER BY Trainer_No"

            table.Load(command.ExecuteReader())
            connection.Close()

            grvTrainer.DataSource = table

        Catch ex As Exception
            MessageBox.Show(ex.ToString)

        End Try

    End Sub

    Private Sub GetTrainer(ByVal pID As Integer)

        Try
            Dim connection As New OleDb.OleDbConnection
            Dim command As New OleDb.OleDbCommand
            Dim table As New Data.DataTable


            connection.ConnectionString = path
            connection.Open()


            command.Connection = connection
            command.CommandText = "SELECT* FROM Trainer WHERE ID=@ID"

            Dim parID = New OleDb.OleDbParameter("@ID", pID)
            command.Parameters.Add(parID)

            table.Load(command.ExecuteReader())
            connection.Close()


            If table.Rows.Count > 0 Then

                txtID.Text = table(0)("ID").ToString
                txtName.Text = table(0)("Trainer_Name").ToString
                txtSurnamr.Text = table(0)("Trainer_Surname").ToString
                txtContactNo.Text = table(0)("Contact_No").ToString
                txtClassType.Text = table(0)("Class_Type").ToString
                txtmonthlysalary.Text = table(0)("Monthly_Salary").ToString
                txtScaleID.Text = table(0)("Scale_ID").ToString
                txtTrainerNo.Text = table(0)("Trainer_No").ToString
            End If

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        grvTrainer.Enabled = False

        btnAdd.Enabled = False
        btnUpdate.Enabled = False
        btnDelete.Enabled = False

        'Enabling for the Add event
        btnSubmit.Enabled = True
        btnCancel.Enabled = True

        Mode = "INSERT"

        txtID.Text = ""
        txtName.Text = ""
        txtSurnamr.Text = ""
        txtContactNo.Text = ""
        txtClassType.Text = ""


        txtmonthlysalary.Text = GetMonthlySalary().ToString
        txtScaleID.Text = GetScaleID().ToString
        txtTrainerNo.Text = GetNextTrainerNo().ToString()


    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        If grvTrainer.Rows.Count > 0 Then

            If MessageBox.Show("Want to delete?", "Confirm delete?", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then

                Try
                    Dim connection As New OleDb.OleDbConnection
                    Dim command As New OleDb.OleDbCommand


                    connection.ConnectionString = path
                    connection.Open()

                    command.Connection = connection
                    command.CommandText = "DELETE FROM Trainer WHERE ID = @ID"


                    Dim parID = New OleDb.OleDbParameter("@ID", grvTrainer.CurrentRow.Cells("ID").Value)
                    command.Parameters.Add(parID)
                    command.ExecuteNonQuery()
                    connection.Close()
                    GetTrainers()

                Catch ex As Exception
                    MessageBox.Show(ex.ToString)
                End Try
            End If
        Else
            MessageBox.Show("There are no records to delete......")
        End If
    End Sub

    Private Sub frmTrainer_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        GetTrainers()
        btnSubmit.Enabled = False
        btnCancel.Enabled = False
        Mode = ""

    End Sub

    Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click

        If String.IsNullOrEmpty(txtName.Text) Then
            MessageBox.Show("Please enter a name")
            Return
        End If

        If String.IsNullOrEmpty(txtSurnamr.Text) Then
            MessageBox.Show("Please enter a surname")
            Return
        End If

        If String.IsNullOrEmpty(txtContactNo.Text) Then
            MessageBox.Show("Please enter the contact no")
            Return
        End If

        If String.IsNullOrEmpty(txtClassType.Text) Then
            MessageBox.Show("Please enter the class type")
            Return
        End If

        Select Case Mode

            Case "INSERT"

                Try
                    Dim connection As New OleDb.OleDbConnection
                    Dim command As New OleDb.OleDbCommand

                    connection.ConnectionString = path
                    connection.Open()


                    command.Connection = connection
                    command.CommandText = "INSERT INTO Trainer (Trainer_Name, Trainer_Surname,Contact_No,Class_Type,Monthly_Salary,Scale_ID,Trainer_No)VALUES(@Name, @Surname, @ContactNo, @ClassType, @MonthlySalary,ScaleID, @TrainerNo)"

                    Dim parName = New OleDb.OleDbParameter("@Name", txtName.Text)
                    Dim parSurname = New OleDb.OleDbParameter("@Surname", txtSurnamr.Text)
                    Dim parContactNo = New OleDb.OleDbParameter("@ContactNo", txtContactNo.Text)
                    Dim parClassType = New OleDb.OleDbParameter("@ClassType", txtClassType.Text)
                    Dim parMonthlySalary = New OleDb.OleDbParameter("@MonthlySalary", txtmonthlysalary.Text)
                    Dim parScaleID = New OleDb.OleDbParameter("@ScaleID", txtScaleID.Text)
                    Dim parTrainerNo = New OleDb.OleDbParameter("@TrainerNo", txtTrainerNo.Text)


                    command.Parameters.Add(parName)
                    command.Parameters.Add(parSurname)
                    command.Parameters.Add(parContactNo)
                    command.Parameters.Add(parClassType)
                    command.Parameters.Add(parMonthlySalary)
                    command.Parameters.Add(parScaleID)
                    command.Parameters.Add(parTrainerNo)

                    command.ExecuteNonQuery()
                    connection.Close()
                    GetTrainers()

                Catch ex As Exception
                    MessageBox.Show(ex.ToString)
                End Try



            Case "UPDATE"

                Try
                    Dim connection As New OleDb.OleDbConnection
                    Dim command As New OleDb.OleDbCommand


                    connection.ConnectionString = path
                    connection.Open()

                    command.Connection = connection
                    command.CommandText = "UPDATE Trainer SET Trainer_Name=@Name, Trainer_Surname=@Surname, Contact_No=@ContactNo, Class_Type=@ClassType WHERE ID = @ID"


                    Dim parName = New OleDb.OleDbParameter("@Name", txtName.Text)
                    Dim parSurname = New OleDb.OleDbParameter("@Surname", txtSurnamr.Text)
                    Dim parID = New OleDb.OleDbParameter("@ID", txtID.Text)
                    Dim parContactNo = New OleDb.OleDbParameter("@ContactNo", txtContactNo.Text)
                    Dim parClassType = New OleDb.OleDbParameter("@ClassType", txtClassType.Text)



                    'Adding Parameters to the command object
                    command.Parameters.Add(ParName)
                    command.Parameters.Add(parSurname)
                    command.Parameters.Add(parID)
                    command.Parameters.Add(parContactNo)
                    command.Parameters.Add(parClassType)


                    'Executing Parameters
                    command.ExecuteNonQuery()
                    connection.Close()
                    GetTrainers()

                Catch ex As Exception
                    MessageBox.Show(ex.ToString())

                End Try

            Case Else
                MessageBox.Show("Nothing to Submit")

        End Select

        Mode = ""
        grvTrainer.Enabled = True
        btnSubmit.Enabled = False
        btnCancel.Enabled = False

        btnAdd.Enabled = True
        btnDelete.Enabled = True
        btnUpdate.Enabled = True

    End Sub

    Private Sub grvTrainer_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles grvTrainer.CellContentClick

        Dim ID As Integer

        If Integer.TryParse(grvTrainer.CurrentRow.Cells("ID").Value.ToString(), ID) Then
            GetTrainer(ID)
        End If

    End Sub

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click

        grvTrainer.Enabled = True
        btnSubmit.Enabled = False
        btnCancel.Enabled = False

        btnAdd.Enabled = True
        btnDelete.Enabled = True
        btnUpdate.Enabled = True
        Mode = ""

        If grvTrainer.Rows.Count > 0 Then
            Dim ID As Integer

            If Integer.TryParse(grvTrainer.CurrentRow.Cells("ID").Value.ToString(), ID) Then
                GetTrainer(ID)
            End If

            txtID.Text = ""
            txtName.Text = ""
            txtSurnamr.Text = ""
            txtContactNo.Text = ""
            txtClassType.Text = ""
            txtmonthlysalary.Text = ""
            txtScaleID.Text = ""
            txtTrainerNo.Text = ""
        End If

    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

        btnAdd.Enabled = False
        btnDelete.Enabled = False
        btnUpdate.Enabled = False

        btnSubmit.Enabled = True
        btnCancel.Enabled = True

        Mode = "UPDATE"

    End Sub

    Private Sub BtnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Application.Exit()
    End Sub

   
End Class