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

Author: admin | Posted: 13-06-2010 | Category: ASP.NET & MySQL (MySql.Data.MySqlClient)

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

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

Example

AspNetMySQLEditListRecord.aspx

<%@ import Namespace="System.Data" %><br>
<%@ import Namespace="MySql.Data.MySqlClient" %><br>
<%@ Page Language="VB" %><br>
<script runat="server"></p>
<p>Dim objConn As MySqlConnection<br>
Dim objCmd As MySqlCommand</p>
<p>Sub Page_Load(sender As Object, e As EventArgs)<br>
Dim strConnString As String<br>
strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"<br>
objConn = New MySqlConnection(strConnString)<br>
objConn.Open()</p>
<p>BindData()<br>
End Sub</p>
<p>Sub BindData()<br>
Dim strSQL As String<br>
strSQL = "SELECT * FROM customer"</p>
<p>Dim dtReader As MySqlDataReader<br>
objCmd = New MySqlCommand(strSQL, objConn)<br>
dtReader = objCmd.ExecuteReader()</p>
<p>'*** BindData to Repeater ***'<br>
myRepeater.DataSource = dtReader<br>
myRepeater.DataBind()</p>
<p>dtReader.Close()<br>
dtReader = Nothing</p>
<p>End Sub</p>
<p>Sub Page_UnLoad()<br>
objConn.Close()<br>
objConn = Nothing<br>
End Sub</p>
<p>Sub myRepeater_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs) Handles myRepeater.ItemDataBound</p>
<p>'*** CustomerID ***'<br>
Dim lblCustomerID As Label = CType(e.Item.FindControl("lblCustomerID"),Label)<br>
IF Not IsNothing(lblCustomerID) Then<br>
lblCustomerID.Text = e.Item.DataItem("CustomerID")<br>
End IF</p>
<p>'*** Name ***'<br>
Dim lblName As Label = CType(e.Item.FindControl("lblName"),Label)<br>
IF Not IsNothing(lblName) Then<br>
lblName.Text = e.Item.DataItem("Name")<br>
End IF</p>
<p>'*** Email ***'<br>
Dim lblEmail As Label = CType(e.Item.FindControl("lblEmail"),Label)<br>
IF Not IsNothing(lblEmail) Then<br>
lblEmail.Text = e.Item.DataItem("Email")<br>
End IF</p>
<p>'*** CountryCode ***'<br>
Dim lblCountryCode As Label = CType(e.Item.FindControl("lblCountryCode"),Label)<br>
IF Not IsNothing(lblCountryCode) Then<br>
lblCountryCode.Text = e.Item.DataItem("CountryCode")<br>
End IF</p>
<p>'*** Budget ***'<br>
Dim lblBudget As Label = CType(e.Item.FindControl("lblBudget"),Label)<br>
IF Not IsNothing(lblBudget) Then<br>
lblBudget.Text = e.Item.DataItem("Budget")<br>
End IF</p>
<p>'*** Used ***'<br>
Dim lblUsed As Label = CType(e.Item.FindControl("lblUsed"),Label)<br>
IF Not IsNothing(lblUsed) Then<br>
lblUsed.Text = e.Item.DataItem("Used")<br>
End IF</p>
<p>'*** Hyperlink ***'<br>
Dim hplEdit As Hyperlink = CType(e.Item.FindControl("hplEdit"),Hyperlink)<br>
IF Not IsNothing(hplEdit) Then<br>
hplEdit.Text = "Edit"<br>
hplEdit.NavigateUrl = "AspNetMySQLEditForm.aspx?CustomerID=" & e.Item.DataItem("CustomerID")<br>
End IF</p>
<p>End Sub</p>
<p></script><br>
<html><br>
<head><br>
<title>ShotDev.Com Tutorial</title><br>
</head><br>
<body><br>
<form id="form1" runat="server"><br>
<asp:Repeater id="myRepeater" runat="server"><br>
<HeaderTemplate><br>
<table border="1"><br>
<tr><br>
<th>CustomerID</th><br>
<th>Name</th><br>
<th>Email</th><br>
<th>CountryCode</th><br>
<th>Budget</th><br>
<th>Used</th><br>
<th>Edit</th><br>
</tr><br>
</HeaderTemplate><br>
<ItemTemplate><br>
<tr><br>
<td align="center"><asp:Label id="lblCustomerID" runat="server"></asp:Label></td><br>
<td><asp:Label id="lblName" runat="server"></asp:Label></td><br>
<td><asp:Label id="lblEmail" runat="server"></asp:Label></td><br>
<td align="center"><asp:Label id="lblCountryCode" runat="server"></asp:Label></td><br>
<td align="right"><asp:Label id="lblBudget" runat="server"></asp:Label></td><br>
<td align="right"><asp:Label id="lblUsed" runat="server"></asp:Label></td><br>
<td align="right"><asp:Hyperlink id="hplEdit" runat="server"></asp:Hyperlink></td><br>
</tr><br>
</ItemTemplate><br>
</asp:Repeater><br>
</form><br>
</body><br>
</html><br>

AspNetMySQLEditForm.aspx

