need help filtering a datasource (newbie)

[raindogs]

I’m taking my first crack at ASP.NET and I’ve run into a problem with my datasource that seems like it should be very simple. Using the <controlparameter> tag to filter a datasource is working great, but I’d like to use it to define the field to be filtered as well as the value. To clarify, here’s what I have now:

<asp:SqlDataSource ID="test" runat="server" ConnectionString="<My String>"
SelectCommand="SELECT * FROM [occupants] WHERE (LastName = @LastName)">
<SelectParameters>
<asp:ControlParameter ControlID="searchText" Name="LastName" PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>

In this case, I have a “searchText” field further up the page that the user types into to enter a last name to search for. What I’d like to do is take this a step further with a drop down-box containg search fields - “First Name”, “Last Name”, “User ID”, etc. - that the user could use to refine the search. It seems like I should be able to just update the datasoruce like this:

<asp:SqlDataSource ID="test" runat="server" ConnectionString="<My String>"
SelectCommand="SELECT * FROM [occupants] WHERE (@SearchFor = @SearchString)">
<SelectParameters>
<asp:ControlParameter ControlID="searchText" Name="SearchString" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="searchField" Name="SearchFor" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Sadly, this does not work. I’ve checked through a fair amount of documentation on this, but I just can’t seem to find a way through. Can anyone point me in the right direction?

Thanks,
Alex

 

[jbenson001]

It doesn't work because you are comparing 2 variables, not a variable to a column. My suggestion is to use a Stored Procedure and use parameters.

Jim

 

[raindogs]

Thanks for the tip, that clears up a lot. Unfortunately, I’m still having a bit of trouble. I set up the following stored procedure to query my “occupants” table:

[dbo].[occ_select] (@p1 char(20), @p2 char(20)) as
execute('SELECT * from occupants where ' + @p1 + ' = ''' + @p2 + '''')

I loaded it into my “test” datasource and, when I ran a test with a couple of initial values, it worked great. However, after I submit the form in the page, it always comes back blank. Not the “EmptyDataTemplate” display, just a blank spot on the page. Any ideas on why this might be? The applicable code is below, thanks again for the help.

**********
Grid View
**********
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="test">
<EmptyDataTemplate>
no data found
</EmptyDataTemplate>
</asp:GridView>


***********
Data Source
***********

<asp:SqlDataSource ID="test" runat="server" ConnectionString="<%$ ConnectionStrings:callcenter_MS_1604ConnectionString %>"
SelectCommand="occ_select" SelectCommandType="StoredProcedure" OnSelected="test_Selected">
<SelectParameters>
<asp:ControlParameter ControlID="searchField" Name="p1" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="searchText" Name="p2" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

 

[jbenson001]

I would assume you are not getting any rows returned from the query. Test the query with parameters in query analyzer and see if you get any rows.