Using Parameters with Data Source Controls for Inserting and Updating
When you use a data-bound control such as a GridView, DetailsView, or FormView control with an ASP.NET data source control, the data-bound control can pass parameter names and values to the data source control based on the bound fields in the data-bound control. The data source control then includes the field names and values in the parameter collection for select or update operations. For more information, see Using Parameters with the SqlDataSource Control and Using Parameters with the ObjectDataSource Control.
Dictionaries Passed to Data Source Controls
When a data-bound control requests an operation from the data source control, it passes one or more IDictionary collections containing parameter names and values for the requested data operation. The values of the name/value pairs in the dictionary are derived from child controls. For example, in an update operation, the data-bound control reads parameter values from TextBox or CheckBox controls that are displayed in edit mode. The names for the name/value pairs are taken from the names of the fields bound to child controls and from the field names specified in the DataKeyNames property. For an update or delete operation, the data-bound control might also pass a dictionary containing the original values of the data record.
Name/value pairs are passed using the following IDictionary collections:
Values collection Passed for an insert operation. Contains the name/value pairs for a new record. Field names and values for the Values collection are taken from child controls in an InsertItemTemplate or from bound fields in a DetailsView control whose InsertVisible property is set to true.
Keys collection Passed for update and delete operations. Contains the primary key or keys for the record being updated or deleted. If the key fields can be modified at the data source, the Keys collection also contains the original values of the key fields. When a data-bound control is populated with data from the data source control, it maintains that data in view state. When an update or delete operation is requested, the Keys collection is populated with values stored earlier in view state. If the data-bound control's EnableViewState property is set to false, the Keys collection is not populated for the update or delete operation.
NewValues collection Passed for an update operation. Contains the name/value pairs with new values for the updated item, including new values for updatable key fields. Field names and values for the NewValues collection are taken from child controls in an EditItemTemplate or from bound fields in a DetailsView control whose ReadOnly property is set to false.
OldValues collection Passed for update or delete operations. Contains the original values for the data record to use for optimistic concurrency checking. (For information on optimistic concurrency checking, see the ConflictDetection property of the data source control you are working with.) Values for key fields identified by the DataKeyNames property are not included in the OldValues collection. Key field names and values are included only in the Keys collection. When a data-bound control is populated with data from the data source control, it maintains that data in view state. When an update or delete operation is requested, the OldValues collection is populated with values stored earlier in view state. If the data-bound control's EnableViewState property is set to false, the OldValues collection is not populated for the update or delete operation.
You can access all of these collections using the arguments passed with the data-bound control event for the requested operation. For example, in the GridView control's RowUpdating event, the GridViewUpdateEventArgs class provides access to the NewValues collection.
Parameter Names
The data source control creates parameters automatically for the values passed in the IDictionary collections. For an insert operation, the data source control populates its InsertParameters collection with values from the name/value pairs in the Values collection. For an update operation, the data source control populates its UpdateParameters collection with values from the name/value pairs in the Keys, NewValues, and OldValues collections. For a delete operation, the data source control populates its DeleteParameters collection with values from the name/value pairs in the Keys and OldValues collections.
The OldValues collection is not populated by default. It is populated only when the data-source control's ConflictDetection property is set to CompareAllValues.
For an update or delete operation, by default only parameters for current bound values are created. If you have to access both current and original bound values (for example, to support optimistic concurrency checks), you can have the data source control create parameters for both current and original values. To do this, you must establish a naming convention for parameters that will contain original values. The format of the parameters for original values is determined by the OldValuesParameterFormatString property. Set the OldValuesParameterFormatString property to a string that includes "{0}" as a placeholder for the name of the field. For example, if you are using the SqlDataSource control, and if you set the OldValuesParameterFormatString property to "old_{0}", the names of the original-value parameters will resolve to the field name prefixed with "@old_". (The SqlDataSource control appends an "@" character to the beginning of all parameter names.) Consider an update operation that involves a field named LastModifiedDate. The current value for the field is passed in the Values dictionary, and the original value for the field is passed in the OldValues dictionary. A parameter named @LastModifiedDate is created to pass the current value, and a parameter named @old\_LastModifiedDate is created to pass the original value. You can then include both parameters in an SQL statement to differentiate between the current and original values for the field, as shown in the following example:
UPDATE Table1 SET LastModifiedDate = @LastModifiedDate
WHERE Key = @Key AND LastModifiedDate = @old_LastModifiedDate
You do not have to access the name/value IDictionary collections directly. You can simply include the automatically generated parameter names in your SQL statements (if your data source supports named parameters), or as the names of the parameters for the data methods in a business object you are accessing with an ObjectDataSource control.
Optionally, you can define Parameter objects in the data source control's UpdateParameters, InsertParameters, or DeleteParameters collections in order to customize the values passed by the data-bound control. You might create Parameter objects to strongly type the value or to specify a default value if null is passed.
The following code example shows a DetailsView control bound to a SqlDataSource control. The SqlDataSource control's InsertCommand, UpdateCommand, and DeleteCommand properties use the parameter names that are automatically generated by the SqlDataSource control. The parameter values are populated based on the Keys and NewValues dictionaries. The OldValues dictionary is not used because the ConflictDetection property is set to ConflictOptions.OverwriteChanges, the default.
<%@ Page language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Sub EmployeesDropDownList_OnSelectedIndexChanged(sender As Object, e As EventArgs)
EmployeeDetailsView.DataBind()
End Sub
Sub EmployeeDetailsView_ItemUpdated(sender As Object, e As DetailsViewUpdatedEventArgs)
EmployeesDropDownList.DataBind()
EmployeesDropDownList.SelectedValue = e.Keys("EmployeeID").ToString()
EmployeeDetailsView.DataBind()
End Sub
Sub EmployeeDetailsView_ItemDeleted(sender As Object, e As DetailsViewDeletedEventArgs)
EmployeesDropDownList.DataBind()
End Sub
Sub EmployeeDetailsSqlDataSource_OnInserted(sender As Object, e As SqlDataSourceStatusEventArgs)
Dim command As System.Data.Common.DbCommand = e.Command
EmployeesDropDownList.DataBind()
EmployeesDropDownList.SelectedValue = _
command.Parameters("@EmpID").Value.ToString()
EmployeeDetailsView.DataBind()
End Sub
</script>
<html xmlns="https://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Northwind Employees</title>
</head>
<body>
<form id="form1" runat="server">
<h3>Northwind Employees</h3>
<table cellspacing="10">
<tr>
<td valign="top">
<asp:DropDownList ID="EmployeesDropDownList"
DataSourceID="EmployeesSqlDataSource"
DataValueField="EmployeeID"
DataTextField="FullName"
AutoPostBack="True"
OnSelectedIndexChanged="EmployeesDropDownList_OnSelectedIndexChanged"
RunAt="Server" />
</td>
<td valign="top">
<asp:DetailsView ID="EmployeeDetailsView"
DataSourceID="EmployeeDetailsSqlDataSource"
AutoGenerateRows="false"
AutoGenerateInsertbutton="true"
AutoGenerateEditbutton="true"
AutoGenerateDeletebutton="true"
DataKeyNames="EmployeeID"
Gridlines="Both"
OnItemUpdated="EmployeeDetailsView_ItemUpdated"
OnItemDeleted="EmployeeDetailsView_ItemDeleted"
RunAt="server">
<HeaderStyle backcolor="Navy"
forecolor="White"/>
<RowStyle backcolor="White"/>
<AlternatingRowStyle backcolor="LightGray"/>
<EditRowStyle backcolor="LightCyan"/>
<Fields>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>
<asp:BoundField DataField="FirstName" HeaderText="First Name"/>
<asp:BoundField DataField="LastName" HeaderText="Last Name"/>
<asp:BoundField DataField="Address" HeaderText="Address"/>
<asp:BoundField DataField="City" HeaderText="City"/>
<asp:BoundField DataField="Region" HeaderText="Region"/>
<asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>
</Fields>
</asp:DetailsView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="EmployeesSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees"
Connectionstring="<%$ ConnectionStrings:NorthwindConnection %>"
RunAt="server">
</asp:SqlDataSource>
<asp:SqlDataSource ID="EmployeeDetailsSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode
FROM Employees WHERE EmployeeID = @EmpID"
InsertCommand="INSERT INTO Employees(LastName, FirstName, Address, City, Region, PostalCode)
VALUES (@LastName, @FirstName, @Address, @City, @Region, @PostalCode);
SELECT @EmpID = SCOPE_IDENTITY()"
UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName, Address=@Address,
City=@City, Region=@Region, PostalCode=@PostalCode
WHERE EmployeeID=@EmployeeID"
DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
RunAt="server">
<SelectParameters>
<asp:ControlParameter ControlID="EmployeesDropDownList" PropertyName="SelectedValue"
Name="EmpID" Type="Int32" DefaultValue="0" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="Region" Type="String" />
<asp:Parameter Name="PostalCode" Type="String" />
<asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="Region" Type="String" />
<asp:Parameter Name="PostalCode" Type="String" />
<asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
</DeleteParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
<%@ Page language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
void EmployeesDropDownList_OnSelectedIndexChanged(Object sender, EventArgs e)
{
EmployeeDetailsView.DataBind();
}
void EmployeeDetailsView_ItemUpdated(Object sender, DetailsViewUpdatedEventArgs e)
{
EmployeesDropDownList.DataBind();
EmployeesDropDownList.SelectedValue = e.Keys["EmployeeID"].ToString();
EmployeeDetailsView.DataBind();
}
void EmployeeDetailsView_ItemDeleted(Object sender, DetailsViewDeletedEventArgs e)
{
EmployeesDropDownList.DataBind();
}
void EmployeeDetailsSqlDataSource_OnInserted(Object sender, SqlDataSourceStatusEventArgs e)
{
System.Data.Common.DbCommand command = e.Command;
EmployeesDropDownList.DataBind();
EmployeesDropDownList.SelectedValue =
command.Parameters["@EmpID"].Value.ToString();
EmployeeDetailsView.DataBind();
}
</script>
<html xmlns="https://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Northwind Employees</title>
</head>
<body>
<form id="form1" runat="server">
<h3>Northwind Employees</h3>
<table cellspacing="10">
<tr>
<td valign="top">
<asp:DropDownList ID="EmployeesDropDownList"
DataSourceID="EmployeesSqlDataSource"
DataValueField="EmployeeID"
DataTextField="FullName"
AutoPostBack="True"
OnSelectedIndexChanged="EmployeesDropDownList_OnSelectedIndexChanged"
RunAt="Server" />
</td>
<td valign="top">
<asp:DetailsView ID="EmployeeDetailsView"
DataSourceID="EmployeeDetailsSqlDataSource"
AutoGenerateRows="false"
AutoGenerateInsertbutton="true"
AutoGenerateEditbutton="true"
AutoGenerateDeletebutton="true"
DataKeyNames="EmployeeID"
Gridlines="Both"
OnItemUpdated="EmployeeDetailsView_ItemUpdated"
OnItemDeleted="EmployeeDetailsView_ItemDeleted"
RunAt="server">
<HeaderStyle backcolor="Navy"
forecolor="White"/>
<RowStyle backcolor="White"/>
<AlternatingRowStyle backcolor="LightGray"/>
<EditRowStyle backcolor="LightCyan"/>
<Fields>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee ID" InsertVisible="False" ReadOnly="true"/>
<asp:BoundField DataField="FirstName" HeaderText="First Name"/>
<asp:BoundField DataField="LastName" HeaderText="Last Name"/>
<asp:BoundField DataField="Address" HeaderText="Address"/>
<asp:BoundField DataField="City" HeaderText="City"/>
<asp:BoundField DataField="Region" HeaderText="Region"/>
<asp:BoundField DataField="PostalCode" HeaderText="Postal Code"/>
</Fields>
</asp:DetailsView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="EmployeesSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName + ', ' + FirstName AS FullName FROM Employees"
Connectionstring="<%$ ConnectionStrings:NorthwindConnection %>"
RunAt="server">
</asp:SqlDataSource>
<asp:SqlDataSource ID="EmployeeDetailsSqlDataSource"
SelectCommand="SELECT EmployeeID, LastName, FirstName, Address, City, Region, PostalCode
FROM Employees WHERE EmployeeID = @EmpID"
InsertCommand="INSERT INTO Employees(LastName, FirstName, Address, City, Region, PostalCode)
VALUES (@LastName, @FirstName, @Address, @City, @Region, @PostalCode);
SELECT @EmpID = SCOPE_IDENTITY()"
UpdateCommand="UPDATE Employees SET LastName=@LastName, FirstName=@FirstName, Address=@Address,
City=@City, Region=@Region, PostalCode=@PostalCode
WHERE EmployeeID=@EmployeeID"
DeleteCommand="DELETE Employees WHERE EmployeeID=@EmployeeID"
ConnectionString="<%$ ConnectionStrings:NorthwindConnection %>"
OnInserted="EmployeeDetailsSqlDataSource_OnInserted"
RunAt="server">
<SelectParameters>
<asp:ControlParameter ControlID="EmployeesDropDownList" PropertyName="SelectedValue"
Name="EmpID" Type="Int32" DefaultValue="0" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="Region" Type="String" />
<asp:Parameter Name="PostalCode" Type="String" />
<asp:Parameter Name="EmpID" Direction="Output" Type="Int32" DefaultValue="0" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="Region" Type="String" />
<asp:Parameter Name="PostalCode" Type="String" />
<asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="EmployeeID" Type="Int32" DefaultValue="0" />
</DeleteParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
See Also
Concepts
Data Source Web Server Controls
Using Parameters with Data Source Controls for Filtering