web 2.0

ASP.NET(vb.net) & Microsoft Access Edit/Update Rows Record

ASP.NET(vb.net) & Microsoft Access Edit/Update Rows Record - This isĀ  example scripts how to use ASP.NET edit update rows in Microsoft Access (.mdb) database.

ShotDev Focus:
- ASP.NET(vb.net) & Microsoft Access Edit/Update Record

Example

AspNetAccessEditListRecord.aspx

<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<%@ Page Language="VB" %>
<script runat="server">

Dim objConn As OleDbConnection
Dim objCmd As OleDbCommand

Sub Page_Load(sender As Object, e As EventArgs)
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("database/mydatabase.mdb")&";Jet OLEDB:Database Password=;"
objConn = New OleDbConnection(strConnString)
objConn.Open()

BindData()
End Sub

Sub BindData()
Dim strSQL As String
strSQL = "SELECT * FROM customer"

Dim dtReader As OleDbDataReader
objCmd = New OleDbCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()

'*** BindData to Repeater ***'
myRepeater.DataSource = dtReader
myRepeater.DataBind()

dtReader.Close()
dtReader = Nothing

End Sub

Sub Page_UnLoad()
objConn.Close()
objConn = Nothing
End Sub

Sub myRepeater_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs) Handles myRepeater.ItemDataBound

'*** CustomerID ***'
Dim lblCustomerID As Label = CType(e.Item.FindControl("lblCustomerID"),Label)
IF Not IsNothing(lblCustomerID) Then
lblCustomerID.Text = e.Item.DataItem("CustomerID")
End IF

'*** Name ***'
Dim lblName As Label = CType(e.Item.FindControl("lblName"),Label)
IF Not IsNothing(lblName) Then
lblName.Text = e.Item.DataItem("Name")
End IF

'*** Email ***'
Dim lblEmail As Label = CType(e.Item.FindControl("lblEmail"),Label)
IF Not IsNothing(lblEmail) Then
lblEmail.Text = e.Item.DataItem("Email")
End IF

'*** CountryCode ***'
Dim lblCountryCode As Label = CType(e.Item.FindControl("lblCountryCode"),Label)
IF Not IsNothing(lblCountryCode) Then
lblCountryCode.Text = e.Item.DataItem("CountryCode")
End IF

'*** Budget ***'
Dim lblBudget As Label = CType(e.Item.FindControl("lblBudget"),Label)
IF Not IsNothing(lblBudget) Then
lblBudget.Text = e.Item.DataItem("Budget")
End IF

'*** Used ***'
Dim lblUsed As Label = CType(e.Item.FindControl("lblUsed"),Label)
IF Not IsNothing(lblUsed) Then
lblUsed.Text = e.Item.DataItem("Used")
End IF

'*** Hyperlink ***'
Dim hplEdit As Hyperlink = CType(e.Item.FindControl("hplEdit"),Hyperlink)
IF Not IsNothing(hplEdit) Then
hplEdit.Text = "Edit"
hplEdit.NavigateUrl = "AspNetAccessEditForm.aspx?CustomerID=" & e.Item.DataItem("CustomerID")
End IF

End Sub

</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Repeater id="myRepeater" runat="server">
<HeaderTemplate>
<table border="1">
<tr>
<th>CustomerID</th>
<th>Name</th>
<th>Email</th>
<th>CountryCode</th>
<th>Budget</th>
<th>Used</th>
<th>Edit</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center"><asp:Label id="lblCustomerID" runat="server"></asp:Label></td>
<td><asp:Label id="lblName" runat="server"></asp:Label></td>
<td><asp:Label id="lblEmail" runat="server"></asp:Label></td>
<td align="center"><asp:Label id="lblCountryCode" runat="server"></asp:Label></td>
<td align="right"><asp:Label id="lblBudget" runat="server"></asp:Label></td>
<td align="right"><asp:Label id="lblUsed" runat="server"></asp:Label></td>
<td align="right"><asp:Hyperlink id="hplEdit" runat="server"></asp:Hyperlink></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</form>
</body>
</html>

AspNetAccessEditForm.aspx

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Dim objConn As New OleDbConnection
Dim objCmd As New OleDbCommand
Dim dtReader As OleDbDataReader
Dim strConnString,strSQL As String

