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">
<asp:Label id="lblCustomerID" runat="server" text="CustomerID"></asp:Label></td>
<td width="235">
<asp:TextBox id="txtCustomerID" runat="server" Width="79px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label id="lblName" runat="server" text="Name"></asp:Label></td>
<td>
<asp:TextBox id="txtName" runat="server" Width="177px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label id="lblEmail" runat="server" text="Email"></asp:Label></td>
<td>
<asp:TextBox id="txtEmail" runat="server" Width="155px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label id="lblCountryCode" runat="server" text="CountryCode"></asp:Label></td>
<td>
<asp:TextBox id="txtCountryCode" runat="server" Width="38px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label id="lblBudget" runat="server" text="Budget"></asp:Label></td>
<td>
<asp:TextBox id="txtBudget" runat="server" Width="76px"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label id="lblUsed" runat="server" text="Used"></asp:Label></td>
<td>
<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
.
.
.
Download this script.
Loading ...