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

Author: admin | Posted: 13-06-2010 | Category: ASP.NET(vb.net) & Oracle (System.Data.OracleClient)

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

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

Example

AspNetOracleEditListRecord.aspx

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

Dim objConn As OracleConnection
Dim objCmd As OracleCommand

Sub Page_Load(sender As Object, e As EventArgs)
Dim strConnString As String
strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;"
objConn = New OracleConnection(strConnString)
objConn.Open()

BindData()
End Sub

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

Dim dtReader As OracleDataReader
objCmd = New OracleCommand(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 = "AspNetOracleEditForm.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>

AspNetOracleEditForm.aspx

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OracleClient"%>
<%@ Page Language="VB" %>
<script runat="server">
Dim objConn As New OracleConnection
Dim objCmd As New OracleCommand
Dim dtReader As OracleDataReader
Dim strConnString,strSQL As String

Sub Page_Load(sender As Object, e As EventArgs)
strConnString = "Data Source=TCDB;User Id=myuser;Password=mypassword;"
objConn.ConnectionString = strConnString
objConn.Open()

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

Sub ViewData()

'*** DataTable ***'
Dim dtAdapter As OracleDataAdapter
Dim dt As New DataTable
strSQL = "SELECT * FROM customer WHERE CustomerID = '"& Request.QueryString("CustomerID") &"' "
dtAdapter = New OracleDataAdapter(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 OracleCommand
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">
&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="False"></asp:Label>
</form>
</body>
</html>

Screenshot