In this article I will explain how to conditionally populate SqlDataSource results based on value from QueryString Parameter in ASP.Net
Database
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below
ConnectionString in Web.Config
Below is the connection string defined in the Web.Config file, you can replace it with that of your database.
<connectionStrings>
<add name="constr" connectionString="Data Source=.\SQL2005;Initial Catalog=Northwind;User id = sa;password=123"/>
</connectionStrings>
HTML Markup
The HTML Markup consists of an ASP.Net GridView control which I’ll populate using the SqlDataSource control. In this example I am depicting how to pass QueryString Value as Parameter to SqlDataSource hence I have placed a TextBox and a Button that will be used to pass values as QueryString
<asp:TextBox ID="txtCustomerId" runat="server"></asp:TextBox>
<asp:Button ID="btnRedirect" runat="server" Text="Redirect" OnClick="btnRedirect_Click" />
<br /><br />
<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
runat="server" AutoGenerateColumns="false" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Customer Id" ItemStyle-Width="150px" />
<asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>"
SelectCommand="SELECT * FROM Customers WHERE CustomerId=@CustomerId OR @CustomerId IS NULL" CancelSelectOnNullParameter="false">
<SelectParameters>
<asp:QueryStringParameter Name="CustomerId" DbType = "String" Direction = "Input" QueryStringField="Id" DefaultValue="" ConvertEmptyStringToNull="True" />
</SelectParameters>
</asp:SqlDataSource>
If you observe the SQL Query you will notice that I am matching the CustomerId column with the @CustomerId QueryString Parameter and if that’s NULL then all records are fetched.
For this purpose you need to set the property CancelSelectOnNullParameter to false, as it tells the SqlDataSource that when the SQL Parameter is NULL then too the SQL Query will be executed.
SqlDataSource QueryStringParameter
The SqlDataSource QueryStringParameter has some properties that are important
Name - Name of the Parameter you want to use in the SQL Query, here I have named it CustomerId and in Query it is used as @CustomerId.
DbType –The Data Type of the SQL Parameter, here it is specified as String.
Direction(Optional) – Specified whether the Parameter is INPUT or OUTPUT. Default is INPUT.
QueryStringField – Name of the QueryString Parameter used in the URL.
DefaultValue (Optional) – Here you can specify the Default Value of the Parameter when the QueryString Parameter is missing or not supplied.
ConvertEmptyStringToNull (Optional) – As the Name suggests this will set the Parameter value to NULL when it is empty or blank.
Passing TextBox Value as Query String Parameter to the Page
Below is the code to pass the Value of the TextBox as QueryString Parameter on Button Click event.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
txtCustomerId.Text = Request.QueryString["Id"];
}
}
protected void btnRedirect_Click(object sender, EventArgs e)
{
Response.Redirect("~/Default.aspx?Id=" + txtCustomerId.Text.Trim());
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
txtCustomerId.Text = Request.QueryString("Id")
End If
End Sub
Protected Sub btnRedirect_Click(sender As Object, e As EventArgs)
Response.Redirect("~/Default.aspx?Id=" + txtCustomerId.Text.Trim())
End Sub
![ASP.Net SqlDataSource pass value to SelectParameter using QueryString Parameter Example]()
![ASP.Net SqlDataSource pass value to SelectParameter using QueryString Parameter Example]()
Demo
Downloads
