asked on

filtering a data set with multiple selections

I am trying to fill a dataset with a users choice of 3 drop down lists. When the page is run the lastr drop down menu is the only criteria used to filter the data. The user should be able to filter by one or all drop down lists.

I am sure the problem is in the Default.aspx.vb page (at the bottom of the code snippet page).

I have included the code for both the Default.aspx and Default.aspx.vb pages.

I would appriciate any help that can be given.

John

Default.aspx
 
<%@ Page Language="VB" MasterPageFile="~/AppMaster.master" Title="Home Page" %>
 
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="mainCopy">
 
<asp:DropDownList ID="DDLMaiCode" runat="server" AppendDataBoundItems="True" DataSourceID="SqlDataSource2" DataTextField="column1" DataValueField="column1" AutoPostBack="True">
      </asp:DropDownList>
    
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:HHSCListConnectionStringMailCode %>" 
ProviderName="<%$ ConnectionStrings:HHSCListConnectionStringMailCode.ProviderName %>" SelectCommand="SELECT DISTINCT [$mailStop] AS column1 FROM [Sheet1]">
</asp:SqlDataSource>
    
    
<asp:DropDownList ID="DDLCity" runat="server" AppendDataBoundItems="True" DataSourceID="SqlDataSource3" DataTextField="column1" DataValueField="column1" AutoPostBack="True">
    	</asp:DropDownList>
    
    	<asp:SqlDataSource ID="SqlDataSource3" runat="server" 
ConnectionString="<%$ ConnectionStrings:HHSCListConnectionStringCity %>" 
ProviderName="<%$ ConnectionStrings:HHSCListConnectionStringCity.ProviderName %>" 
SelectCommand="SELECT DISTINCT [$physicalDeliveryOfficeName] AS column1 FROM [Sheet1]">
</asp:SqlDataSource>
    
 
<asp:DropDownList ID="DDLAgency" runat="server" AppendDataBoundItems="True" 
DataSourceID="SqlDataSource4" DataTextField="column1" DataValueField="column1" AutoPostBack="True">
    	</asp:DropDownList>
    
<asp:SqlDataSource ID="SqlDataSource4" runat="server" 
ConnectionString="<%$ ConnectionStrings:HHSCListConnectionStringAgency %>" 
ProviderName="<%$ ConnectionStrings:HHSCListConnectionStringAgency.ProviderName %>" 
SelectCommand="SELECT DISTINCT [$company] AS column1 FROM [Sheet1]">
</asp:SqlDataSource>
      
 
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource5">
    	</asp:GridView>
 
    	<asp:SqlDataSource ID="SqlDataSource5" runat="server" 
ConnectionString="<%$ ConnectionStrings:HHSCListConnectionStringFilter %>" 
ProviderName="<%$ ConnectionStrings:HHSCListConnectionStringFilter.ProviderName %>" 
      SelectCommand="SELECT * FROM [Sheet1] WHERE ([$company] = ?)">
     
<SelectParameters>
            <asp:ControlParameter ControlID="DDLAgency" Name="column1" 
                PropertyName="SelectedValue" Type="String" />
       </SelectParameters>
    </asp:SqlDataSource>
</asp:Content>
 
 
 
 
Default.aspx.vb
 
Partial Class _Default
    Inherits System.Web.UI.Page
 
    Protected Sub Page_load(ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            DDLMaiCode.DatasourceId = "SqlDataSource2"
            DDLMaiCode.Databind()
        End If
 
    Protected Sub Page_load(ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            DDLCity.DatasourceId = "SqlDataSource3"
            DDLCity.Databind()
        End If
 
    Protected Sub Page_load(ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ddlAgency.DatasourceId = "SqlDataSource4"
            ddlAgency.Databind()
        End If
  End Sub
End Class

Open in new window

spprivate

Make sure you have enableviewstate set to true so that your selected values are retained.
Also in the code behind where you populate the dropdowns,put a check for if not page post back,other wise your drpdowns will be loaded fresh every tme page is poste back

loserbrewer

ASKER

All drop down lists have enableviewstate set to true

and I have the following in the code behind for each of the drop down lists:

If Not IsPostBack Then
DDLMaiCode.DatasourceId = "SqlDataSource2"
DDLMaiCode.Databind()
End If

spprivate

Why do we have three page loads here.Change that to
Also for populating the grid with one or 3 user choices from dropdown,
use a dynamic sql where clause.
That is buiild a sql statement in code behind will be

SQL = "Select * from your tab"
Dim whereclause as string=""
if ddl1.selectedvalue.tostring <>"Select" then
whereclause=whereclause+"where val1="+ddl1.selectedvalue.tostring()
end if
if ddl2.selectedvalue.tostring <>"Select"
if whereclause.lenght > 0 then
whereclause = whereclause + "and val2 = " ddl2.selectedvalue.tostring
else
whereclause=whereclause+"where val2="+ddl2.selectedvalue.tostring()
end if
end if.

Siimillarly you have to buld the dynamic sql ,execute and assign that ds to the grid.

Protected Sub Page_load(ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            DDLMaiCode.DatasourceId = "SqlDataSource2"
            DDLMaiCode.Databind()
            DDLCity.DatasourceId = "SqlDataSource3"
            DDLCity.Databind()
           ddlAgency.DatasourceId = "SqlDataSource4"
            ddlAgency.Databind()
 
 
        End If

Open in new window

ASKER CERTIFIED SOLUTION

spprivate

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

loserbrewer

ASKER

thanks for the help