Request.QueryString in Gridview SQLDataSource

.NET
1

Hi, I’m writing a web app for the company I work for in asp.net 2.0. I’ve got a GridView and I’ve defined a SqlDataSource to populate it. My problem is I need to use the querystring value to determine which column of data I’m going to display.

If I hardcode the value into the Select command it works fine, but if I try to use Request.QueryString[“id”] in the select command I get a runtime error. I’d say its just my lack of knowledge for how to code the querystring into the command.
(note: I am using the Northwind database as a testing platform right now)

Here is my code. The area I’m having problems with is in bold. I know its wrong now and I’ve tried all the variations I know to try

<asp:SqlDataSource ID=“SqlDataSource1” runat=“server” ConnectionString=“<%$ ConnectionStrings:NorthwindConnectionString %>”
SelectCommand=“SELECT [CustomerID], [CompanyName], [City] FROM [Customers] WHERE [City]='<% Request.QueryString[“cid”] %>'”></asp:SqlDataSource>
<asp:GridView ID=“GridView1” runat=“server” AutoGenerateColumns=“False” DataKeyNames=“CustomerID”
DataSourceID=“SqlDataSource1” Width=“794px”>
<Columns>
<asp:BoundField DataField=“CustomerID” HeaderText=“CustomerID” ReadOnly=“True” SortExpression=“CustomerID” />
<asp:BoundField DataField=“CompanyName” HeaderText=“CompanyName” SortExpression=“CompanyName” />
<asp:BoundField DataField=“City” HeaderText=“City” SortExpression=“City” />
</Columns>
</asp:GridView>

Any help would be appreciated.

2

You have to add a parameter to your SqlDataSource
here is an example… made with the Visual Studio wizard.
[color=#0000ff][size=1]


<[/size][/color][color=#800000]asp[/color][color=#0000ff]:[/color][color=#800000]SqlDataSource[/color][color=#ff0000]ID[/color][color=#0000ff]="SqlDataSource1"[/color][color=#ff0000]runat[/color][color=#0000ff]="server"[/color][color=#ff0000]ConnectionString[/color][color=#0000ff]="..." [/color][color=#ff0000]ProviderName[/color][color=#0000ff]="System.Data.SqlClient"[/color][color=#ff0000]SelectCommand[/color][color=#0000ff]="SELECT [UserId], [LastUpdatedDate] FROM [vw_aspnet_Profiles] WHERE ([UserId] = @UserId)">[/color]
[color=#0000ff]<[/color][color=#800000]SelectParameters[/color][color=#0000ff]>[/color]
[color=#0000ff]<[/color][color=#800000]asp[/color][color=#0000ff]:[/color][color=#800000]QueryStringParameter[/color][color=#ff0000]DefaultValue[/color][color=#0000ff]="0"[/color][color=#ff0000]Name[/color][color=#0000ff]="UserId"[/color][color=#ff0000]QueryStringField[/color][color=#0000ff]="Id"[/color][color=#ff0000]Type[/color][color=#0000ff]="Object"[/color][color=#0000ff]/>[/color]
[color=#0000ff]</[/color][color=#800000]SelectParameters[/color][color=#0000ff]>[/color]
[color=#0000ff]</[/color][color=#800000]asp[/color][color=#0000ff]:[/color][color=#800000]SqlDataSource[/color][color=#0000ff]>[/color]
[size=1][color=#0000ff]

[/color][/size]

3

Thank you for the reply.

This is what I was looking for.

In the example you show is the correct format page.aspx?id=(UserID goes Here)?

Also where in visual studio do you add this parameter to the SqlDatasource?

thanks again! :slight_smile:

4

Hi there,

I made that Sqldatasource control by “Drag-n-Drop”.
I droped a SqlDataSource control from the ToolBox into the Designer.

Then if you move your mouse over the control an “arrow” will appear on the top-right corner of the control. Select the control if its not already selected and click the arrow.
An “SqlDatasource Task” menu pops up. Click “Configure Data Source”.

Then just follow the setps of the wizard.

You should set your connection string in the web.conifg filefor later reuse. (2nd setp in the wizard)
In the “Configure the Select Stament” step add a WHERE clause to the sql query.

5

pufa,

Thanks for your help. I was able to get the querystring working.

I had seen the “WHERE” function in the SqlDataSource but wasn’t sure about using the parameters. Your explination helped out allot!

Just wanted to say thanks again!