...

View Full Version : Database Search Page



Zarathas
01-04-2013, 02:44 PM
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
01-04-2013, 03:43 PM
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
01-05-2013, 02:42 AM
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
01-05-2013, 02:50 AM
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
01-05-2013, 06:49 AM
I was gunna leave the finding out of split() to him :P



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum