locked
Using IF query for Access Database in Expression Web RRS feed

  • Question

  • User-2093306650 posted

    I have an access DB in expression web 4 and am having trouble getting the results.

    The fields are Accomodation name; address; website; region; region2; accommodation type (and a few others)

    What I want to do is to be able to have a drop down list for the region (Cornwall, Devon, Cumbria, Midlands etc); then if Cornwall or Devon are chosen region2 will need to list either North or South, but if it is any other county, it will be blank; it will then need to list in the accommodation type either B&B, hotel, self catering etc, depending on what has been chosen for the region (and region2 if appropriate).  I then want to display it in a gridview underneath that!  The code I have so far is:

    <form id="form1" runat="server">
        <asp:DropDownList id="AccessRegion" runat="server" AutoPostBack="True" DataSourceID="Region" DataTextField="Region" DataValueField="Region">
        </asp:DropDownList>
        <asp:AccessDataSource ID="Region" runat="server" DataFile="webdata.mdb" SelectCommand="SELECT DISTINCT [Region] FROM [web data]">
        </asp:AccessDataSource>
        <br />
        <asp:DropDownList id="AccessREgion2" runat="server" AutoPostBack="True" DataSourceID="Region2" DataTextField="Region2_BEF7E8022D1C44E5B9D2F341583359D6_Value" DataValueField="Region2_BEF7E8022D1C44E5B9D2F341583359D6_Value">
        </asp:DropDownList>
        <asp:AccessDataSource ID="Region2" runat="server" DataFile="webdata.mdb" SelectCommand="SELECT DISTINCT [Region2_BEF7E8022D1C44E5B9D2F341583359D6_Value] FROM [web data] WHERE ([Region] = ?)">
            <SelectParameters>
                <asp:ControlParameter ControlID="AccessRegion" Name="Region" PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:AccessDataSource>
        <br />
        <asp:DropDownList id="AccessAccom" runat="server" AutoPostBack="True" DataSourceID="Accom" DataTextField="Accommodation_Type_021CB65B7AA64312BCE1AF1114BFB229_Value" DataValueField="Accommodation_Type_021CB65B7AA64312BCE1AF1114BFB229_Value">
        </asp:DropDownList>
        <asp:AccessDataSource ID="Accom" runat="server" DataFile="webdata.mdb" SelectCommand="SELECT DISTINCT [Accommodation Type_021CB65B7AA64312BCE1AF1114BFB229_Value] AS Accommodation_Type_021CB65B7AA64312BCE1AF1114BFB229_Value FROM [web data] WHERE (([Region] = ?))">
            <SelectParameters>
                <asp:ControlParameter ControlID="AccessRegion" Name="Region" PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:AccessDataSource>
        <br />
        <asp:GridView id="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="Results">
            <Columns>
                <asp:BoundField DataField="Accommodation Name" HeaderText="Accommodation Name" SortExpression="Accommodation Name">
                </asp:BoundField>
                <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address">
                </asp:BoundField>
                <asp:BoundField DataField="Web Address" HeaderText="Web Address" SortExpression="Web Address">
                </asp:BoundField>
                <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region">
                </asp:BoundField>
                <asp:BoundField DataField="Region2_BEF7E8022D1C44E5B9D2F341583359D6_Value" HeaderText="Region2_BEF7E8022D1C44E5B9D2F341583359D6_Value" SortExpression="Region2_BEF7E8022D1C44E5B9D2F341583359D6_Value">
                </asp:BoundField>
                <asp:BoundField DataField="Dog Type &amp; Number" HeaderText="Dog Type &amp; Number" SortExpression="Dog Type &amp; Number">
                </asp:BoundField>
                <asp:CheckBoxField DataField="Enclosed" HeaderText="Enclosed" SortExpression="Enclosed">
                </asp:CheckBoxField>
                <asp:CheckBoxField DataField="Walks" HeaderText="Walks" SortExpression="Walks">
                </asp:CheckBoxField>
                <asp:CheckBoxField DataField="Freebies" HeaderText="Freebies" SortExpression="Freebies">
                </asp:CheckBoxField>
                <asp:BoundField DataField="Free" HeaderText="Free" SortExpression="Free">
                </asp:BoundField>
                <asp:BoundField DataField="Accommodation Type_021CB65B7AA64312BCE1AF1114BFB229_Value" HeaderText="Accommodation Type_021CB65B7AA64312BCE1AF1114BFB229_Value" SortExpression="Accommodation Type_021CB65B7AA64312BCE1AF1114BFB229_Value">
                </asp:BoundField>
            </Columns>
        </asp:GridView>
        <asp:AccessDataSource ID="Results" runat="server" DataFile="webdata.mdb" SelectCommand="SELECT * FROM [web data] WHERE (([Region] = ?) AND ([Accommodation Type_021CB65B7AA64312BCE1AF1114BFB229_Value] = ?))">
            <SelectParameters>
                <asp:ControlParameter ControlID="AccessRegion" Name="Region" PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="AccessAccom" Name="Accommodation_Type_021CB65B7AA64312BCE1AF1114BFB229_Value" PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:AccessDataSource>
        <br />
    </form>

    I am a novice at this and I just want something simple to start off with.  Any help would be very much appreciated, but please keep it very basic!  I have faffed round with this for most of the day, so there might be dodgy code in there :-S  I have asked on the EW forum, but they pointed me to here.  I have a very basic search at http://www.dogsinvited.co.uk/search.htm

    Thanks for your help!

     

    Debbie

     

    Tuesday, April 19, 2011 4:52 PM

Answers

  • User3866881 posted

    Hi:)

    From what you say, it seems that you want to do a Cascading Dropdownlist, isn't that right?

    If so. I think you should make AutoPostBack = True to the Dropdownlist, and then in the SelectIndexChanged event please use SqlDataAdapter with proper Sql statement to fill DataTable, and bind the DataTable to the Dropdownlist, and then fetch several values from these dropdownlists and use another SqlDataAdapter to fill datatable for GridView...

    This is very flexible way:)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 21, 2011 10:05 PM