Navigator: Home - Advanced - Add/Edit/Delete Access DB, using ADO.NET + DataGrid VB

Add/Edit/Delete Access DB, using ADO.NET + DataGrid VB

Learn how to display data from an Access Database using ADO.NET and a DataGrid, and also learn how we can use the DataGrid to add new data and edit & delete existing data. VB version.

Download the Full Working Version of this Project written with Visual Studio.NET VB 2005 Here!

Looking for the C#.NET 2005 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!

In this tutorial, we will show how we can display data from an Access database in a DataGrid Control, using ADO.NET. We will also see how we can use the DataGrid to edit, delete and add records to the database. First, we start off by adding the DataGrid to our ASPX page:
Note: All the On<Event>Commands will be referenced in our code.

<form id="form1" runat="server">
<div>
<asp:DataGrid ID="DataGrid1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataKeyField="ID" ForeColor="#333333" GridLines="None" OnCancelCommand="DataGrid1_CancelCommand" OnDeleteCommand="DataGrid1_DeleteCommand" OnEditCommand="DataGrid1_EditCommand" OnUpdateCommand="DataGrid1_UpdateCommand">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<EditItemStyle BackColor="#2461BF" />
<SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<AlternatingItemStyle BackColor="White" />
<ItemStyle BackColor="#EFF3FB" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:EditCommandColumn CancelText="Cancel" EditText="Edit" UpdateText="Update"></asp:EditCommandColumn>
<asp:BoundColumn DataField="PetName" HeaderText="Pet Name"></asp:BoundColumn>
<asp:BoundColumn DataField="PetType" HeaderText="Pet Type"></asp:BoundColumn>
<asp:ButtonColumn CommandName="Delete" Text="Delete"></asp:ButtonColumn>
</Columns>
</asp:DataGrid>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Add" /></div>
</form>

Next, we add the code to read the data from the database:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If (Not IsPostBack) Then
ReadRecords()
End If
End Sub

Private Sub ReadRecords()
Dim conn As OleDbConnection = Nothing
Dim reader As OleDbDataReader = Nothing
Try
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/Database1.mdb"))
conn.Open()

Dim cmd As New OleDbCommand("Select * FROM Table1", conn)
reader = cmd.ExecuteReader()

DataGrid1.DataSource = reader
DataGrid1.DataBind()
Finally
If reader IsNot Nothing Then
reader.Close()
End If
If conn IsNot Nothing Then
conn.Close()
End If
End Try
End Sub

The above piece of code simply reads the data from the database and binds it to the DataGrid. We can now add more functionality to the DataGrid by adding the Button Columns Add, Update and Cancel, as well as Delete. We do this by going into the Property Builder (Property Pages) and choosing these buttons from the Button Column list. First, we uncheck 'Creat columns automatically at run time, and then we also add a Bound Column for each database column we want displayed by the DataGrid. For each of these Bound Column, we type the Header Text and the Data Field should correspond with the column ID in the Access database.
Once we have done this, we can add the code to edit and update the fields:

Protected Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
DataGrid1.EditItemIndex = -1
ReadRecords()
End Sub

Protected Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
DataGrid1.EditItemIndex = e.Item.ItemIndex
ReadRecords()
End Sub

Protected Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
Dim ID As Integer = CInt(Fix(DataGrid1.DataKeys(CInt(Fix(e.Item.ItemIndex)))))

Dim name As String = (CType(e.Item.Cells(1).Controls(0), TextBox)).Text
Dim type As String = (CType(e.Item.Cells(2).Controls(0), TextBox)).Text

Dim sql As String = "UPDATE Table1 SET PetName=""" & name & """, PetType=""" & type & """" & " WHERE ID=" & ID
ExecuteNonQuery(sql)

DataGrid1.EditItemIndex = -1
ReadRecords()
End Sub

Private Sub ExecuteNonQuery(ByVal sql As String)
Dim conn As OleDbConnection = Nothing
Try
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/Database1.mdb"))
conn.Open()

Dim cmd As New OleDbCommand(sql, conn)
cmd.ExecuteNonQuery()
Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
End Sub

The entire code-behind will look something like this:

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.OleDb

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If (Not IsPostBack) Then
ReadRecords()
End If
End Sub

Private Sub ReadRecords()
Dim conn As OleDbConnection = Nothing
Dim reader As OleDbDataReader = Nothing
Try
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/Database1.mdb"))
conn.Open()

Dim cmd As New OleDbCommand("Select * FROM Table1", conn)
reader = cmd.ExecuteReader()

DataGrid1.DataSource = reader
DataGrid1.DataBind()
Finally
If reader IsNot Nothing Then
reader.Close()
End If
If conn IsNot Nothing Then
conn.Close()
End If
End Try
End Sub

Protected Sub DataGrid1_CancelCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
DataGrid1.EditItemIndex = -1
ReadRecords()
End Sub

Protected Sub DataGrid1_EditCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
DataGrid1.EditItemIndex = e.Item.ItemIndex
ReadRecords()
End Sub

Protected Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
Dim ID As Integer = CInt(Fix(DataGrid1.DataKeys(CInt(Fix(e.Item.ItemIndex)))))

Dim name As String = (CType(e.Item.Cells(1).Controls(0), TextBox)).Text
Dim type As String = (CType(e.Item.Cells(2).Controls(0), TextBox)).Text

Dim sql As String = "UPDATE Table1 SET PetName=""" & name & """, PetType=""" & type & """" & " WHERE ID=" & ID
ExecuteNonQuery(sql)

DataGrid1.EditItemIndex = -1
ReadRecords()
End Sub

Private Sub ExecuteNonQuery(ByVal sql As String)
Dim conn As OleDbConnection = Nothing
Try
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/Database1.mdb"))
conn.Open()

Dim cmd As New OleDbCommand(sql, conn)
cmd.ExecuteNonQuery()
Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim sql As String = "INSERT INTO Table1 (PetName, PetType)" & " VALUES (""new"", ""new"")"
ExecuteNonQuery(sql)
ReadRecords()
End Sub

Protected Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As DataGridCommandEventArgs)
Dim ID As Integer = CInt(Fix(DataGrid1.DataKeys(CInt(Fix(e.Item.ItemIndex)))))

Dim sql As String = "DELETE FROM Table1 WHERE ID=" & ID
ExecuteNonQuery(sql)
ReadRecords()
End Sub
End Class

Download the Full Working Version of this Project written with Visual Studio.NET VB 2005 Here!

Looking for the C#.NET 2005 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!
411asp.net123aspxDotNetFreaksServer Intellect