locked
Please Help - Searching Database using DropDown Box RRS feed

  • Question

  • User-1382820488 posted

    Hi All

     I have no doubt that this is simple to explain however i just cant work it out.

     

    Basically i have a database containing jobs - I have databound this to a Grid View using AccessDataSource Control.

     I then have a drop down box which is databound to a seperate table using a sepearate AccessDataSource control which gives the drop down box a list of job titles.

     

    SO the problem I am facing is i can search the database and pick out exact matches between the Database and the DropDown box. For example if there are jobs in the database with the following titles:

    >> Website Develop

    >> Website Developer

    >> Website Developer - ASP.NET

    >> Website Developer PHP

     And lets say in the Drop Down box i selected the value that says "Website Developer" The website will ONLY return the jobs with the title exactly matching the value in the Drop Down Box - so Website Developer - ASP.NET and Website Developer PHP will not be displayed.

    I need someone to please explain how to search the "JobTitle" field of the database to find jobs that CONTAIN the selected title from the DropDown box rather than finding jobs with an exact title to that selected in the Drop Down Box.

     

    Below is my code for the Access Data Source Control for the database - The AccessDataSource for DropDown box and the button that calls the SQL statement:

     

     //Code For SQL Statement ------------------------------------------------------------------------------------------------------------

    <SCRIPT Runat="Server">
    
    Sub Show_Type (Src As Object, Args As EventArgs)
    
      Dim SQLString As String
            SQLString = "SELECT JobID, Job_Title, Job_Location, Area, Salary FROM Jobs " & _
                  "WHERE Job_Title = '" & DropDownList1.DataValueField & "'"
            AccessDataSource1.SelectCommand = SQLString
    
    End Sub
    
    </SCRIPT>
    
     
    //Code for Database AccessDataSource -------------------------------------------------------------------------------------
     
    <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
          DataFile="~/jobs.mdb" 
          SelectCommand="SELECT [JobID], [Job_Title], [Job_Location], [Area], [Salary] FROM [Jobs]">
    </asp:AccessDataSource>
    // Code for DataList -----------------------------------------------------------------------------------------------------------------
     
    <asp:DataList ID="DataList1" runat="server" BackColor="White" 
                BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" 
                DataSourceID="AccessDataSource1" ForeColor="Black" GridLines="Vertical" 
                Width="755px">
                <FooterStyle BackColor="#CCCCCC" />
                <AlternatingItemStyle BackColor="#CCCCCC" />
                <SelectedItemStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                <ItemTemplate>
                    JobID:
                    <asp:Label ID="JobIDLabel" runat="server" Text='<%# Eval("JobID") %>' />
                    <br />
                    Job_Title:
                    <asp:Label ID="Job_TitleLabel" runat="server" Text='<%# Eval("Job_Title") %>' />
                    <br />
                    Job_Location:
                    <asp:Label ID="Job_LocationLabel" runat="server" 
                        Text='<%# Eval("Job_Location") %>' />
                    <br />
                    Area:
                    <asp:Label ID="AreaLabel" runat="server" Text='<%# Eval("Area") %>' />
                    <br />
                    Salary: £<asp:Label ID="SalaryLabel" runat="server" 
                        Text='<%# Eval("Salary") %>' />
                    <br />
                    <br />
                </ItemTemplate>
            </asp:DataList>
      //Code For DropDown AccessDataSource Control -----------------------------------------------------------------
     <asp:AccessDataSource ID="AccessDataSource2" runat="server" 
         DataFile="~/jobs.mdb" SelectCommand="SELECT [Job_title] FROM [Job_title]">
    </asp:AccessDataSource>  
     //Code For DropDown Box ---------------------------------------------------------------------------------------------------
     
    <asp:DropDownList ID="DropDownList1" runat="server" 
        DataSourceID="AccessDataSource2" DataTextField="Job_title" 
        DataValueField="Job_title" Height="25px" Width="202px">
    </asp:DropDownList>
      //Code For Search Button ---------------------------------------------------------------------------------------------------
    <asp:Button ID="Button1" runat="server" OnClick="Show_Type" Text="Button" /> 
    Thursday, February 5, 2009 5:24 AM

Answers

  • User-796298121 posted

    The first thing I would try is to use the Like operator in your Select command.  For instance:

    SQLString = "SELECT JobID, Job_Title, Job_Location, Area, Salary FROM Jobs " & _
                  "WHERE Job_Title LIKE '" & DropDownList1.DataValueField & "*'"

    I'm not an MS Access person so you may have to check the syntax but using a like should help to broaden the results.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 5, 2009 8:53 AM
  • User-1199946673 posted

    The first thing I would try is to use the Like operator in your Select command.  For instance:

    SQLString = "SELECT JobID, Job_Title, Job_Location, Area, Salary FROM Jobs " & _
                  "WHERE Job_Title LIKE '" & DropDownList1.DataValueField & "*'"

    I'm not an MS Access person so you may have to check the syntax but using a like should help to broaden the results.

    I don't  think that this is correct The wildcard character when using Acces is *, however, when using OleDb in .NET, the wildcard character is %, like in SQL Server. Also, the question is 'to find jobs that CONTAIN the selected title from the DropDown box'. And if you set the AutoPostBack property of the DropDownList to true, you don't have to write any line of code:

    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"
        DataSourceID="AccessDataSource2" DataTextField="Job_title"
        DataValueField="Job_title" Height="25px" Width="202px">

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"
          DataFile="~/jobs.mdb"
          SelectCommand="SELECT [JobID], [Job_Title], [Job_Location], [Area], [Salary] FROM [Jobs] WHERE Job_Title LIKE '%' + ? + '%'">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" Name="Job_Title" PropertyName="SelectedValue" />
            </SelectParameters>
    </asp:AccessDataSource>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 5, 2009 10:45 AM

All replies

  • User-796298121 posted

    The first thing I would try is to use the Like operator in your Select command.  For instance:

    SQLString = "SELECT JobID, Job_Title, Job_Location, Area, Salary FROM Jobs " & _
                  "WHERE Job_Title LIKE '" & DropDownList1.DataValueField & "*'"

    I'm not an MS Access person so you may have to check the syntax but using a like should help to broaden the results.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 5, 2009 8:53 AM
  • User-1199946673 posted

    The first thing I would try is to use the Like operator in your Select command.  For instance:

    SQLString = "SELECT JobID, Job_Title, Job_Location, Area, Salary FROM Jobs " & _
                  "WHERE Job_Title LIKE '" & DropDownList1.DataValueField & "*'"

    I'm not an MS Access person so you may have to check the syntax but using a like should help to broaden the results.

    I don't  think that this is correct The wildcard character when using Acces is *, however, when using OleDb in .NET, the wildcard character is %, like in SQL Server. Also, the question is 'to find jobs that CONTAIN the selected title from the DropDown box'. And if you set the AutoPostBack property of the DropDownList to true, you don't have to write any line of code:

    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"
        DataSourceID="AccessDataSource2" DataTextField="Job_title"
        DataValueField="Job_title" Height="25px" Width="202px">

    <asp:AccessDataSource ID="AccessDataSource1" runat="server"
          DataFile="~/jobs.mdb"
          SelectCommand="SELECT [JobID], [Job_Title], [Job_Location], [Area], [Salary] FROM [Jobs] WHERE Job_Title LIKE '%' + ? + '%'">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" Name="Job_Title" PropertyName="SelectedValue" />
            </SelectParameters>
    </asp:AccessDataSource>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 5, 2009 10:45 AM