locked
Search Queries in VB.net Help Required! RRS feed

  • Question

  • User-83227557 posted

    hi

    im trying to perform a search query using drop down boxes with a button named search. I want it to bring up the data that is searched for. I dont know where to start from, i have looked around for some coding and different ways to do it but they seem complicated, this is the bit of ASP that is my weakness, need some assistance and guidance. below is the code for the page;

     

    <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="ForSale.aspx.vb" Inherits="Users_ForSale" title="Properties For Sale" %>
    
    <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
        </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
            
            <h3>Properties For Sale</h3>
            <h5>
                <asp:Label ID="lblTown" runat="server" Text="Town:"></asp:Label>
                <asp:DropDownList ID="ddlTownSearch" runat="server">
                    <asp:ListItem></asp:ListItem>
                    <asp:ListItem>Chadderton</asp:ListItem>
                    <asp:ListItem Value="Failsworth"></asp:ListItem>
                    <asp:ListItem>Oldham</asp:ListItem>
                    <asp:ListItem>Royton</asp:ListItem>
                    <asp:ListItem>Shaw</asp:ListItem>
                </asp:DropDownList>
                <asp:Label ID="lblBedroomsSearch" runat="server" Text="Bedrooms:"></asp:Label>
                <asp:DropDownList ID="DropDownList1" runat="server">
                    <asp:ListItem></asp:ListItem>
                    <asp:ListItem>1</asp:ListItem>
                    <asp:ListItem Value="2"></asp:ListItem>
                    <asp:ListItem Value="3"></asp:ListItem>
                    <asp:ListItem>4</asp:ListItem>
                    <asp:ListItem>5</asp:ListItem>
                    <asp:ListItem>6+</asp:ListItem>
                </asp:DropDownList>
                <asp:Label ID="lblMinPrice" runat="server" Text="Min Price (£):"></asp:Label>
                <asp:TextBox ID="txtMinPriceSearch" runat="server" Width="87px"></asp:TextBox>
                <asp:Label ID="lblMaxPriceSearch" runat="server" style="text-align: left" 
                    Text="Max Price (£):"></asp:Label>
                <asp:TextBox ID="TextBox1" runat="server" Width="87px"></asp:TextBox>
            </h5>
            <h5>
                <asp:Button ID="btnForsaleSearch" runat="server" Text="Search" />
            </h5>
            <p>
                <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
                    AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" 
                    DataKeyNames="ProductId" DataSourceID="SqlDataSource1" ForeColor="#333333" 
                    GridLines="None" Width="588px">
                    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                    <Columns>
                        <asp:ImageField DataImageUrlField="ImageURL">
                        </asp:ImageField>
                        <asp:BoundField DataField="ProductId" HeaderText="ProductId" ReadOnly="True" 
                            SortExpression="ProductId" />
                        <asp:BoundField DataField="Description" HeaderText="Description" 
                            SortExpression="Description" />
                        <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
                        <asp:BoundField DataField="Town" HeaderText="Town" 
                            SortExpression="Town" />
                    </Columns>
                    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <AlternatingRowStyle BackColor="White" />
                </asp:GridView>
            </p>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:JPEstatesConnectionString %>" 
            SelectCommand="SELECT * FROM [Products]" 
                DeleteCommand="DELETE FROM [Products] WHERE [ProductId] = @ProductId" 
                InsertCommand="INSERT INTO [Products] ([ProductId], [Description], [Price], [Category], [ImageURL]) VALUES (@ProductId, @Description, @Price, @Category, @ImageURL)" 
                UpdateCommand="UPDATE [Products] SET [Description] = @Description, [Price] = @Price, [Category] = @Category, [ImageURL] = @ImageURL WHERE [ProductId] = @ProductId">
                    <DeleteParameters>
                        <asp:Parameter Name="ProductId" Type="String" />
                    </DeleteParameters>
                    <UpdateParameters>
                        <asp:Parameter Name="Description" Type="String" />
                        <asp:Parameter Name="Price" Type="Decimal" />
                        <asp:Parameter Name="Category" Type="String" />
                        <asp:Parameter Name="ImageURL" Type="String" />
                        <asp:Parameter Name="ProductId" Type="String" />
                    </UpdateParameters>
                    <InsertParameters>
                        <asp:Parameter Name="ProductId" Type="String" />
                        <asp:Parameter Name="Description" Type="String" />
                        <asp:Parameter Name="Price" Type="Decimal" />
                        <asp:Parameter Name="Category" Type="String" />
                        <asp:Parameter Name="ImageURL" Type="String" />
                    </InsertParameters>
            </asp:SqlDataSource>
    
    </asp:Content>
    
    
    Wednesday, April 6, 2011 5:51 AM

