Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Search Page

    Hi All

    I am attempting to create a web page using Visual Studio to search the "products" table in my local SQL Database. I have used a Gridview control to display the results from four drop down menus which are price, brand, colour and screen size.

    Ive managed to make the brand and colour menus to work, affecting the results of the Gridview, but I cannot make the others work because they involve a number range e.g. 200-500, 500-1000 etc... I am not sure what code I will need to make this work. Here is what ive done so far,

    Code:
    <%@ Page MasterPageFile="~/MasterPage.Master" Language="vb" AutoEventWireup="false" CodeBehind="products.aspx.vb" Inherits="webproject.products" %>
    <asp:Content ID="Content1" runat="server" 
        contentplaceholderid="ContentPlaceHolder1">
        <p>
            <strong __designer:mapid="29">Search Products</strong><br 
                __designer:mapid="26" />
            <br __designer:mapid="28" />
            <asp:Label ID="Label1" runat="server" Text="Price: "></asp:Label>
            <asp:DropDownList ID="PriceDropDown" runat="server" AutoPostBack="True">
                <asp:ListItem Value="">Under £200</asp:ListItem>
                <asp:ListItem Value="">£200-500</asp:ListItem>
                <asp:ListItem Value="500-1000">£500-1000</asp:ListItem>
                <asp:ListItem Value="1000-1500">£1000-1500</asp:ListItem>
                <asp:ListItem Value="1500-100000">Over £1500 </asp:ListItem>
            </asp:DropDownList>
            <br __designer:mapid="2a" />
            <br __designer:mapid="2b" />
            <asp:Label ID="Label2" runat="server" Text="Brand: "></asp:Label>
            <asp:DropDownList ID="BrandDropDown" runat="server" 
                Width="80px" AutoPostBack="True">
                <asp:ListItem Value="Samsung">Samsung</asp:ListItem>
                <asp:ListItem Value="LG">LG</asp:ListItem>
            </asp:DropDownList>
            <br __designer:mapid="30" />
            <br __designer:mapid="31" />
            <asp:Label ID="Label3" runat="server" Text="Screen Size: "></asp:Label>
            <asp:DropDownList ID="ScreenDropDown" runat="server" 
                Width="80px" AutoPostBack="True">
                <asp:ListItem Value="ScreenSize &gt;= 32 &lt;=39">From 32&quot; to 39&quot;</asp:ListItem>
                <asp:ListItem Value="ScreenSize &gt;=40 &lt;=45">From 40&quot; to 45&quot;</asp:ListItem>
                <asp:ListItem Value="ScreenSize &gt;=46 &lt;=55">From 46&quot; to 55&quot;</asp:ListItem>
                <asp:ListItem Value="ScreenSize &gt;=56">56&quot; and More</asp:ListItem>
            </asp:DropDownList>
            <br __designer:mapid="36" />
            <br __designer:mapid="37" />
            <asp:Label ID="Label4" runat="server" Text="Colour: "></asp:Label>
            <asp:DropDownList ID="ColourDropDown" runat="server" 
                Width="80px" AutoPostBack="True">
                <asp:ListItem Value="Black">Black</asp:ListItem>
                <asp:ListItem Value="Silver">Silver</asp:ListItem>
            </asp:DropDownList>
        </p>
        <p>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                DataSourceID="SqlDataSource1" EnableModelValidation="True">
                <Columns>
                    <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
                    <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
                    <asp:BoundField DataField="Brand" HeaderText="Brand" SortExpression="Brand" />
                    <asp:BoundField DataField="ScreenSize" HeaderText="ScreenSize" 
                        SortExpression="ScreenSize" />
                    <asp:BoundField DataField="Colour" HeaderText="Colour" 
                        SortExpression="Colour" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
                SelectCommand="SELECT * FROM [Products] WHERE (([Brand] = @Brand) AND ([Colour] = @Colour) ([Price] ))">
                <SelectParameters>
                    <asp:ControlParameter ControlID="BrandDropDown" Name="Brand" 
                        PropertyName="SelectedValue" Type="String" />
                    <asp:ControlParameter ControlID="ColourDropDown" Name="Colour" 
                        PropertyName="SelectedValue" Type="String" />
                    <asp:ControlParameter ControlID="PriceDropDown" Name="Price" 
                        PropertyName="SelectedValue" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>
        </p>
    </asp:Content>
    If anyone can help it would be much appreciated Also sorry if this is in the wrong section, i'm a bit new to all of this.

  • #2
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,723
    Thanks
    41
    Thanked 191 Times in 190 Posts
    You really should move that inline SQL to at least the code behind; preferably to a stored proc... either way the correct syntax (without knowing your database structure that is) would be
    Code:
    SELECT * 
    FROM [Products] 
    WHERE [Brand] = @Brand 
              AND [Colour] = @Colour 
              AND [Price] BETWEEN @LoNumber AND @HiNumber
    so you need to figure out how to split the 'value' into usable numbers. You can pass this to a stored proc easily
    Code:
    CREATE PROC usp_getProducts
         @Brand varchar = null,
         @Colour varchar = null,
         @LoNumber int = null,
         @HiNumber int = null,
    AS
    SELECT * 
    FROM [Products] 
    WHERE [Brand] = @Brand 
              AND [Colour] = @Colour 
              AND [Price] BETWEEN @LoNumber AND @HiNumber

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Change this:
    Code:
            <asp:DropDownList ID="ScreenDropDown" runat="server" 
                Width="80px" AutoPostBack="True">
                <asp:ListItem Value="ScreenSize &gt;= 32 &lt;=39">From 32&quot; to 39&quot;</asp:ListItem>
                <asp:ListItem Value="ScreenSize &gt;=40 &lt;=45">From 40&quot; to 45&quot;</asp:ListItem>
                <asp:ListItem Value="ScreenSize &gt;=46 &lt;=55">From 46&quot; to 55&quot;</asp:ListItem>
                <asp:ListItem Value="ScreenSize &gt;=56">56&quot; and More</asp:ListItem>
            </asp:DropDownList>
    to simply
    Code:
            <asp:DropDownList ID="ScreenDropDown" runat="server" 
                Width="80px" AutoPostBack="True">
                <asp:ListItem Value="32:39">From 32&quot; to 39&quot;</asp:ListItem>
                <asp:ListItem Value="40:45">From 40&quot; to 45&quot;</asp:ListItem>
                <asp:ListItem Value="46:55">From 46&quot; to 55&quot;</asp:ListItem>
                <asp:ListItem Value="56:999999">56&quot; and More</asp:ListItem>
            </asp:DropDownList>
    There is *NO REASON* to have human readable text for those VALUE= values.

    *NOW* in your ASP.NET code you can do something like:
    Code:
        Dim sizes As String() = Split( ScreenDropDown.SelectedValue, ":" )
        Dim low As Integer = CINT( sizes(0) )
        Dim high As Integer = CINT( sizes(1) )
    And now you can pass low and high to the SQL query or stored procedure that Alykins showed.

    Pardon me if I goofed a bit in the VB syntax; been a long time since I used VB.NET. But the concept is right.
    Last edited by Old Pedant; 01-05-2013 at 01:46 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    And if it's not obvious: Do the same thing with price. And whether you use colon or dash between the range values doesn't matter. Just use the same character in the SPLIT( ) function call.

    Just be sure that *ALL* of your values in the drop downs *ARE* ranges.

    In the PRICE dropdown, the first two shown are not. Fix them:
    Code:
                <asp:ListItem Value="0-199.99">Under £200</asp:ListItem>
                <asp:ListItem Value="200-499.99">£200-500</asp:ListItem>
                <asp:ListItem Value="500-999.99">£500-1000</asp:ListItem>
                <asp:ListItem Value="1000-1499.99">£1000-1500</asp:ListItem>
                <asp:ListItem Value="1500-10000000">Over £1500 </asp:ListItem>
    Also, don't have any overlaps. Though, in a sense, they don't hurt. If an item was priced at *EXACTLY* 200, say, it would show up in both "Under 200" and "200-500" if you do have an overlap.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,723
    Thanks
    41
    Thanked 191 Times in 190 Posts
    I was gunna leave the finding out of split() to him :P

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •