SqlDataSource.Update Method ()
Performs an update operation using the UpdateCommand SQL string and any parameters that are in the UpdateParameters collection.
Assembly: System.Web (in System.Web.dll)
Return Value
Type: System.Int32A value that represents the number of rows updated in the underlying database.
| Exception | Condition |
|---|---|
| InvalidOperationException | The SqlDataSource cannot establish a connection with the underlying data source. |
The Update method is automatically called by the GridView, DetailsView, and FormView controls during postback if the data has been changed. For data that has been changed in other controls, the Update method can be explicitly called on postback during the Load event.
Before the Update operation is performed, the OnUpdating method is called to raise the Updating event. You can handle this event to examine the values of the parameters and to perform any preprocessing before an Update operation.
After the Update operation completes, the OnUpdated method is called to raise the Updated event. You can handle this event to examine any return values and error codes and to perform any post-processing.
The Update method delegates to the Update method of the SqlDataSourceView object that is associated with the SqlDataSource control. To perform an update operation, the SqlDataSourceView builds a DbCommand object using the UpdateCommand text and any associated UpdateParameters properties, and then executes the DbCommand object against the underlying database.
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. |
This section contains two code examples. The first code example demonstrates how to use a SqlDataSource control to display data in a DropDownList control and update data when the Submit button is clicked. The second 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 following code example demonstrates how to use a SqlDataSource control to display data in a DropDownList control and update data when the Submit button is clicked. The UpdateCommand property is set with a parameterized SQL statement, and two ControlParameter parameters are added to the UpdateParameters collection. When the Submit button is clicked, the OnClick event is handled to call the Update method explicitly.
<%@Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> Sub On_Click(ByVal source As Object, ByVal e As EventArgs) Try SqlDataSource1.Update() Catch except As Exception ' Handle the Exception. End Try Label2.Text="The record was updated successfully!" End Sub 'On_Click </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"> <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>
The following code example demonstrates how to display data that is retrieved from a 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="VB" %> <%@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"> Sub On_Click(ByVal source As Object, ByVal e As EventArgs) SqlDataSource1.Update() End Sub 'On_Click Sub On_Sql_Updating(ByVal source As Object, ByVal e As SqlDataSourceCommandEventArgs) Dim command as DbCommand Dim connection as DbConnection Dim transaction as DbTransaction command = e.Command connection = command.Connection connection.Open() transaction = connection.BeginTransaction() command.Transaction = transaction End Sub 'On_Sql_Updating Sub On_Sql_Updated(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs) Dim command As DbCommand Dim transaction As DbTransaction command = e.Command transaction = 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. Dim OtherProcessSucceeded as Boolean = True If (OtherProcessSucceeded) Then transaction.Commit() Label2.Text="The record was updated successfully!" Else transaction.Rollback() Label2.Text="The record was not updated." End If End Sub ' On_Sql_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="On_Sql_Updating" OnUpdated ="On_Sql_Updated"> <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>
Available since 2.0
