locked
Help With Multiples control/SQL/Access Database/VS2010Express RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User1128081051 posted

    Hello everyone. I just started with ASP/VB/VS2010 just a few days. So far everything went well but I am running into some problem.

    I have a search page. This search page have 3 controls: textbox, listbox, dropdownlist. and a button "search". I want to make it so that when I submit the data the database(access) will return the item being search. It will search in the colums selected by the listbox, and look for the category in the dropdownlist.

    But somehow my SQL command, as well as the query builder is not really helping me in this process. It's making thing more complicated. The database connection and such is fine it just I don't know how to manage all 3 controls in SQL and make them return to me the data I need. Also there is a default catagories which is search in all columns, and in all for all category.

    Also. If you guys can help me with a way to post the returning data to a different page. That be great also. Thank you in advance.

     

     

     

     

    Friday, November 12, 2010 10:12 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-1199946673 posted

    But somehow my SQL command, as well as the query builder is not really helping me in this process
     

    That's because the VWD/Visual Studio query builder doen't fully support Access. Only very simple basic statements are supported.

    Also, bevause you can select on or more colums in the lixtbox to do the select, even when using SQL Server, you'll need to do some coding to create the SelectCommand, because you cannot parameterize columns. When using as Access- Or SQL-DataSource names DsBooks it shoudl be something like:

                DsBooks.SelectCommand = "SELECT * FROM BOOKS WHERE Category = @Category"
            For Each Item As ListItem In listbox1.Items
                If Item.Selected Then
                    DsBooks.SelectCommand += String.Format(" AND {0} Like @Search + '%'", Item.Text)
                End If
            Next


    and you can use Control parameters to bind the parameters:

                <SelectParameters>
                <asp:ControlParameter Name="Category" ControlID="CategoryDropDownList" PropertyName="SelectedValue" />
                <asp:ControlParameter Name="Search" ControlID="SearchTextBox" PropertyName="Text" />
            </SelectParameters>


    And if you want to show all records in the gridview when the textbox is empty, don't forget to set the CancelSelectOnNullParameter of the DataSource to false

    More info

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria 

    But I see you're using an ObjectDataSource? Then it all deplands how you coded your object....

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 13, 2010 7:14 AM