SqlDataSourceView.InsertCommand Property
Assembly: System.Web (in system.web.dll)
/** @property */ public String get_InsertCommand () /** @property */ public void set_InsertCommand (String value)
public function get InsertCommand () : String public function set InsertCommand (value : String)
Not applicable.
Property Value
An SQL string that the SqlDataSourceView uses to insert data.Because different database products use different varieties of SQL, the syntax of the SQL string depends on the current ADO.NET provider being used, which is identified by the ProviderName property. If the SQL string is a parameterized query or command, the placeholder of the parameter also depends on the ADO.NET provider being used. For example, if the provider is the System.Data.SqlClient, which is the default provider for the SqlDataSource class, the placeholder of the parameter is '@parameterName'. However, if the provider is set to the System.Data.Odbc or System.Data.OleDb, the placeholder of the parameter is '?'. For more information on parameterized SQL queries and commands, see Parameters with the SqlDataSource and AccessDataSource Controls.
The InsertCommand property can be an SQL string or the name of a stored procedure, if the data source supports stored procedures.
The value of the InsertCommand property is stored in view state.
This section contains two code examples. The first code example demonstrates how to insert data into a database using the SqlDataSource control. The second code example demonstrates how to retrieve data from Microsoft SQL Server, display it in a GridView control, and use a DetailsView control to see details of a selected row in the GridView and as a form to insert new records.
The following code example demonstrates how to insert data into a database using the SqlDataSource control. The current data in the table is displayed in the DropDownList control. You can add new records by entering values in the TextBox controls, and then clicking the Insert button. When the Insert button is clicked, the specified values are inserted into the database and the DropDownList is refreshed.
<%@Page Language="VJ#" %>
<!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 InsertShipper(Object source, System.EventArgs e)
{
SqlDataSource1.Insert();
} //InsertShipper
</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:dropdownlist
id="DropDownList1"
runat="server"
datasourceid="SqlDataSource1"
datatextfield="CompanyName"
datavaluefield="ShipperID" />
<asp:sqldatasource
id="SqlDataSource1"
runat="server"
connectionstring="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;Connect Timeout=15"
selectcommand="SELECT CompanyName,ShipperID FROM Shippers"
insertcommand="INSERT INTO Shippers (CompanyName,Phone) VALUES (@CoName,@Phone)">
<insertparameters>
<asp:formparameter name="CoName" formfield="CompanyNameBox" />
<asp:formparameter name="Phone" formfield="PhoneBox" />
</insertparameters>
</asp:sqldatasource>
<br /><asp:textbox
id="CompanyNameBox"
runat="server" />
<asp:RequiredFieldValidator
id="RequiredFieldValidator1"
runat="server"
ControlToValidate="CompanyNameBox"
Display="Static"
ErrorMessage="Please enter a company name." />
<br /><asp:textbox
id="PhoneBox"
runat="server" />
<asp:RequiredFieldValidator
id="RequiredFieldValidator2"
runat="server"
ControlToValidate="PhoneBox"
Display="Static"
ErrorMessage="Please enter a phone number." />
<br /><asp:button
id="Button1"
runat="server"
text="Insert New Shipper"
onclick="InsertShipper" />
</form>
</body>
</html>
The following code example demonstrates how to retrieve data from SQL Server, display it in a GridView control, and use a DetailsView control to see details of a selected row in the GridView and as a form to insert new records.
Initially, the data is displayed in the GridView control and the selected row of the GridView is also displayed in the DetailsView control. The GridView and DetailsView controls use different data source controls; the one that is associated with the DetailsView has the FilterExpression and FilterParameters properties, which ensure that the selected row of the GridView is displayed.
If you click the automatically generated Insert button of the DetailsView control, the DetailsView shows a different user interface, which is used to insert a new record. The example uses a stored procedure to insert records and returns the primary key of the inserted row. If you insert a record, the DetailsView automatically populates the InsertParameters collection with values from the bound columns and calls the Insert method. The DetailsView can infer the correct parameters from any BoundField object and a parameter for the TemplateField object when the ASP.NET two-way data-binding syntax is used. In this example, an additional parameter is added in the OnInserting event handler to handle the primary key that is returned by the stored procedure.
Finally, after data is inserted by the DetailsView control into the database, the OnInserted event handler is called to handle the Inserted event, the value of the primary key of the inserted row is displayed, and the DataBind method of the GridView control is called explicitly to refresh the data.
<%@Page Language="VJ#" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!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_Inserting(Object sender, SqlDataSourceCommandEventArgs e)
{
// Add the Title parameter from the TemplatedField.
// Cast the first Item of the DetailsView to a Table.
Table tbl = (Table)DetailsView1.get_Controls().get_Item(0);
// Get row two (the third row) from the rows collection.
// This is the row that the DropDownList is in.
TableRowCollection rows = tbl.get_Rows();
TableRow titleRow = rows.get_Item(2);
// Cast the second item in the controls collection of cell one as a
// DropDownList.
DropDownList title = (DropDownList)titleRow.get_Cells().get_Item(1).
get_Controls().get_Item(1);
SqlParameter titleParam = new SqlParameter(
"@Title", title.get_SelectedValue());
e.get_Command().get_Parameters().Add(titleParam);
} //On_Inserting
private void On_Inserted(Object sender, SqlDataSourceStatusEventArgs e)
{
// Explicitly call DataBind to refresh the data
// and show the newly inserted row.
GridView1.DataBind();
} //On_Inserted
</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:GridView
id="GridView1"
runat="server"
AutoGenerateColumns="False"
DataKeyNames="EmployeeID"
SelectedIndex="0"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField HeaderText="First Name" DataField="FirstName" />
<asp:BoundField HeaderText="Last Name" DataField="LastName" />
<asp:BoundField HeaderText="Title" DataField="Title" />
<asp:ButtonField ButtonType="Link" CommandName="Select" Text="Details..." />
</Columns>
</asp:GridView>
<asp:SqlDataSource
id="SqlDataSource1"
runat="server"
ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees">
</asp:SqlDataSource>
<hr />
<asp:DetailsView
id="DetailsView1"
runat="server"
DataSourceID="SqlDataSource2"
AutoGenerateRows="False"
AutoGenerateInsertButton="True">
<Fields>
<asp:BoundField HeaderText="First Name" DataField="FirstName" ReadOnly="False"/>
<asp:BoundField HeaderText="Last Name" DataField="LastName" ReadOnly="False"/>
<asp:TemplateField HeaderText="Title">
<ItemTemplate>
<asp:DropDownList
runat="server">
<asp:ListItem Selected="True">Sales Representative</asp:ListItem>
<asp:ListItem>Sales Manager</asp:ListItem>
<asp:ListItem>Vice President, Sales</asp:ListItem>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="Notes" DataField="Notes" ReadOnly="False"/>
</Fields>
</asp:DetailsView>
<asp:SqlDataSource
id="SqlDataSource2"
runat="server"
ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
SelectCommand="SELECT * FROM Employees"
InsertCommand="INSERT INTO Employees(FirstName,LastName,Title,Notes)VALUES (@FirstName,@LastName,@Title,@Notes)"
OnInserting="On_Inserting"
OnInserted="On_Inserted"
FilterExpression="EmployeeID=@EmployeeID">
<FilterParameters>
<asp:ControlParameter Name="EmployeeID" ControlId="GridView1" PropertyName="SelectedDataKey.Value"/>
</FilterParameters>
</asp:SqlDataSource>
</form>
</body>
</html>