Insert Update Delete Navigation & Searching in Access Database Using VB.NET

These are some basic operations and must be needed for each application which uses the database support. In this article, we can learn each operation easily and rapidly. So keep learning ....
  • 77535
 

Insert, Update, Delete, Search & Navigation are basic operations used in database handling. Below is the simple application which allows to insert, update, delete, search and navigation in database using vb.net as frontend. Some restrictions are also there in this application to perform these operations for better results and access.

 

Coding for each operation is done separately, it is easy to understand. So please have a look on the source code.

 

CODE

 

  • MODULE1

 

Imports System.Data.OleDb

Module Module1

    Public Con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=.\\employee.accdb;Persist Security Info=False")

    Public Dad As OleDbDataAdapter

    Public Drd As OleDbDataReader

    Public Cmd As OleDbCommand

    Public Dst = New DataSet

    Public CurrentRow As Integer

End Module

 

  • FORM1

Imports System.Data.OleDb

Imports System.String

' Form Load

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgsHandlesMyBase.Load

        CurrentRow = 0

        Con.Open()

        Dad = New OleDbDataAdapter("SELECT * FROM empinfo ORDER BY Id", Con)

        Dad.Fill(Dst, "employee")

        ShowData(CurrentRow)

        Con.Close()

    End Sub

    ' To display data on form

    Private Sub ShowData(ByVal CurrentRow)

        Try

            Id.Text = Dst.Tables("employee").Rows(CurrentRow)("Id")

            FName.Text = Dst.Tables("employee").Rows(CurrentRow)("FName")

            LName.Text = Dst.Tables("employee").Rows(CurrentRow)("LName")

            Designation.Text = Dst.Tables("employee").Rows(CurrentRow)("Designation")

            Salary.Text = Dst.Tables("employee").Rows(CurrentRow)("Salary")

        Catch ex As Exception

        End Try

    End Sub

    ' To exit from application

    Private Sub ExitNow_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles ExitNow.Click

        Con.Close()

        Me.Close()

        End

    End Sub

    ' To Navigate to First Record

    Private Sub First_Click(ByVal sender As System.ObjectByVal e As System.EventArgsHandlesFirst.Click

        CurrentRow = 0

        ShowData(CurrentRow)

    End Sub

    ' To Navigate to Last Record

    Private Sub Previous_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles Previous.Click

        If CurrentRow <> 0 Then

            CurrentRow -= 1

            ShowData(CurrentRow)

        Else

            MsgBox("First Record is Reached!!!")

        End If

    End Sub

    ' To Navigate to Next Record

    Private Sub Forward_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles Forward.Click

        If CurrentRow = Dst.Tables("employee").Rows.Count - 1 Then

            MsgBox("Last Record is Reached!!!")

        Else

            CurrentRow += 1

            ShowData(CurrentRow)

        End If

    End Sub

    ' To Navigate to Last Record

    Private Sub Last_Click(ByVal sender As System.ObjectByVal e As System.EventArgsHandlesLast.Click

        CurrentRow = Dst.Tables("employee").Rows.Count - 1

        ShowData(CurrentRow)

    End Sub

    'To select the data in Id field

    Private Sub Id_GotFocus(ByVal sender As ObjectByVal e As System.EventArgsHandlesId.GotFocus

        Id.SelectAll()

    End Sub

    ' To clear all fields : Id, First Name, Last Name, Designation, Salary

    Private Sub Clear()

        Id.Text = ""

        FName.Text = ""

        LName.Text = ""

        Designation.Text = ""

        Salary.Text = ""

    End Sub

    ' To search a record in database

    Private Sub Search_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles Search.Click

        Dim SearchId As Integer

        Dim i, j As Integer

        If CheckId() = False Then

            MsgBox("Integer Value Required!!!")

            Exit Sub

        End If

        Try

            SearchId = Id.Text

            j = Dst.Tables("employee").Rows.Count - 1

            i = 0

            While i <> j + 1

                If SearchId = Dst.Tables("employee").Rows(i)("Id"Then

                    ShowData(i)

                    Exit While

                ElseIf i = j Then

                    Clear()

                    MsgBox("Record Not Found!!!")

                    ShowData(CurrentRow)

                    Exit While

                End If

                i += 1

            End While

            CurrentRow = i

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

    ' To insert the record in database

    Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles Button1.Click

        Dim Str As String

        If CheckId() = False Then

            MsgBox("Id : Integer Value Required !!!")

            Exit Sub

        ElseIf IsIdExist() = True Then

            MsgBox("Id : Id is already exist. Please choose another one..... For help please consider the data by clicking on ""Show Data"" Button")

            Exit Sub

        ElseIf CheckFName() = False Then

            MsgBox("FName : Only Characters Allowed!!!")

            Exit Sub

        ElseIf CheckLName() = False Then

            MsgBox("LName : Only Characters Allowed!!!")

            Exit Sub

        ElseIf CheckDesignation() = False Then

            MsgBox("Designation : Only Characters Allowed!!!")

            Exit Sub

        ElseIf CheckSalary() = False Then

            MsgBox("Salary : Integer Value Required !!!")

            Exit Sub

        End If

 

        Try

            Str = "insert into empinfo values("

            Str += Id.Text.Trim()

            Str += ","

            Str += """" & FName.Text.Trim() & """"

            Str += ","

            Str += """" & LName.Text.Trim() & """"

            Str += ","

            Str += """" & Designation.Text.Trim() & """"

            Str += ","

            Str += Salary.Text.Trim()

            Str += ")"

            Con.Open()

            Cmd = New OleDbCommand(Str, Con)

            Cmd.ExecuteNonQuery()

            Dst.Clear()

            Dad = New OleDbDataAdapter("SELECT * FROM empinfo ORDER BY Id", Con)

            Dad.Fill(Dst, "employee")

            MsgBox("Record inserted successfully...")

            Con.Close()

        Catch ex As Exception

            MessageBox.Show("Could Not Insert Record!!!")

            MsgBox(ex.Message & " -  " & ex.Source)

            Con.Close()

        End Try

    End Sub

    ' To delete the record from database

    Private Sub Button3_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles Button3.Click

        Dim Str As String

        If CheckId() = False Then

            MsgBox("Id : Integer Value Required!!!")

            Exit Sub

        End If

        Try

            Str = "delete from empinfo where id="

            Str += Id.Text.Trim

            Con.Open()

            Cmd = New OleDbCommand(Str, Con)

            Cmd.ExecuteNonQuery()

            Dst.clear()

            Dad = New OleDbDataAdapter("SELECT * FROM empinfo ORDER BY Id", Con)

            Dad.Fill(Dst, "employee")

            MsgBox("Record deleted successfully...")

            If CurrentRow > 0 Then

                CurrentRow -= 1

                ShowData(CurrentRow)

            End If

            Con.Close()

        Catch ex As Exception

            MessageBox.Show("Could Not delete Record!!!")

            MsgBox(ex.Message & " -  " & ex.Source)

            Con.Close()

        End Try

    End Sub

    ' To select the data in First Name Field

    Private Sub FName_GotFocus(ByVal sender As ObjectByVal e As System.EventArgsHandlesFName.GotFocus

        FName.SelectAll()

    End Sub

    ' To select the data in Last Name Field

    Private Sub LName_GotFocus(ByVal sender As ObjectByVal e As System.EventArgsHandlesLName.GotFocus

        LName.SelectAll()

    End Sub

    ' To select the data in Designation Field

    Private Sub Designation_GotFocus(ByVal sender As ObjectByVal e As System.EventArgs)Handles Designation.GotFocus

        Designation.SelectAll()

    End Sub

    ' To select the data in Salary Field

    Private Sub Salary_GotFocus(ByVal sender As ObjectByVal e As System.EventArgsHandlesSalary.GotFocus

        Salary.SelectAll()

    End Sub

    ' To update the records in database

    Private Sub Button2_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles Button2.Click

        If CheckId() = False Then

            MsgBox("Id : Integer Value Required !!!")

            Exit Sub

        ElseIf CheckFName() = False Then

            MsgBox("FName : Only Characters Allowed!!!")

            Exit Sub

        ElseIf CheckLName() = False Then

            MsgBox("LName : Only Characters Allowed!!!")

            Exit Sub

        ElseIf CheckDesignation() = False Then

            MsgBox("Designation : Only Characters Allowed!!!")

            Exit Sub

        ElseIf CheckSalary() = False Then

            MsgBox("Salary : Integer Value Required !!!")

            Exit Sub

        End If

 

        Try

            Dim Str As String

            Str = "update empinfo set FName="

            Str += """" & FName.Text & """"

            Str += " where Id="

            Str += Id.Text.Trim()

            Con.Open()

            Cmd = New OleDbCommand(Str, Con)

            Cmd.ExecuteNonQuery()

            Con.Close()

            Con.Open()

            Str = "update empinfo set LName="

            Str += """" & LName.Text & """"

            Str += " where Id="

            Str += Id.Text.Trim()

            Cmd = New OleDbCommand(Str, Con)

            Cmd.ExecuteNonQuery()

            Con.Close()

            Con.Open()

            Str = "update empinfo set Designation="

            Str += """" & Designation.Text & """"

            Str += " where Id="

            Str += Id.Text.Trim()

            Cmd = New OleDbCommand(Str, Con)

            Cmd.ExecuteNonQuery()

            Con.Close()

            Con.Open()

            Str = "update empinfo set Salary="

            Str += """" & Salary.Text & """"

            Str += " where Id="

            Str += Id.Text.Trim()

            Cmd = New OleDbCommand(Str, Con)

            Cmd.ExecuteNonQuery()

            Con.Close()

            Dst.Clear()

            Dad = New OleDbDataAdapter("SELECT * FROM empinfo ORDER BY Id", Con)

            Dad.Fill(Dst, "employee")

            MsgBox("Updated Successfully...")

        Catch ex As Exception

            MsgBox(ex.Message & "," & ex.Source)

        End Try

    End Sub

    ' To check the data in Id field : whether numeric or not

    Private Function CheckId()

        Try

            If IsNumeric(Id.Text) = False Then

                ShowData(CurrentRow)

                Id.Focus()

                Return False

            End If

        Catch ex As Exception

        End Try

        Return True

    End Function

    ' To check the data in salary field : whether numeric or not

    Private Function CheckSalary()

        Try

            If IsNumeric(Salary.Text) = False Then

                ShowData(CurrentRow)

                Salary.Focus()

                Return False

            End If

        Catch ex As Exception

        End Try

        Return True

    End Function

    ' To check the data in First Name Field : whether a string or not

    Private Function CheckFName()

        Try

            If FName.Text = "" Or ValidateString(FName.Text) = False Then

                ShowData(CurrentRow)

                FName.Focus()

                Return False

            End If

        Catch ex As Exception

        End Try

        Return True

    End Function

    ' To check the data in Last Name Field : whether a string or not

    Private Function CheckLName()

        Try

            If LName.Text = "" Or ValidateString(LName.Text) = False Then

                ShowData(CurrentRow)

                LName.Focus()

                Return False

            End If

        Catch ex As Exception

        End Try

        Return True

    End Function

    ' To check the data in Designation Field : whether a string or not

    Private Function CheckDesignation()

        Try

            If Designation.Text = "" Or ValidateString(Designation.Text) = False Then

                ShowData(CurrentRow)

                Designation.Focus()

                Return False

            End If

        Catch ex As Exception

        End Try

        Return True

    End Function

    ' To check the string for numeric values

    Private Function ValidateString(ByVal Str)

        Dim i As Integer

        Dim ch As Char

        i = 0

        While i < Str.Length()

            ch = Str.Chars(i)

            If IsNumeric(ch) = True Then

                Return False

            End If

            i += 1

        End While

        Return True

    End Function

    ' To show the data in the datagridview

    Private Sub Button4_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles Button4.Click

        Me.Hide()

        Form2.Show()

    End Sub

    ' To check whether Id exist in database

    Private Function IsIdExist()

        Dim Str, Str1 As String

        Dim i As Integer

        Str = Id.Text

        i = 0

        While i <> Dst.Tables("employee").rows.count

            Str1 = Dst.Tables("employee").Rows(i)("Id")

            If Str = Str1 Then

                Return True

            End If

            i += 1

        End While

        Return False

    End Function

End Class

 

  • FORM2

Public Class Form2

    ' To display the data in datagridview control

    Private Sub Form2_Load(ByVal sender As System.ObjectByVal e As System.EventArgsHandlesMyBase.Load

        DataGridView1.DataSource = Dst.Tables(0)

    End Sub

    ' To close the current form and display main frame

    Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs)Handles Button1.Click

        Me.Close()

        Form1.Show()

    End Sub

End Class

 

OUTPUT

Application will look like this at start

InsertUpdateDelete1.gif 

When you try to Insert a record in database with the Id which is already exist, You will see the following message.

 InsertUpdateDelete2.gif

When an invalid value is assign to any field, such a message will display. In this example there was a problem in Last Name Field

InsertUpdateDelete3.gif

After successful insertion in database you will receive a message which is shown in below figure

InsertUpdateDelete4.gif

This message will appear in case of successful update

 InsertUpdateDelete5.gif

By clicking on "Show Data" button on main form, you can see all the records in datagridview control

InsertUpdateDelete6.gif

The picture shown below contain a message which will appear on successful deletion of records from database

InsertUpdateDelete7.gif

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.