Sub Page_Load(sender As Object, e As EventArgs)
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("database/mydatabase.mdb")&";Jet OLEDB:Database Password=;"
objConn.ConnectionString = strConnString
objConn.Open()

IF Not Page.IsPostBack() Then
ViewData()
End IF
End Sub

Sub ViewData()
'*** DataTable ***'
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
strSQL = "SELECT * FROM customer WHERE CustomerID = '"& Request.QueryString("CustomerID") &"' "
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)

If dt.Rows.Count > 0 Then
Me.txtCustomerID.Text = dt.Rows(0)("CustomerID")
Me.txtName.Text = dt.Rows(0)("Name")
Me.txtEmail.Text = dt.Rows(0)("Email")
Me.txtCountryCode.Text = dt.Rows(0)("CountryCode")
Me.txtBudget.Text = dt.Rows(0)("Budget")
Me.txtUsed.Text = dt.Rows(0)("Used")
End IF
End Sub

Sub btnSave_Click(sender As Object, e As EventArgs)

strSQL = "UPDATE customer SET " & _
" CustomerID = '"& Me.txtCustomerID.Text &"' " & _
" ,Name = '"& Me.txtName.Text &"' " & _
" ,Email = '"& Me.txtEmail.Text &"' " & _
" ,CountryCode = '"& Me.txtCountryCode.Text &"' " & _
" ,Budget = '"& Me.txtBudget.Text &"' " & _
" ,Used = '"& Me.txtUsed.Text &"' " & _
" WHERE CustomerID = '" & Request.QueryString("CustomerID") & "' "

objCmd = New OleDbCommand
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With

Me.pnlAdd.Visible = False
Try
objCmd.ExecuteNonQuery()
Me.lblStatus.Text = "Record Update Sucessful."
Me.lblStatus.Visible = True
Catch ex As Exception
Me.lblStatus.Text = "Record Cannot Update : Error (" & ex.Message & ") "
End Try

End Sub

Sub Page_UnLoad()
objConn.Close()
objConn = Nothing
End Sub

</script>
<html>
<head>
<title>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Panel id="pnlAdd" runat="server">
<table width="353" border="1">
<tbody>
<tr>
<td width="102">
&nbsp;<asp:Label id="lblCustomerID" runat="server" text="CustomerID"></asp:Label></td>
<td width="235">
&nbsp;<asp:TextBox id="txtCustomerID" runat="server" Width="79px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
&nbsp;<asp:Label id="lblName" runat="server" text="Name"></asp:Label></td>
<td>
&nbsp;<asp:TextBox id="txtName" runat="server" Width="177px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
&nbsp;<asp:Label id="lblEmail" runat="server" text="Email"></asp:Label></td>
<td>
&nbsp;<asp:TextBox id="txtEmail" runat="server" Width="155px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
&nbsp;<asp:Label id="lblCountryCode" runat="server" text="CountryCode"></asp:Label></td>
<td>
&nbsp;<asp:TextBox id="txtCountryCode" runat="server" Width="38px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
&nbsp;<asp:Label id="lblBudget" runat="server" text="Budget"></asp:Label></td>
<td>
&nbsp;<asp:TextBox id="txtBudget" runat="server" Width="76px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
&nbsp;<asp:Label id="lblUsed" runat="server" text="Used"></asp:Label></td>
<td>
&nbsp;<asp:TextBox id="txtUsed" runat="server" Width="76px"></asp:TextBox>
</td>
</tr>
</tbody>
</table>
<br />
<asp:Button id="btnSave" onclick="btnSave_Click" runat="server" Text="Save"></asp:Button>
<br />
</asp:Panel>
<asp:Label id="lblStatus" runat="server" visible="True"></asp:Label>
</form>
</body>
</html>

Screenshot

ASP.NET(vb.net) & Microsoft Access Edit/Update Rows Record

ASP.NET(vb.net) & Microsoft Access Edit/Update Rows Record
.
.
.
Download this script.
Download

1 Star2 Stars3 Stars4 Stars5 Stars6 Stars7 Stars8 Stars9 Stars10 Stars (No Ratings Yet)
Loading ... Loading ...

Leave a Reply

You must be logged in to post a comment.