Mega Code Archive
SqlDataSource with dynamic parameter
<![CDATA[
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CustomParameters.aspx.cs" Inherits="CustomParameters" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Custom Parameters</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="sourceCustomers"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT CustomerID, ContactName FROM Customers"/>
<asp:DropDownList ID="lstCustomers"
runat="server"
DataSourceID="sourceCustomers"
DataTextField="ContactName"
DataValueField="CustomerID"
AutoPostBack="True">
</asp:DropDownList>
<asp:SqlDataSource ID="sourceOrders"
runat="server"
ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:Northwind %>"
SelectCommand="SELECT OrderID,OrderDate,ShippedDate FROM Orders WHERE CustomerID=@CustomerID AND OrderDate>=@EarliestOrderDate"
OnSelecting="sourceOrders_Selecting">
<SelectParameters>
<asp:ControlParameter Name="CustomerID" ControlID="lstCustomers" PropertyName="SelectedValue" />
<asp:Parameter Name="EarliestOrderDate" DefaultValue="1900/01/01" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="gridOrders"
runat="server"
DataSourceID="sourceOrders"
AutoGenerateColumns="False"
DataKeyNames="OrderID" >
<Columns>
<asp:boundfield DataField="OrderID" HeaderText="OrderID"
InsertVisible="False"
ReadOnly="True"
SortExpression="OrderID"></asp:boundfield>
<asp:boundfield DataField="OrderDate" HeaderText="OrderDate" DataFormatString="{0:d}"
SortExpression="OrderDate"></asp:boundfield>
<asp:boundfield DataField="ShippedDate" DataFormatString="{0:d}"
HeaderText="ShippedDate"
SortExpression="ShippedDate"></asp:boundfield>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
File: CustomParameters.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class CustomParameters : System.Web.UI.Page
{
protected void sourceOrders_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters["@EarliestOrderDate"].Value = DateTime.Today.AddYears(-10);
}
}
File: Web.config
<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="Northwind" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI"/>
</connectionStrings>
</configuration>
]]>