SqlDataSource.Updating Event
Assembly: System.Web (in system.web.dll)
Handle the Updating event to perform additional initialization operations that are specific to your application, to validate the values of parameters, or to change the parameter values before the SqlDataSource control performs the update operation. The connection to the underlying data source is not yet open when the event handler delegate is called. Therefore, you cannot directly cancel the Update database operation by calling the Cancel method on the DbCommand object that is exposed by the SqlDataSourceCommandEventArgs object. You can, however, cancel the database operation by setting the Cancel property of the SqlDataSourceCommandEventArgs to true.
For more information about handling events, see Consuming Events.
Security Note: |
|---|
| Values are inserted into parameters without validation, which is a potential security threat. Use the Updating event to validate parameter values before executing the query. For more information, see Script Exploits Overview (Visual Studio). |
The following code example demonstrates how to display data that is retrieved from a Microsoft SQL Server database in a DropDownList control and update the record using a TextBox control. The example shows how you can use a DbTransaction object to add transaction context when using the SqlDataSource control to update data.
<%@Page Language="C#" %> <%@Import Namespace="System.Data" %> <%@Import Namespace="System.Data.Common" %> <%@Import Namespace="System.Data.SqlClient" %> <%@Import Namespace="System.Diagnostics" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> private void On_Click(Object source, EventArgs e) { SqlDataSource1.Update(); } private void OnSqlUpdating(Object source, SqlDataSourceCommandEventArgs e) { DbCommand command = e.Command; DbConnection cx = command.Connection; cx.Open(); DbTransaction tx = cx.BeginTransaction(); command.Transaction = tx; } private void OnSqlUpdated(Object source, SqlDataSourceStatusEventArgs e) { DbCommand command = e.Command; DbTransaction tx = command.Transaction; // In this code example the OtherProcessSucceeded variable represents // the outcome of some other process that occurs whenever the data is // updated, and must succeed for the data change to be committed. For // simplicity, we set this value to true. bool OtherProcessSucceeded = true; if (OtherProcessSucceeded) { tx.Commit(); Label2.Text="The record was updated successfully!"; } else { tx.Rollback(); Label2.Text="The record was not updated."; } } </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>ASP.NET Example</title> </head> <body> <form id="form1" runat="server"> <asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees" UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID" OnUpdating="OnSqlUpdating" OnUpdated ="OnSqlUpdated"> <UpdateParameters> <asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/> <asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/> </UpdateParameters> </asp:SqlDataSource> <asp:DropDownList id="DropDownList1" runat="server" DataTextField="LastName" DataValueField="EmployeeID" DataSourceID="SqlDataSource1"> </asp:DropDownList> <br /> <asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user." AssociatedControlID="TextBox1" /> <asp:TextBox id="TextBox1" runat="server" /> <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" /> <br /><asp:Label id="Label2" runat="server" Text="" /> </form> </body> </html>
<%@Page Language="VJ#" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Diagnostics" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
private void On_Click(Object source, System.EventArgs e)
{
try {
SqlDataSource1.Update();
}
catch (System.Exception except) {
// Handle Exception
}
Label2.set_Text("The record was updated successfully!");
} //On_Click
private void OnSqlUpdate(Object source, SqlDataSourceCommandEventArgs e)
{
// Log the command in the Event Log on the Web server.
String logInfo = e.get_Command().get_CommandText()
+ " is being submitted to the database.";
IEnumerator ie = e.get_Command().get_Parameters().GetEnumerator();
while (ie.MoveNext()) {
DbParameter param = ((DbParameter)(ie.get_Current()));
logInfo = logInfo + " " + param.get_ParameterName()+ "="
+ param.get_Value();
}
EventLog log = new EventLog();
log.set_Log("Application");
log.set_Source("ASP.NET Example");
log.WriteEntry(logInfo);
} //OnSqlUpdate
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>ASP.NET Example</title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource
id="SqlDataSource1"
runat="server"
ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
SelectCommand="SELECT EmployeeID, LastName, Address FROM Employees"
UpdateCommand="UPDATE Employees SET Address=@Address WHERE EmployeeID=@EmployeeID"
OnUpdating="OnSqlUpdate">
<UpdateParameters>
<asp:ControlParameter Name="Address" ControlId="TextBox1" PropertyName="Text"/>
<asp:ControlParameter Name="EmployeeID" ControlId="DropDownList1" PropertyName="SelectedValue"/>
</UpdateParameters>
</asp:SqlDataSource>
<asp:DropDownList
id="DropDownList1"
runat="server"
DataTextField="LastName"
DataValueField="EmployeeID"
DataSourceID="SqlDataSource1">
</asp:DropDownList>
<br />
<asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user."
AssociatedControlID="TextBox1" />
<asp:TextBox id="TextBox1" runat="server" />
<asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />
<br /><asp:Label id="Label2" runat="server" Text="" />
</form>
</body>
</html>
Reference
SqlDataSource ClassSqlDataSource Members
System.Web.UI.WebControls Namespace
SqlDataSource.Updated Event
OnUpdating
Update
SqlDataSource.UpdateParameters Property
Other Resources
ASP.NET Data Access OverviewData Source Web Server Controls
SqlDataSource Web Server Control Overview
Introduction to the ASP.NET Page Life Cycle
Security Note: