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

ASP.NET Multltiple filters using dropdown lists

Hi Experts, I am trying to put up a simple aspx page that would allow me to filter by several criterias according to what is selected on my dropdown lists. I am using a Access Data Source and the Data List control but I dont know exactly how would this work to combine several filters in my form for examle I want to take the selected values for each field and use that as a filter when I click on Search or show all if I click Show all

Here is some of my code:

Thanks in advance!!
<td style="width: 38%; text-align: left; height: 20px;">
            <span id="specialtyLabel" 
            style="font-family:Times New Roman;font-size:10pt; text-align: left;">Specialty:</span>
            <asp:DropDownList ID="specialityDropDown" runat="server" AutoPostBack="True" 
                DataSourceID="AccessDataSource1" DataTextField="Specialty" 
                DataValueField="Specialty">
                              
            </asp:DropDownList>
        </td>
        <td style="width: 30%; height: 20px; text-align: left;">
            <asp:Button ID="searchButton" runat="server" Text="Search" />
        </td>
    </tr>
    <tr>
        <td style="width: 38%; text-align: left; height: 21px;">
            <span id="zipCodeLabel" style="font-family:Times New Roman;font-size:10pt;">Zip Code:</span>
            <asp:TextBox ID="txtZipCode" runat="server"></asp:TextBox>
&nbsp;
            <asp:DropDownList ID="distanceDropDown"  runat="server" AutoPostBack="True">
                <asp:ListItem Selected="True" Value ="1000">Find Closest</asp:ListItem>
                <asp:ListItem Value ="10">Within 10 Miles</asp:ListItem>
                <asp:ListItem Value ="25">Within 25 Miles</asp:ListItem>
                <asp:ListItem Value ="50">Within 50 Miles</asp:ListItem>
                <asp:ListItem Value ="100">Within 100 Miles</asp:ListItem>
            </asp:DropDownList>
        </td>
        <td style="width: 30%; height: 21px; text-align: left;">
            <asp:Button ID="showallButton" runat="server" Text="Show All" />
            <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
                DataFile="~/App_Data/Providers.accdb" 
                
                SelectCommand="SELECT DISTINCT [Specialty] FROM [tbl_Providers] ORDER BY [Specialty] ASC">
            </asp:AccessDataSource>
        </td>
    </tr>
</table>
    <br />
    <asp:DataList ID="DataList1" runat="server" CellPadding="4" 
        DataSourceID="AccessDataSource2" ForeColor="#333333">
        <AlternatingItemStyle BackColor="White" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <ItemStyle BackColor="#EFF3FB" />
       
        <ItemTemplate>
             <asp:Label ID="Last_NameLabel" runat="server" Text='<%# Eval("Last_Name") %>' />
           , <asp:Label ID="First_NameLabel" runat="server" 
                Text='<%# Eval("First_Name") %>' />
            <br />
            <asp:Label ID="SpecialtyLabel" runat="server" Text='<%# Eval("Specialty") %>' />
            ,<asp:Label ID="Specialty_2Label" runat="server" 
             Text='<%# Eval("[Specialty 2]") %>' />
            <br />
            <asp:Label ID="AddressLabel" runat="server" Text='<%# Eval("Address") %>' />
            <br />
            <asp:Label ID="Address2Label" runat="server" Text='<%# Eval("Address2") %>' />
            <br />
            <asp:Label ID="CityLabel" runat="server" Text='<%# Eval("City") %>' />
             ,<asp:Label ID="StateLabel" runat="server" Text='<%# Eval("State") %>' />
           ,   
            <asp:Label ID="ZipLabel" runat="server" Text='<%# Eval("Zip") %>' />
            <br />
            Phone:
            <asp:Label ID="PhoneLabel" runat="server" Text='<%# Eval("Phone") %>' />
            <br />
            Fax:
            <asp:Label ID="FaxLabel" runat="server" Text='<%# Eval("Fax") %>' />
            <br />
<br />
        </ItemTemplate>

Open in new window

0
TonyReba
Asked:
TonyReba
  • 3
  • 2
1 Solution
 
guru_samiCommented:
1: upon your search button click  databind your Datalist i.e. call DataList1.DataBind()
2: Set select parameters for your AccessDataSource2
3: Add onSelecting event handler for AD2
4: Set the values for those parameter equal to the DDL selections e.g.

protected void AccessDataSource2_Selecting1(object sender, SqlDataSourceSelectingEventArgs e)
        {
              e.Command.Parameters.Add(new SqlParameter("@ZipCode", txtZipCode.Text));
            e.Command.Parameters.Add(new SqlParameter("@Distance", distanceDropDown.SelectedValue.ToString()));          
           
        }

Note: ZipCode and Distance are parameters in your Select query
0
 
TonyRebaAuthor Commented:
Can you go over a sample code behind  in which this is done
0
 
guru_samiCommented:
e.g. here is sample:
.aspx

<asp:TextBox ID="txtStateID" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Search" />

