Filter GridView based on Drop-down list but only after it fully popula

[mjc225]

I am using a GridView on a page that will need to display data based on either a URL passed in with a QueryString OR fields passed from a form. This may return 50-500 records or so based on the state etc... If this is too many, (and only if this is too many) the user will be able to filter based on the occupation. I can get it to filter on query string AND state at the same time but not or sadly. I want it to default to all occupations after pulling from the DB based on state, (I tried setting the default value for occupation to -1) and then, the user can use the dropdown control to filter it more.




<asp:dropdownlist id="ddlCategories" runat="server" appenddatabounditems="True" autopostback="True"
datasourceid="sdsCategories" datatextfield="Occupation" datavaluefield="Occupation">
<asp:listitem text="All Categories" value="-1">
</asp:listitem>
</asp:dropdownlist>
<asp:sqldatasource id="sdsCategories" runat="server" connectionstring="<%$ ConnectionStrings:eventsConnectionString1 %>"
selectcommand="SELECT [Occupation] FROM [Events]" ProviderName="<%$ ConnectionStrings:eventsConnectionString1.ProviderName %>">
</asp:sqldatasource>


<asp:gridview id="gvProducts" runat="server" datasourceid="sdsProducts" style="margin-top: 12px;" AllowSorting="True" AutoGenerateColumns="False">
<columns>
<asp:BoundField DataField="EventTitle" HeaderText="EventTitle" SortExpression="EventTitle" />
<asp:BoundField DataField="EventEndDate" HeaderText="EventEndDate" SortExpression="EventEndDate" DataFormatString="{0:MM/dd/yyyy}" />
<asp:BoundField DataField="EventStartDate" HeaderText="EventStartDate" SortExpression="EventStartDate" DataFormatString="{0:MM/dd/yyyy}" />
<asp:BoundField DataField="EventCity" HeaderText="EventCity" SortExpression="EventCity" />
<asp:BoundField DataField="EventState" HeaderText="EventState" SortExpression="EventState" />
<asp:BoundField DataField="Occupation" HeaderText="Occupation" SortExpression="Occupation" />
</columns>
</asp:gridview>
<asp:sqldatasource id="sdsProducts" runat="server" connectionstring="<%$ ConnectionStrings:eventsConnectionString2 %>"
selectcommand="SELECT [EventTitle], [EventEndDate], [EventStartDate], [EventCity], [EventState], [Occupation] FROM [Events] WHERE ([EventState] = ?)" ProviderName="<%$ ConnectionStrings:eventsConnectionString2.ProviderName %>">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="PA" Name="EventState" QueryStringField="ID"
Type="String" />
</SelectParameters>
</asp:sqldatasource>

 

[jbenson001]

The problem is you are using the datasource controls. These are only good for very simple SQL manipulation, once you need to do anything complex, they are difficult to use. Not to mention that you cannot debug them if you have a problem.

I suggest using a data aplication layer such as MS Data Access Application Block:

http://www.microsoft.com/downloads/...0a-9877-4a7b-88ec-0426b48df275&displaylang=en

Also, you should be using stored procedures with parameters to manipulate(select, update, etc.) the data you want.