How to display records according to this rule?

Discussion in 'ASP .Net' started by Miguel Dias Moura, Dec 7, 2004.

  1. Hello,

    I have a search form in an ASP.NET/VB page.
    The form has the input text box and the button "search".

    The keywords are passed in the URL to results.aspx.

    Here is an example:
    results.aspx?search=asp%20book%20london

    (%20 means for space)

    I used the words "asp", "book", "london".

    Each database record has 2 fields: "title" and "description".

    I want to display all the records where at least one of the keywords is
    found in any of the 2 fields "title" and "description".

    1. I suppose I need to get the words out of the URL to be used by SQL.
    Am I right? How can I do this?

    2. How should the SQL look?

    I have been applying filters but never something as complex as this
    search.

    Thanks,
    Miguel
    Miguel Dias Moura, Dec 7, 2004
    #1
    1. Advertising

  2. This isn't really complex at all.

    > 1. I suppose I need to get the words out of the URL to be used by SQL.
    > Am I right? How can I do this?


    string[] aryValues = Request.QueryString("search").Split(' ');

    > 2. How should the SQL look?


    SELECT * FROM mytable WHERE title LIKE '%asp%'
    OR title LIKE '%book%'
    OR title LIKE '%London%'
    OR description LIKE '%asp%'
    OR description LIKE '%book%'
    OR description LIKE '%London%'

    --
    HTH,
    Kevin Spencer
    ..Net Developer
    Microsoft MVP
    Neither a follower
    nor a lender be.

    "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message
    news:...
    > Hello,
    >
    > I have a search form in an ASP.NET/VB page.
    > The form has the input text box and the button "search".
    >
    > The keywords are passed in the URL to results.aspx.
    >
    > Here is an example:
    > results.aspx?search=asp%20book%20london
    >
    > (%20 means for space)
    >
    > I used the words "asp", "book", "london".
    >
    > Each database record has 2 fields: "title" and "description".
    >
    > I want to display all the records where at least one of the keywords is
    > found in any of the 2 fields "title" and "description".
    >
    > 1. I suppose I need to get the words out of the URL to be used by SQL.
    > Am I right? How can I do this?
    >
    > 2. How should the SQL look?
    >
    > I have been applying filters but never something as complex as this
    > search.
    >
    > Thanks,
    > Miguel
    >
    >
    >
    Kevin Spencer, Dec 7, 2004
    #2
    1. Advertising

  3. Hi,

    If you enable Full-Text Search on the database and also the table in
    question you can use the following query and pass directly the search
    string:

    SELECT < some columns or * > FROM < your table >
    INNER JOIN
    FREETEXTTABLE(< your table >, < column or * >, @p_freetext) AS KEY_TBL
    ON < your table >.< your table's unique key> = KEY_TBL.[KEY]
    (optionally:)
    WHERE ....
    (recommended:)
    ORDER BY KEY_TBL.[RANK]

    Then just add the @p_freetext parameter to the parameters of the command.

    See the topics related to full-text querying in MS SQL Server Books Online
    for more details and examples.

    Hope this helps
    Martin Dechev
    ASP.NET MVP
    "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message
    news:...
    > Hello,
    >
    > I have a search form in an ASP.NET/VB page.
    > The form has the input text box and the button "search".
    >
    > The keywords are passed in the URL to results.aspx.
    >
    > Here is an example:
    > results.aspx?search=asp%20book%20london
    >
    > (%20 means for space)
    >
    > I used the words "asp", "book", "london".
    >
    > Each database record has 2 fields: "title" and "description".
    >
    > I want to display all the records where at least one of the keywords is
    > found in any of the 2 fields "title" and "description".
    >
    > 1. I suppose I need to get the words out of the URL to be used by SQL.
    > Am I right? How can I do this?
    >
    > 2. How should the SQL look?
    >
    > I have been applying filters but never something as complex as this
    > search.
    >
    > Thanks,
    > Miguel
    >
    >
    >
    Martin Dechev, Dec 7, 2004
    #3
  4. Hi,

    I am a little bit about your solution.
    Sorry but I am new in this.

    How to I apply " string[] aryValues =
    Request.QueryString("search").Split(' ');" to be executed when the page
    loads?

    The number of Keywords it's not always 3. It can be more.
    I suppose your SQL doesn't predicts that.

    Thanks,
    Miguel

    "Kevin Spencer" <> wrote in message
    news::
    > This isn't really complex at all.
    >
    > > 1. I suppose I need to get the words out of the URL to be used by SQL.
    > > Am I right? How can I do this?

    >
    > string[] aryValues = Request.QueryString("search").Split(' ');
    >
    > > 2. How should the SQL look?

    >
    > SELECT * FROM mytable WHERE title LIKE '%asp%'
    > OR title LIKE '%book%'
    > OR title LIKE '%London%'
    > OR description LIKE '%asp%'
    > OR description LIKE '%book%'
    > OR description LIKE '%London%'
    >
    > --
    > HTH,
    > Kevin Spencer
    > .Net Developer
    > Microsoft MVP
    > Neither a follower
    > nor a lender be.
    >
    > "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message
    > news:...
    > > Hello,
    > >
    > > I have a search form in an ASP.NET/VB page.
    > > The form has the input text box and the button "search".
    > >
    > > The keywords are passed in the URL to results.aspx.
    > >
    > > Here is an example:
    > > results.aspx?search=asp%20book%20london
    > >
    > > (%20 means for space)
    > >
    > > I used the words "asp", "book", "london".
    > >
    > > Each database record has 2 fields: "title" and "description".
    > >
    > > I want to display all the records where at least one of the keywords
    > > is
    > > found in any of the 2 fields "title" and "description".
    > >
    > > 1. I suppose I need to get the words out of the URL to be used by SQL.
    > > Am I right? How can I do this?
    > >
    > > 2. How should the SQL look?
    > >
    > > I have been applying filters but never something as complex as this
    > > search.
    > >
    > > Thanks,
    > > Miguel
    > >
    > >
    > >
    Miguel Dias Moura, Dec 7, 2004
    #4
  5. Hi,

    It seems good but I got lost. :)

    Where can I find MS SQL Server Books Online?

    Is there any disadvantage of enabling a database to Full-Text Search?

    Thanks,
    Miguel

    "Martin Dechev" <> wrote in message
    news::
    > Hi,
    >
    > If you enable Full-Text Search on the database and also the table in
    > question you can use the following query and pass directly the search
    > string:
    >
    > SELECT < some columns or * > FROM < your table >
    > INNER JOIN
    > FREETEXTTABLE(< your table >, < column or * >, @p_freetext) AS KEY_TBL
    > ON < your table >.< your table's unique key> = KEY_TBL.[KEY]
    > (optionally:)
    > WHERE ....
    > (recommended:)
    > ORDER BY KEY_TBL.[RANK]
    >
    > Then just add the @p_freetext parameter to the parameters of the
    > command.
    >
    > See the topics related to full-text querying in MS SQL Server Books
    > Online
    > for more details and examples.
    >
    > Hope this helps
    > Martin Dechev
    > ASP.NET MVP
    > "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message
    > news:...
    > > Hello,
    > >
    > > I have a search form in an ASP.NET/VB page.
    > > The form has the input text box and the button "search".
    > >
    > > The keywords are passed in the URL to results.aspx.
    > >
    > > Here is an example:
    > > results.aspx?search=asp%20book%20london
    > >
    > > (%20 means for space)
    > >
    > > I used the words "asp", "book", "london".
    > >
    > > Each database record has 2 fields: "title" and "description".
    > >
    > > I want to display all the records where at least one of the keywords
    > > is
    > > found in any of the 2 fields "title" and "description".
    > >
    > > 1. I suppose I need to get the words out of the URL to be used by SQL.
    > > Am I right? How can I do this?
    > >
    > > 2. How should the SQL look?
    > >
    > > I have been applying filters but never something as complex as this
    > > search.
    > >
    > > Thanks,
    > > Miguel
    > >
    > >
    > >
    Miguel Dias Moura, Dec 7, 2004
    #5
  6. Hi,

    "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message
    news:...
    > Hi,
    >
    > It seems good but I got lost. :)
    >
    > Where can I find MS SQL Server Books Online?


    If have chosen to install them (they are optional components in the SQL
    Server installation) - Start -> Programs -> Microsoft SQL Server -> Books
    Online.

    >
    > Is there any disadvantage of enabling a database to Full-Text Search?


    The SQL Server process takes some more memory (and some CPU time when
    creating and updating the index). For small databases - there is small
    difference. Also, for small databases Kevin's solution is ok - it will be
    reasonably fast.

    Greetings
    Martin Dechev
    ASP.NET MVP

    >
    > Thanks,
    > Miguel
    >
    > "Martin Dechev" <> wrote in message
    > news::
    > > Hi,
    > >
    > > If you enable Full-Text Search on the database and also the table in
    > > question you can use the following query and pass directly the search
    > > string:
    > >
    > > SELECT < some columns or * > FROM < your table >
    > > INNER JOIN
    > > FREETEXTTABLE(< your table >, < column or * >, @p_freetext) AS KEY_TBL
    > > ON < your table >.< your table's unique key> = KEY_TBL.[KEY]
    > > (optionally:)
    > > WHERE ....
    > > (recommended:)
    > > ORDER BY KEY_TBL.[RANK]
    > >
    > > Then just add the @p_freetext parameter to the parameters of the
    > > command.
    > >
    > > See the topics related to full-text querying in MS SQL Server Books
    > > Online
    > > for more details and examples.
    > >
    > > Hope this helps
    > > Martin Dechev
    > > ASP.NET MVP
    > > "Miguel Dias Moura" <md*REMOVE*moura@*NOSPAM*gmail.com> wrote in message
    > > news:...
    > > > Hello,
    > > >
    > > > I have a search form in an ASP.NET/VB page.
    > > > The form has the input text box and the button "search".
    > > >
    > > > The keywords are passed in the URL to results.aspx.
    > > >
    > > > Here is an example:
    > > > results.aspx?search=asp%20book%20london
    > > >
    > > > (%20 means for space)
    > > >
    > > > I used the words "asp", "book", "london".
    > > >
    > > > Each database record has 2 fields: "title" and "description".
    > > >
    > > > I want to display all the records where at least one of the keywords
    > > > is
    > > > found in any of the 2 fields "title" and "description".
    > > >
    > > > 1. I suppose I need to get the words out of the URL to be used by SQL.
    > > > Am I right? How can I do this?
    > > >
    > > > 2. How should the SQL look?
    > > >
    > > > I have been applying filters but never something as complex as this
    > > > search.
    > > >
    > > > Thanks,
    > > > Miguel
    > > >
    > > >
    > > >

    >
    Martin Dechev, Dec 9, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luke Airig
    Replies:
    0
    Views:
    785
    Luke Airig
    Dec 31, 2003
  2. Replies:
    0
    Views:
    1,356
  3. work4u
    Replies:
    0
    Views:
    134
    work4u
    Jan 8, 2004
  4. Dan

    Delete records or update records

    Dan, May 10, 2004, in forum: ASP General
    Replies:
    1
    Views:
    460
    Ray at
    May 10, 2004
  5. Replies:
    3
    Views:
    653
    Anthony Jones
    Nov 2, 2006
Loading...

Share This Page