SqlDataSourceView.SelectCommand Property
Assembly: System.Web (in system.web.dll)
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 Using Parameters with the SqlDataSource Control.
The SelectCommand property can be an SQL string or the name of a stored procedure, if the data source supports stored procedures.
Security Note |
|---|
| It is more secure to use a stored procedure than a SQL statement for the SelectCommand property. |
The value of the SelectCommand property is stored in view state.
This section contains two code examples. The first code example demonstrates how to set the SelectCommand text to a basic SQL query to retrieve data from a Microsoft SQL Server database and display it in a DropDownList control. The second code example demonstrates how to set the SelectCommand text to the name of a stored procedure to retrieve data from a SQL Server database and display it in a DropDownList.
The following code example demonstrates how to set the SelectCommand text to a basic SQL query to retrieve data from a SQL Server database and display it in a DropDownList control. The Button and TextBox controls are provided as a simple interface to update the address for the selected user in the DropDownList.
<%@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 On_Click(Object source, System.EventArgs e)
{
try {
SqlDataSource1.Update();
}
catch (Exception except) {
// Handle the Exception.
}
Label2.set_Text("The record was updated successfully!");
} //On_Click
</SCRIPT>
<HTML>
<BODY>
<FORM 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">
<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>
<P>
<asp:Label id="Label1" runat="server" Text="Enter a new address for the selected user." />
<asp:TextBox id="TextBox1" runat="server" />
<asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />
<P><asp:Label id="Label2" runat="server" Text="" />
</FORM>
</BODY>
</HTML>
The following code example demonstrates how to set the SelectCommand text to the name of a stored procedure to retrieve data from a SQL Server database and display it in a DropDownList control. The SelectCommand property can be an SQL query or the name of a stored procedure, if the data source supports stored procedures.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML>
<BODY>
<FORM runat="server">
<asp:DropDownList
id="DropDownList1"
runat="server"
DataTextField="LastName"
DataSourceID="SqlDataSource1" />
<asp:SqlDataSource
id="SqlDataSource1"
runat="server"
ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
SelectCommand="sp_lastnames">
</asp:SqlDataSource>
<!--
The sp_lastnames stored procedure is
CREATE PROCEDURE sp_lastnames AS
SELECT LastName FROM Employees
GO
-->
</FORM>
</BODY>
</HTML>
Windows 98, Windows 2000 SP4, Windows Server 2003, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP SP2, Windows XP Starter Edition
The .NET Framework does not support all versions of every platform. For a list of the supported versions, see System Requirements.
Security Note