CURD Operation into MS-ACCESS Database Using VB.NET

Create, Update, Read, Delete and Search Operation into MS-ACCESS Database Using VB.NET

I am Creating Windows application using VB.Net. In this Article you will learn about Select, Insert, Update, delete and Search Operation in VB.Net and Microsoft Access. Here I am displaying Records in Datagridview from Microsoft Access Database.
I am Creating windows application in vb.net named CURD Operation into Microsoft Access Database Using VB.NET. In this application I am using Seven Textbox, one Datagridview, Four Button controls and Eaight Labels.

I am using Following Controls and it's name
txt_ID.Text : It is used to get the Book ID.
txt_Bookname.Text : It is used to get the Book Name.
txt_Author.Text : It is used to get the Author Name.
txt_Year.Text : It is used to get the Book Publish Year.
txt_price.Text : It is used to get the Book Price.
txt_Qty.Text : It is used to get the Book Qty.
txt_Search.Text : It is used to get the DataGridview data Search.

Step 1 => Create Microsoft Access Database "CURD.accdb"

Table Name "CURD"

Step 2 => Import "Access Extension"

Following Coding

Imports System.Data.OleDb
Imports System.IO

Step 3 => Connect Database

Following Coding

Public Class Form1
    Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\CURD.accdb")
    Dim dr As OleDbDataReader
    Dim i As Integer
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            conn.Open()
            lbl_Connection.Text = "Connected"
            lbl_Connection.ForeColor = Color.Lime
        Catch ex As Exception
            lbl_Connection.Text = "DisConnected"
            lbl_Connection.ForeColor = Color.Red
        End Try
        conn.Close()

        txt_ID.Text = "[ AUTO INCREMENT ]"
        txt_ID.Enabled = False

        loadingDatagridView()
    End Sub

Step 4 => Load Data From Access Database to DataGridview

Following Coding

    Sub loadingDatagridView()
        Try
            DataGridView1.Rows.Clear()
            conn.Open()
            Dim cmd As New OleDb.OleDbCommand("Select * from CURD", conn)
            dr = cmd.ExecuteReader
            While dr.Read
                DataGridView1.Rows.Add(dr.Item("ID"), dr.Item("BookName"), dr.Item("Author"), dr.Item("Year"), dr.Item("Price"), dr.Item("Qty"))

            End While
            dr.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conn.Close()
    End Sub

Step 5 => DataGridView CellClick Event

Following Coding

    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        txt_ID.Text = DataGridView1.CurrentRow.Cells(0).Value
        txt_Bookname.Text = DataGridView1.CurrentRow.Cells(1).Value
        txt_Author.Text = DataGridView1.CurrentRow.Cells(2).Value
        txt_Year.Text = DataGridView1.CurrentRow.Cells(3).Value
        txt_price.Text = DataGridView1.CurrentRow.Cells(4).Value
        txt_Qty.Text = DataGridView1.CurrentRow.Cells(5).Value
    End Sub

Step 6 => Insert Record to Microsoft Access Database Using VB.NET

Following Coding

Step 7 => Insert Record to Microsoft Access Database Using VB.NET

Following Coding

    Sub save()
        Try

            If MsgBox("Are You Sure Insert This Record", vbQuestion + vbYesNo) = vbYes Then
                conn.Open()
                Dim cmd As New OleDb.OleDbCommand("Insert into CURD(`BookName`,`Author`,`Year`,`Price`,`Qty`) values(@BookName,@Author,@Year,@Price,@Qty)", conn)
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@BookName", txt_Bookname.Text)
                cmd.Parameters.AddWithValue("@Author", txt_Author.Text)
                cmd.Parameters.AddWithValue("@Year", txt_Year.Text)
                cmd.Parameters.AddWithValue("@Price", txt_price.Text)
                cmd.Parameters.AddWithValue("@Qty", txt_Qty.Text)
                i = cmd.ExecuteNonQuery
                If i > 0 Then
                    MsgBox("Record Save Success !", vbInformation)
                Else
                    MsgBox("Failed", vbCritical)
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        conn.Close()
        loadingDatagridView()
        clear()
    End Sub
    
    Private Sub Btn_Save_Click(sender As Object, e As EventArgs) Handles Btn_Save.Click
        save()
    End Sub

Step 8 => Update Record to Microsoft Access Database Using VB.NET

Following Coding

Sub edit()
        Try
            conn.Open()
            Dim cmd As New OleDb.OleDbCommand("UPDATE CURD SET `BookName`=@BookName,`Author`=@Author,`Year`=@Year,`Price`=@Price,`Qty`=@Qty Where ID=@ID ", conn)
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@BookName", txt_Bookname.Text)
            cmd.Parameters.AddWithValue("@Author", txt_Author.Text)
            cmd.Parameters.AddWithValue("@Year", txt_Year.Text)
            cmd.Parameters.AddWithValue("@Price", txt_price.Text)
            cmd.Parameters.AddWithValue("@Qty", txt_Qty.Text)
            cmd.Parameters.AddWithValue("@ID", txt_ID.Text)
            i = cmd.ExecuteNonQuery
            If i > 0 Then
                MsgBox("Record Update Success !", vbInformation)
            Else
                MsgBox("Failed", vbCritical)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conn.Close()
        loadingDatagridView()
        clear()
    End Sub

    Private Sub Btn_Update_Click(sender As Object, e As EventArgs) Handles Btn_Update.Click
        edit()
    End Sub

Step 9 => Delete Record to Microsoft Access Database Using VB.NET

Following Coding

Sub delete()
        Try

            If MsgBox("Are You Sure Delete This Record", vbQuestion + vbYesNo) = vbYes Then
                conn.Open()
                Dim cmd As New OleDb.OleDbCommand("Delete from CURD Where ID=@ID", conn)
                cmd.Parameters.Clear()
               
                cmd.Parameters.AddWithValue("@ID", txt_ID.Text)
                i = cmd.ExecuteNonQuery
                If i > 0 Then
                    MsgBox("Record Delete Success !", vbInformation)
                Else
                    MsgBox("Failed", vbCritical)
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conn.Close()
        loadingDatagridView()
        clear()
    End Sub

    Private Sub Btn_Delete_Click(sender As Object, e As EventArgs) Handles Btn_Delete.Click
        delete()
    End Sub

Step 10 => Clear Record From VB.NET Controls

Following Coding

    Sub clear()
        txt_ID.Text = "[ AUTO INCREMENT ]"
        txt_Bookname.Clear()
        txt_Author.Clear()
        txt_Year.Clear()
        txt_price.Clear()
        txt_Qty.Clear()
    End Sub

    Private Sub Btn_clear_Click(sender As Object, e As EventArgs) Handles Btn_clear.Click
        clear()
    End Sub

Step 11=> Search Record to Microsoft Access Database Using VB.NET

Following Coding

Sub search()
        Try
            DataGridView1.Rows.Clear()
            conn.Open()
            Dim cmd As New OleDb.OleDbCommand("Select * from CURD WHERE `BookName` like '%" & txt_Search.Text & "%' or `Author` like '%" & txt_Search.Text & "%' ", conn)
            dr = cmd.ExecuteReader
            While dr.Read
                DataGridView1.Rows.Add(dr.Item("ID"), dr.Item("BookName"), dr.Item("Author"), dr.Item("Year"), dr.Item("Price"), dr.Item("Qty"))

            End While
            dr.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        conn.Close()
    End Sub

    Private Sub txt_Search_TextChanged(sender As Object, e As EventArgs) Handles txt_Search.TextChanged
        search()
    End Sub

Thanks

Happy Coding.....

Subscribe For More Projects...