Tutorial RSS
 
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.

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

Looking for more ASP.NET Tutorials? Click Here!

I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.



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

We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.


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



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

Looking for more ASP.NET Tutorials? Click Here!

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.


Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!



100% SPAM FREE! We will never sell or rent your email address!

411asp.net123aspxDotNetFreaksServer Intellect