ASP.NET(vb.net) & SQL Server Edit/Update Rows Record - This is example scripts how to use ASP.NET edit update rows in SQL Server database.
ShotDev Focus:
- ASP.NET(vb.net) & SQL Server Edit/Update Rows Record
Example
AspNetSQLServerEditListRecord.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="VB" %>
<script runat="server">
Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Sub Page_Load(sender As Object, e As EventArgs)
Dim strConnString As String
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"
objConn = New SqlConnection(strConnString)
objConn.Open()
BindData()
End Sub
Sub BindData()
Dim strSQL As String
strSQL = "SELECT * FROM customer"
Dim dtReader As SqlDataReader
objCmd = New SqlCommand(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 = "AspNetSQLServerEditForm.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>
AspNetSQLServerEditForm.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="VB" %>
<script runat="server">
Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim dtReader As SqlDataReader
Dim strConnString,strSQL As String
Sub Page_Load(sender As Object, e As EventArgs)
strConnString = "Server=localhost;Uid=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"
objConn.ConnectionString = strConnString
objConn.Open()
IF Not Page.IsPostBack() Then
ViewData()
End IF
End Sub
Sub ViewData()
'*** DataTable ***'
Dim dtAdapter As SqlDataAdapter
Dim dt As New DataTable
strSQL = "SELECT * FROM customer WHERE CustomerID = '"& Request.QueryString("CustomerID") &"' "
dtAdapter = New SqlDataAdapter(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 SqlCommand
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
Me.pnlAdd.Visible = False
Try
objCmd.ExecuteNonQuery()
Me.lblStatus.Text = "Record Updated"
Me.lblStatus.Visible = True
Catch ex As Exception
Me.lblStatus.Text = "Record can not update"
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="False"></asp:Label>
</form>
</body>
</html>
Screenshot
.
.
.
Download this script.
Loading ...