• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

0
loserbrewer
Asked:
loserbrewer
  • 3
  • 2
1 Solution
 
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
 
loserbrewerAuthor Commented:
thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now