Search And Delete Record In DataGridView VB.NET And MS Access
Table of Contents
Daily programmer and developer search in different search engine and browser how to Search And Delete Record In DataGridView VB.NET And MS Access 2016. MS Access is a Database Management System (DBMS) . Its use for background database management with different programming languages.
If you don’t have the concept about database management system you must read my previous article DBMS Management System.
So in this tutorial we will cover the following topic to create little script Search And Delete Record In DataGridView VB.NET And MS Access 2016.
- Database connection with vb.net.
- How to create connection module in vb.net
- Display data in DataGridView .
- Search Specific Record.
- Delete Specific Record.
1. Database Connection With VB.NET
Search And Delete Record In DataGridView VB.NET And MS Access
To make connection with ms ccess 2016 you need to add one module in your project by click on project menu and select the add module. Enter the name of module then click ok. Write the Following code in your connection module.
Module Module1
Public con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & Application.StartupPath & "\SGS_DB.mdb")
End Module Imports System.Data.OleDb
Module ModConVar
Public cmd As New OleDbCommand
Public cn As New OleDbConnection
Public dr As OleDbDataReader
Public dt As New DataTable
Public da As New OleDbDataAdapter
Public db As String = "sgs_db.mdb"
Public cnstring As String = "Provider=Microsoft.jet.oledb.4.0;data source=" & db & ""
Public sql As String
Public conn As OleDbConnection
Public constr As String = System.Environment.CurrentDirectory.ToString & "\SGS_DB.mdb"
2. Search And Display Specific Record In DataGaridView
To Display Data table record in DataGridview add the following functon and code in your Form. Double click on your Form copy the following code and past it into Form Code.
Imports System.Data.OleDb
Public Class Form1
Public Sub addDGV()
With dgw
' .Columns(0).Visible = False
.Columns(1).Width = 100
.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(2).Width = 100
.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(3).Width = 100
.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
.Columns(4).Width = 100
End With
End Sub
Private Sub dgvrefresh()
Try
Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\sgs_db.mdb;")
conn.Open()
Dim command As New OleDbCommand("SELECT * from qryledger where transid LIKE '" & txtid.Text & "'", conn)
'Dim dano As New OleDb.OleDbDataAdapter("select * from qryledger where transid LIKE '" & txtsearch.Text & "' ", con)
Dim adapter As New OleDbDataAdapter
Dim dt As New DataTable
adapter.SelectCommand = command
adapter.Fill(dt)
dgw.DataSource = dt
addDGV()
adapter.Dispose()
command.Dispose()
conn.Close()
End Using
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR3", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'dgvrefresh()
DataGridProperty(dgw)
End Sub
Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtid.KeyPress
If e.KeyChar = Chr(Keys.Enter) Then
dgvrefresh()
End If
End Sub
3. Delete The Selected Record Form the Data Table
To delete the record from the data table you need to create delete function.
Private Sub deletetrasn()
Try
Dim cmddcust As New OleDb.OleDbCommand("delete * from trans where transid =" & txtid.Text, con)
con.Open()
cmddcust.ExecuteNonQuery()
MsgBox("Voucher Deleted successfully", MsgBoxStyle.Information, Me.Text)
con.Close()
Dim reda As New OleDbDataAdapter("select * from trans", con)
con.Open()
Dim reds As New DataSet
reda.Fill(reds, "trans")
con.Close()
dgvrefresh()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
End Try
End Sub
4. Call Delete Function On Button Click Events
'Search And Delete Record In DataGridView VB.NETIf txtid.Text = "" Then
MsgBox("Please select Voucher to Delete", MsgBoxStyle.Exclamation, "Delete Error")
Exit Sub
Else
If MsgBox("Are you sure you want delete Voucher?", MsgBoxStyle.YesNo, "Delete Voucher") = MsgBoxResult.Yes Then
deletetrasn()
Else
Exit Sub
End If
End If