<%@ import Namespace="System.Data" %><br>
<%@ import Namespace="MySql.Data.MySqlClient" %><br>
<%@ Page Language="VB" %><br>
<script runat="server"><br>
Dim objConn As New MySqlConnection<br>
Dim objCmd As New MySqlCommand<br>
Dim dtReader As MySqlDataReader<br>
Dim strConnString,strSQL As String</p>
<p>Sub Page_Load(sender As Object, e As EventArgs)<br>
strConnString = "Server=localhost;User Id=root; Password=root; Database=mydatabase; Pooling=false"<br>
objConn.ConnectionString = strConnString<br>
objConn.Open()</p>
<p>IF Not Page.IsPostBack() Then<br>
ViewData()<br>
End IF<br>
End Sub</p>
<p>Sub ViewData()<br>
'*** DataTable ***'<br>
Dim dtAdapter As MySqlDataAdapter<br>
Dim dt As New DataTable<br>
strSQL = "SELECT * FROM customer WHERE CustomerID = '"& Request.QueryString("CustomerID") &"' "<br>
dtAdapter = New MySqlDataAdapter(strSQL, objConn)<br>
dtAdapter.Fill(dt)</p>
<p>If dt.Rows.Count > 0 Then<br>
Me.txtCustomerID.Text = dt.Rows(0)("CustomerID")<br>
Me.txtName.Text = dt.Rows(0)("Name")<br>
Me.txtEmail.Text = dt.Rows(0)("Email")<br>
Me.txtCountryCode.Text = dt.Rows(0)("CountryCode")<br>
Me.txtBudget.Text = dt.Rows(0)("Budget")<br>
Me.txtUsed.Text = dt.Rows(0)("Used")<br>
End IF<br>
End Sub</p>
<p>Sub btnSave_Click(sender As Object, e As EventArgs)</p>
<p>strSQL = "UPDATE customer SET " & _<br>
" CustomerID = '"& Me.txtCustomerID.Text &"' " & _<br>
" ,Name = '"& Me.txtName.Text &"' " & _<br>
" ,Email = '"& Me.txtEmail.Text &"' " & _<br>
" ,CountryCode = '"& Me.txtCountryCode.Text &"' " & _<br>
" ,Budget = '"& Me.txtBudget.Text &"' " & _<br>
" ,Used = '"& Me.txtUsed.Text &"' " & _<br>
" WHERE CustomerID = '" & Request.QueryString("CustomerID") & "' "</p>
<p>objCmd = New MySqlCommand<br>
With objCmd<br>
.Connection = objConn<br>
.CommandText = strSQL<br>
.CommandType = CommandType.Text<br>
End With</p>
<p>Me.pnlAdd.Visible = False<br>
Try<br>
objCmd.ExecuteNonQuery()<br>
Me.lblStatus.Text = "Record Updated"<br>
Me.lblStatus.Visible = True<br>
Catch ex As Exception<br>
Me.lblStatus.Text = "Record can not update"<br>
End Try</p>
<p>End Sub</p>
<p>Sub Page_UnLoad()<br>
objConn.Close()<br>
objConn = Nothing<br>
End Sub</p>
<p></script><br>
<html><br>
<head><br>
<title>ShotDev.Com Tutorial</title><br>
</head><br>
<body><br>
<form id="form1" runat="server"><br>
<asp:Panel id="pnlAdd" runat="server"><br>
<table width="353" border="1"><br>
<tbody><br>
<tr><br>
<td width="102"><br>
&nbsp;<asp:Label id="lblCustomerID" runat="server" text="CustomerID"></asp:Label></td><br>
<td width="235"><br>
&nbsp;<asp:TextBox id="txtCustomerID" runat="server" Width="79px"></asp:TextBox><br>
</td><br>
</tr><br>
<tr><br>
<td><br>
&nbsp;<asp:Label id="lblName" runat="server" text="Name"></asp:Label></td><br>
<td><br>
&nbsp;<asp:TextBox id="txtName" runat="server" Width="177px"></asp:TextBox><br>
</td><br>
</tr><br>
<tr><br>
<td><br>
&nbsp;<asp:Label id="lblEmail" runat="server" text="Email"></asp:Label></td><br>
<td><br>
&nbsp;<asp:TextBox id="txtEmail" runat="server" Width="155px"></asp:TextBox><br>
</td><br>
</tr><br>
<tr><br>
<td><br>
&nbsp;<asp:Label id="lblCountryCode" runat="server" text="CountryCode"></asp:Label></td><br>
<td><br>
&nbsp;<asp:TextBox id="txtCountryCode" runat="server" Width="38px"></asp:TextBox><br>
</td><br>
</tr><br>
<tr><br>
<td><br>
&nbsp;<asp:Label id="lblBudget" runat="server" text="Budget"></asp:Label></td><br>
<td><br>
&nbsp;<asp:TextBox id="txtBudget" runat="server" Width="76px"></asp:TextBox><br>
</td><br>
</tr><br>
<tr><br>
<td><br>
&nbsp;<asp:Label id="lblUsed" runat="server" text="Used"></asp:Label></td><br>
<td><br>
&nbsp;<asp:TextBox id="txtUsed" runat="server" Width="76px"></asp:TextBox><br>
</td><br>
</tr><br>
</tbody><br>
</table><br>
<br /><br>
<asp:Button id="btnSave" onclick="btnSave_Click" runat="server" Text="Save"></asp:Button><br>
<br /><br>
</asp:Panel><br>
<asp:Label id="lblStatus" runat="server" visible="False"></asp:Label><br>
</form><br>
</body><br>
</html>

Screenshot