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
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
Imports System.Data.OleDb
Imports System.String
' Form Load
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.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.Object, ByVal 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.Object, ByVal e As System.EventArgs) HandlesFirst.Click
CurrentRow = 0
ShowData(CurrentRow)
End Sub
' To Navigate to Last Record
Private Sub Previous_Click(ByVal sender As System.Object, ByVal 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.Object, ByVal 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.Object, ByVal e As System.EventArgs) HandlesLast.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 Object, ByVal e As System.EventArgs) HandlesId.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.Object, ByVal 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.Object, ByVal 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.Object, ByVal 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 Object, ByVal e As System.EventArgs) HandlesFName.GotFocus
FName.SelectAll()
End Sub
' To select the data in Last Name Field
Private Sub LName_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) HandlesLName.GotFocus
LName.SelectAll()
End Sub
' To select the data in Designation Field
Private Sub Designation_GotFocus(ByVal sender As Object, ByVal 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 Object, ByVal e As System.EventArgs) HandlesSalary.GotFocus
Salary.SelectAll()
End Sub
' To update the records in database
Private Sub Button2_Click(ByVal sender As System.Object, ByVal 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.Object, ByVal 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
Public Class Form2
' To display the data in datagridview control
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.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.Object, ByVal e As System.EventArgs)Handles Button1.Click
Me.Close()
Form1.Show()
End Sub
End Class
OUTPUT
Application will look like this at start
When you try to Insert a record in database with the Id which is already exist, You will see the following message.
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
After successful insertion in database you will receive a message which is shown in below figure
This message will appear in case of successful update
By clicking on "Show Data" button on main form, you can see all the records in datagridview control
The picture shown below contain a message which will appear on successful deletion of records from database