<asp:AccessDataSource ID="AccessDataSource2" runat="server"
        DataFile="~/App_Data/mydata.mdb"
        SelectCommand="SELECT [Name] FROM [REPS_SALES] WHERE ([StateID] = @StateID)"
         onselecting="AccessDataSource2_Selecting">
        <SelectParameters>
            <asp:Parameter Name="StateID" Type="Int32" />
        </SelectParameters>
    </asp:AccessDataSource>

protected void Button1_Click(object sender, EventArgs e)
    {
        DataList1.DataBind()
    }

protected void AccessDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        if (Page.IsPostBack)
        {
            e.Command.Parameters["StateID"].Value = Convert.ToInt32(txtStateID.Text);
           // if you have more control and parameters you set them here
        }
    }

Hope this helps.
0
 
TonyRebaAuthor Commented:
I dont quite understand it. I am trying to make several filters , in my search being the first one by Specilaity. This is working so far , but also want to have other criterias.  Can you please help? this is my code . By the way I am doing this in VB.Net/
<asp:DropDownList ID="specialityDropDown" runat="server" AutoPostBack="True" 
                DataSourceID="AccessDataSource1" DataTextField="Specialty" 
                DataValueField="Specialty">
                              
            </asp:DropDownList>
        </td>
        <td style="width: 30%; height: 20px; text-align: left;">
            <asp:Button ID="searchButton" runat="server" Text="Search" />
        </td>
    </tr>
    <tr>
        <td style="width: 38%; text-align: left; height: 21px;">
            <span id="zipCodeLabel" style="font-family:Times New Roman;font-size:10pt;">Zip Code:</span>
            <asp:TextBox ID="txtZipCode" runat="server"></asp:TextBox>
&nbsp;
            <asp:DropDownList ID="distanceDropDown"  runat="server" AutoPostBack="True">
                <asp:ListItem Selected="True" Value ="1000">Find Closest</asp:ListItem>
                <asp:ListItem Value ="10">Within 10 Miles</asp:ListItem>
                <asp:ListItem Value ="25">Within 25 Miles</asp:ListItem>
                <asp:ListItem Value ="50">Within 50 Miles</asp:ListItem>
                <asp:ListItem Value ="100">Within 100 Miles</asp:ListItem>
            </asp:DropDownList>
        </td>
        <td style="width: 30%; height: 21px; text-align: left;">
            <asp:Button ID="showallButton" runat="server" Text="Show All" />
            <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
                DataFile="~/App_Data/Providers.accdb" 
                
                SelectCommand="SELECT DISTINCT [Specialty] FROM [tbl_Providers] ORDER BY [Specialty] ASC">
            </asp:AccessDataSource>
        </td>
    </tr>
</table>
    <br />
    <asp:DataList ID="DataList1" runat="server" CellPadding="4" 
        DataSourceID="AccessDataSource2" ForeColor="#333333">
        <AlternatingItemStyle BackColor="White" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <ItemStyle BackColor="#EFF3FB" />
       
        <ItemTemplate>
             <asp:Label ID="Last_NameLabel" runat="server" Text='<%# Eval("Last_Name") %>' />
           , <asp:Label ID="First_NameLabel" runat="server" 
                Text='<%# Eval("First_Name") %>' />
            <br />
            <asp:Label ID="SpecialtyLabel" runat="server" Text='<%# Eval("Specialty") %>' />
            ,<asp:Label ID="Specialty_2Label" runat="server" 
             Text='<%# Eval("[Specialty 2]") %>' />
            <br />
            <asp:Label ID="AddressLabel" runat="server" Text='<%# Eval("Address") %>' />
            <br />
            <asp:Label ID="Address2Label" runat="server" Text='<%# Eval("Address2") %>' />
            <br />
            <asp:Label ID="CityLabel" runat="server" Text='<%# Eval("City") %>' />
             ,<asp:Label ID="StateLabel" runat="server" Text='<%# Eval("State") %>' />
           ,   
            <asp:Label ID="ZipLabel" runat="server" Text='<%# Eval("Zip") %>' />
            <br />
            Phone:
            <asp:Label ID="PhoneLabel" runat="server" Text='<%# Eval("Phone") %>' />
            <br />
            Fax:
            <asp:Label ID="FaxLabel" runat="server" Text='<%# Eval("Fax") %>' />
            <br />
<br />
        </ItemTemplate>
        <SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    </asp:DataList>
    <asp:AccessDataSource ID="AccessDataSource2" runat="server" 
        DataFile="~/App_Data/Providers.accdb" 
        SelectCommand="SELECT [First Name] AS First_Name, [Last Name] AS Last_Name, [Specialty], [Specialty 2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax] FROM [tbl_Providers] WHERE ([Specialty] = ?)">
        <SelectParameters>
            <asp:ControlParameter ControlID="specialityDropDown" Name="Specialty" 
                PropertyName="SelectedValue" Type="String" />
        </SelectParameters>
    </asp:AccessDataSource>

Open in new window

0
 
TonyRebaAuthor Commented:
thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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