locked
Searching multiple key words in the same field RRS feed

  • Question

  • User-600945157 posted

    Hi,

    I am trying to build an sql command that selects multiple key words from one field in an Access Database then orders the results by the date they were added

    currently my sql statement is as follows;

    SelectCommand="SELECT [ID], [PartNo], [Description], [Date] FROM [StockItems] WHERE (([Category] = &quot;TVs&quot; AND &quot;SoundEquipment&quot;) AND ([ShowOnSite] = true)) ORDER BY [Date] DESC"></asp:SqlDataSource>

    However currently the command is returning the first category listed (in the case of the example TVs are returned first, if i move Sound equipment to be the first category listed then the sound equipment results are listed first), I need it so that the results are mixed based on the date which they were added.

    How to fix this?

    Thanks 

    Wednesday, February 25, 2015 4:07 AM

Answers

  • User-809753952 posted

    Change your query like this:

    SELECT [ID], [PartNo], [Description], [Date] FROM [StockItems]
    WHERE ( Category like '%TVs%' or Category like '%SoundEquipment%')
     AND ([ShowOnSite] = true) ORDER BY [Date] DESC
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 25, 2015 5:07 AM

All replies

  • User-809753952 posted

    how is your table data look like?

    Wednesday, February 25, 2015 4:27 AM
  • User-600945157 posted

    hi mnmhemaj,

    i dont have a data table, i am using an asp:datasource control to get the data from the database and display it in a listview control;

    <asp:SqlDataSource ID="sql#######" runat="server" ConnectionString="<%$ ConnectionStrings:######## %>" ProviderName="<%$ ConnectionStrings:########.ProviderName %>" SelectCommand="SELECT [ID], [PartNo], [Description], [Date] FROM [StockItems] WHERE (([Category] = &quot;TVs&quot; AND &quot;SoundEquipment&quot;) AND ([ShowOnSite] = true)) ORDER BY [Date] DESC"></asp:SqlDataSource>
    

    However i am only getting the results of the first category. Apologies if i do have a data table somewhere, this is my first project in asp.net so am a newbie

    Thanks.

    Wednesday, February 25, 2015 4:43 AM
  • User-809753952 posted

    please post your table structure and some sample data  from the database.

    Wednesday, February 25, 2015 4:45 AM
  • User-600945157 posted

    My Database table "StockItems" is as follows;

    ID | PartNo | Description    | Date       | ShowOnSite | Category
    -----------------------------------------------------------------
    1  | TV1    | Panasonic 32"  | 21/02/2015 | True       | TVs
    2  | SE1    | Sony MCV50     | 22/02/2015 | True       | SoundEquipment
    3  | TV2    | ##########     | 22/02/2015 | True       | Tvs        
    4  | SE2    | ##########     | 23/02/2015 | True       | SoundEquipment
    5  | TV3    | ##########     | 24/02/2015 | True       | TVs

    The Date field is set as DateTime field type

    Edit: there are other categories to be added to the table, ive only included two in the example 

    Thanks 

    Wednesday, February 25, 2015 5:00 AM
  • User-809753952 posted

    Change your query like this:

    SELECT [ID], [PartNo], [Description], [Date] FROM [StockItems]
    WHERE ( Category like '%TVs%' or Category like '%SoundEquipment%')
     AND ([ShowOnSite] = true) ORDER BY [Date] DESC
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 25, 2015 5:07 AM
  • User-600945157 posted

    Awesome, thank you

    Wednesday, February 25, 2015 5:38 AM