Answers

  • User3866881 posted

    Look at this referred sample from ecbruck:)

    Though it's C#, however it's nearly the same as what you can do in VB.net for aspx page codes

    <%@ page autoeventwireup="true" codefile="FilterWithDropDownList.aspx.cs"
            inherits="GridView_FilterWithDropDownList" language="C#" masterpagefile="~/MasterPages/Default.master"
            title="GridView: Filter With DropDownList" %>


    <asp:content id="Content1" runat="Server" contentplaceholderid="ContentPlaceHolder1">
            Select Category:
           
    <asp:dropdownlist id="ddlCategories" runat="server" appenddatabounditems="True" autopostback="True"
                    datasourceid="sdsCategories" datatextfield="CategoryName" datavaluefield="CategoryID">
                   
    <asp:listitem text="All Categories" value="-1">
                   
    </asp:listitem>
           
    </asp:dropdownlist>
            <
    asp:sqldatasource id="sdsCategories" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
                    selectcommand="SELECT [CategoryID], [CategoryName] FROM [Categories]">
           
    </asp:sqldatasource>
           
    <asp:gridview id="gvProducts" runat="server" autogeneratecolumns="False" datakeynames="ProductID"
                    datasourceid="sdsProducts" style="margin-top: 12px;">
                   
    <columns>
                           
    <asp:boundfield datafield="ProductID" headertext="ProductID" insertvisible="False"
                                    readonly="True" sortexpression="ProductID" />
                           
    <asp:boundfield datafield="ProductName" headertext="ProductName" sortexpression="ProductName" />
                           
    <asp:boundfield datafield="CategoryName" headertext="CategoryName" sortexpression="CategoryName" />
                   
    </columns>
           
    </asp:gridview>
            <
    asp:sqldatasource id="sdsProducts" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
                    selectcommand="SELECT [ProductID], [ProductName], [CategoryName] FROM [Alphabetical list of products] WHERE ([CategoryID] = CASE WHEN @CategoryID = -1 THEN [CategoryID] ELSE @CategoryID END) ORDER BY [ProductName]">
                   
    <selectparameters>
                           
    <asp:controlparameter controlid="ddlCategories" name="CategoryID" propertyname="SelectedValue"
                                    type="Int32" />
                   
    </selectparameters>
           
    </asp:sqldatasource>
    </asp:content>

    From http://forums.asp.net/t/1237671.aspx/1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 8, 2011 10:09 PM
  • User3866881 posted

    To j4v3d:)

    I think you can use LINQDATASOURCE with Dropdownlist to filter data contents with. Have a look  at this:

    http://www.codeproject.com/KB/aspnet/LinqDataSourcebasics1.aspx

    Here's the steps for you:

    1)

    image009.jpg

    2)

    image010.jpg

    3)

    image011.jpg

    4)

    image012.jpg

    5)

    image014.jpg

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 9, 2011 9:08 PM

All replies

  • User1288251396 posted
    1. You may want to use ControlParameters so that your SqlDataSource uses the DropDownLists' values.
    2. This website has an excellent tutorial that should help you get started. It's a little out of date (.Net 2.0) but it's still quite valuable. You can find it at: http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/default.aspx
    Thursday, April 7, 2011 10:26 PM
  • User3866881 posted

    Look at this referred sample from ecbruck:)

    Though it's C#, however it's nearly the same as what you can do in VB.net for aspx page codes

    <%@ page autoeventwireup="true" codefile="FilterWithDropDownList.aspx.cs"
            inherits="GridView_FilterWithDropDownList" language="C#" masterpagefile="~/MasterPages/Default.master"
            title="GridView: Filter With DropDownList" %>


    <asp:content id="Content1" runat="Server" contentplaceholderid="ContentPlaceHolder1">
            Select Category:
           
    <asp:dropdownlist id="ddlCategories" runat="server" appenddatabounditems="True" autopostback="True"
                    datasourceid="sdsCategories" datatextfield="CategoryName" datavaluefield="CategoryID">
                   
    <asp:listitem text="All Categories" value="-1">
                   
    </asp:listitem>
           
    </asp:dropdownlist>
            <
    asp:sqldatasource id="sdsCategories" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
                    selectcommand="SELECT [CategoryID], [CategoryName] FROM [Categories]">
           
    </asp:sqldatasource>
           
    <asp:gridview id="gvProducts" runat="server" autogeneratecolumns="False" datakeynames="ProductID"
                    datasourceid="sdsProducts" style="margin-top: 12px;">
                   
    <columns>
                           
    <asp:boundfield datafield="ProductID" headertext="ProductID" insertvisible="False"
                                    readonly="True" sortexpression="ProductID" />
                           
    <asp:boundfield datafield="ProductName" headertext="ProductName" sortexpression="ProductName" />
                           
    <asp:boundfield datafield="CategoryName" headertext="CategoryName" sortexpression="CategoryName" />
                   
    </columns>
           
    </asp:gridview>
            <
    asp:sqldatasource id="sdsProducts" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"
                    selectcommand="SELECT [ProductID], [ProductName], [CategoryName] FROM [Alphabetical list of products] WHERE ([CategoryID] = CASE WHEN @CategoryID = -1 THEN [CategoryID] ELSE @CategoryID END) ORDER BY [ProductName]">
                   
    <selectparameters>
                           
    <asp:controlparameter controlid="ddlCategories" name="CategoryID" propertyname="SelectedValue"
                                    type="Int32" />
                   
    </selectparameters>
           
    </asp:sqldatasource>
    </asp:content>

    From http://forums.asp.net/t/1237671.aspx/1

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 8, 2011 10:09 PM
  • User-83227557 posted

    i will give both a go, and if i get stuck and need help will get back to you both, thank you for taking time out and assisting me. I really appreciate it

     

    can i not use LINQ for search quieries by using the drop down boxes and somehow data-binding this to the gridview?

    Saturday, April 9, 2011 6:34 AM
  • User3866881 posted

    To j4v3d:)

    I think you can use LINQDATASOURCE with Dropdownlist to filter data contents with. Have a look  at this:

    http://www.codeproject.com/KB/aspnet/LinqDataSourcebasics1.aspx

    Here's the steps for you:

    1)

    image009.jpg

    2)

    image010.jpg

    3)

    image011.jpg

    4)

    image012.jpg

    5)

    image014.jpg

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 9, 2011 9:08 PM