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

loserbrewerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

spprivateCommented:
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
0
loserbrewerAuthor Commented:
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
0
spprivateCommented:
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

0
spprivateCommented:
Sorry
one last stmt in the previous code

SQL = SQL + Whereclause
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
loserbrewerAuthor Commented:
thanks for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.