Database Search Page

[Zarathas]

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,

<%@ 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.

[alykins]

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

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

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

[Old Pedant]

Change this:

        <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

        <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:

    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.

[Old Pedant]

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:

            <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.

[alykins]

I was gunna leave the finding out of split() to him :P