<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<head>
<title>Updating a Row of Data with Validation (OleDb)</title>
<link rel="stylesheet" href="/shared/netdemos.css">
<style type="text/css">
#editPanel {
width:90%; padding:10; background:khaki;
border:thin outset}
#posted {
background:lightgreen; border:thin inset}
</style>
<script language="C#" runat="server">
OleDbConnection myConn = new OleDbConnection ( "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=|DataDirectory|dbtutor.mdb" );
OleDbCommand myCmd = new OleDbCommand ( );
string id;
protected void Page_Load ( object src, EventArgs e ) {
myCmd.Connection = myConn;
id = Request.QueryString [ "id" ];
if ( !IsPostBack ) {
if ( id == null ) {
bindGrid ( );
loadPanel.Visible = true;
editPanel.Visible = false;
postPanel.Visible = false;
} else {
getMessage ( Request.QueryString [ "id" ] );
loadPanel.Visible = false;
editPanel.Visible = true;
postPanel.Visible = false;
}
} else {
loadPanel.Visible = false;
editPanel.Visible = false;
postPanel.Visible = true;
}
}
public void bindGrid ( ) {
myCmd.CommandText = "SELECT * FROM Messages ORDER BY MessageDate DESC";
myConn.Open ( );
myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
myGrid.DataBind ( );
}
public void getMessage ( String id ) {
OleDbDataAdapter myAdapter = new OleDbDataAdapter (
"SELECT * FROM Messages WHERE MessageID=" + id, myConn );
DataTable msgDetails = new DataTable ( );
myAdapter.Fill ( msgDetails );
DataRowView myRow = msgDetails.DefaultView [ 0 ];
msgDate.Value = myRow [ "MessageDate" ].ToString ( );
msgFrom.Value = myRow [ "MessageFrom" ].ToString ( );
msgEmail.Value = myRow [ "Email" ].ToString ( );
msgSubject.Value = myRow [ "MessageSubject" ].ToString ( );
msgBody.Value = myRow [ "MessageBody" ].ToString ( );
editPanel.DataBind ( );
}
public void updateMessage ( object src, EventArgs e ) {
if ( Page.IsValid ) {
myCmd.CommandText = "UPDATE Messages SET MessageFrom=?, Email=?, MessageSubject=?, MessageBody=? WHERE MessageID=" + id;
myCmd.Parameters.Add ( "From", OleDbType.Char ).Value = msgFrom.Value;
myCmd.Parameters.Add ( "Mail", OleDbType.Char ).Value = msgEmail.Value;
myCmd.Parameters.Add ( "Subject", OleDbType.Char ).Value = msgSubject.Value;
myCmd.Parameters.Add ( "Body", OleDbType.VarChar ).Value = msgBody.Value;
myConn.Open ( );
myCmd.ExecuteNonQuery ( );
myConn.Close ( );
}
bindPostPanel ( id );
}
public void bindPostPanel ( String id ) {
myCmd.CommandText = "SELECT * FROM Messages WHERE MessageId='" + id + "'";
myConn.Open ( );
listDetails.DataSource = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
listDetails.DataBind ( );
myConn.Dispose ( );
}
</script>
</head>
<body>
<!-- #include virtual="~/shared/top.inc -->
<div class="header"><h2>ADO.NET Primer: <b>Updating a Row of Data</b> with Validation (<b>OleDb</b>)</h2></div>
<!-- #include virtual="~/shared/viewsrc_top.inc" -->
<hr size=1 width=92%>
<br>
<center>
<form runat="server">
<asp:panel id="loadPanel" runat="server">
<h5>Select a Record to Edit</h5>
<asp:datagrid id="myGrid" runat="server"
width=90% cellpadding=5 gridlines="vertical"
bordercolor="black" borderwidth=1
font-size="10pt"
backcolor="ghostwhite"
alternatingitemstyle-backcolor="lightgray"
autogeneratecolumns=false>
<headerstyle backcolor="darkslategray"
forecolor="khaki" height=25 font-bold />
<columns>
<asp:boundcolumn headertext="Date"
datafield="MessageDate"
dataformatstring="{0:d}" />
<asp:hyperlinkcolumn headertext="From"
datanavigateurlfield="MessageID"
datanavigateurlformatstring="update_oledb.aspx?id={0}"
datatextfield="MessageFrom" />
<asp:boundcolumn headertext="Subject"
datafield="MessageSubject" />
</columns>
</asp:datagrid>
<p align="center"><a href="#top"><img alt="Back to top" border=0 height=32 onmouseover="status='Back to top of page'" src="/shared/top.gif" width=32></a></p>
</asp:panel>
<asp:panel id="editPanel" runat="server">
<h5>To save changes to the database, press Update.</h5>
<input type="hidden" id='msgDate' runat="server">
<table width="85%" cellspacing=1 cellpadding=3 border=0>
<col width=40% align="right">
<col width=60%>
<tr>
<td>Your name:</td>
<td><input id="msgFrom" runat="server">
<asp:requiredfieldvalidator runat="server"
controltovalidate="msgFrom"
errormessage="Sender's name cannot be blank. "
display="none" /></td></tr>
<tr>
<td>Your email address:</td>
<td><input id="msgEmail" runat="server">
<asp:regularexpressionvalidator runat="server"
controltovalidate="msgEmail"
validationexpression="^[\w-]+@[\w-]+\.(com|net|org|edu|mil)$"
errormessage="Please enter a valid e-mail address. "
display="none" /></td></tr>
<tr>
<td>Your subject:</td>
<td><input id="msgSubject" runat="server"></td></tr>
<tr>
<td>Your message:</td>
<td><textarea id="msgBody" rows=5 cols=35 runat="server"></textarea></td></tr>
</table>
<p>
<input type=submit value="Update" runat="server" onServerClick="updateMessage">
<input type=reset value="Cancel" onClick="self.location.replace ( 'update_oledb.aspx' )"></p>
<asp:validationsummary runat="server" displaymode="SingleParagraph"
showmessagebox=true showsummary=false />
</form>
</asp:panel>
<asp:panel id="postPanel" runat="server">
<h5>Thank you for editing your comments. This record has been updated.</h5>
<asp:datalist id="listDetails" width="85%" runat="server">
<itemtemplate>
<table id="posted" width=100% cellspacing=1 cellpadding=5 border=0>
<col width=35% align="right">
<tr>
<td>Date:</td>
<td><b><%# Eval ( "messageDate", "{0:d}" ) %></b></td></tr>
<tr>
<td>Name:</td>
<td><b><%# Eval ( "messageFrom" ) %></b></td></tr>
<tr>
<td>Email:</td>
<td><b><%# Eval ( "Email" ) %></b></td></tr>
<tr>
<td>Subject:</td>
<td><b><%# Eval ( "messageSubject" ) %></b></td></tr>
<tr>
<td>Message:</td>
<td><b><%# Eval ( "messageBody" ) %></b></td></tr>
</table>
</itemtemplate>
</asp:datalist>
<p>
<a href="javascript:history.go ( -1 )"><img alt="Back to Edit View" border=0 height=32 onmouseover="status='Back to Edit View'" src="/shared/prev.gif" width=32></a>
<a href="update_oledb.aspx"><img alt="Select Another Record" border=0 height=32 onmouseover="status='Select Another Record'" src="/shared/next.gif" width=32></a>
</p>
</asp:panel>
</form>
</center>
<hr size=1 width=92%>
<!-- #include virtual="~/shared/viewsrc.inc" -->
</body>
</html>