locked
RE: AutoComplete in WebMatrix RRS feed

  • Question

  • User-371148474 posted

    The Autocomplete tutorials provided by mikesdot netting did great,

    I was looking at taking it to a new level.. How possible is it to allow the autocomplete feature for three different labels..

    Currently, the query command 

    var db = Database.Open("Northwind");
        var sql = "SELECT ProductName AS label FROM Products WHERE ProductName LIKE @0";
        var term = Request["term"] + "%";
        var result = db.Query(sql, term);
    
    Only outputs ProductName As label, WHat if I have a text field that allows users search for either Book Title, Author Name or ISBN.. How do I include this in the query to acomodate that and autocomplete depending on which one is entered?

    Thanks.. I hope the illustration is straight
    Saturday, June 7, 2014 12:59 PM

Answers

  • User-1416423428 posted

    Only outputs ProductName As label, WHat if I have a text field that allows users search for either Book Title, Author Name or ISBN.. How do I include this in the query to acomodate that and autocomplete depending on which one is entered?

    1. You can search multiple fields in your SQL query, and use a bunch of OR logic...  you can do a FieldName LIKE '%@0%' OR FieldName2 LIKE '%@0%' OR ....  

    2. or You can use SQL Server's Full Text Search capability. http://msdn.microsoft.com/en-us/library/ms142571.aspx

    When you define the table's Full Text index, you can specify which columns you want searched for, just tick the checkbox for these columns.  

    Then in your SQL query, you 'll use CONTAINSTABLE() or FREETEXTTABLE() 
    http://technet.microsoft.com/en-us/library/aa172823(v=sql.80).aspx

    If you only have a few records, method #1 may be sufficient. But could get slow once you have lots of records.

    Method #2 will be fast, and more flexible, but a bit more complex than a simple LIKE comparison. 

    Of course, your returned output field will always be a single type.  i.e. if you enter a partial ISBN # for example, you'll get the book titles back, or if you enter the author's name, you'll get all his books back.  

    *** if you have separate authors and books table, then you'll have to do some JOIN commands, in addition to the CONTAINSTABLE/FREETEXTTABLE() command (or LIKE(), if your chose the first method.)  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 7, 2014 1:26 PM
  • User-371148474 posted

    Truth is, it did return output field only once (single type) irrepsective of the text field value... I think I can defend that with the above reasons.

    I wanted to find out if there was an adavance way of seeing it through thanks..

    One last issue, How do I get an input text not accept empty inputs (like spacebars "whitespace"), google search was perfect in ignoring this..I can validate against empty text but not like this.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 7, 2014 1:34 PM
  • User-1416423428 posted

    I wanted to find out if there was an adavance way of seeing it through thanks..

    Use FullText search and rank by relevancy. 

    How do I get an input text not accept empty inputs (like spacebars "whitespace"),

    Use jquery/javascript for that, and filter your input field on the client side. 

    Of course, don't rely on this, you still need to validate on the server side.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 7, 2014 3:42 PM

All replies

  • User-1416423428 posted

    Only outputs ProductName As label, WHat if I have a text field that allows users search for either Book Title, Author Name or ISBN.. How do I include this in the query to acomodate that and autocomplete depending on which one is entered?

    1. You can search multiple fields in your SQL query, and use a bunch of OR logic...  you can do a FieldName LIKE '%@0%' OR FieldName2 LIKE '%@0%' OR ....  

    2. or You can use SQL Server's Full Text Search capability. http://msdn.microsoft.com/en-us/library/ms142571.aspx

    When you define the table's Full Text index, you can specify which columns you want searched for, just tick the checkbox for these columns.  

    Then in your SQL query, you 'll use CONTAINSTABLE() or FREETEXTTABLE() 
    http://technet.microsoft.com/en-us/library/aa172823(v=sql.80).aspx

    If you only have a few records, method #1 may be sufficient. But could get slow once you have lots of records.

    Method #2 will be fast, and more flexible, but a bit more complex than a simple LIKE comparison. 

    Of course, your returned output field will always be a single type.  i.e. if you enter a partial ISBN # for example, you'll get the book titles back, or if you enter the author's name, you'll get all his books back.  

    *** if you have separate authors and books table, then you'll have to do some JOIN commands, in addition to the CONTAINSTABLE/FREETEXTTABLE() command (or LIKE(), if your chose the first method.)  

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 7, 2014 1:26 PM
  • User-371148474 posted

    Truth is, it did return output field only once (single type) irrepsective of the text field value... I think I can defend that with the above reasons.

    I wanted to find out if there was an adavance way of seeing it through thanks..

    One last issue, How do I get an input text not accept empty inputs (like spacebars "whitespace"), google search was perfect in ignoring this..I can validate against empty text but not like this.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 7, 2014 1:34 PM
  • User-1416423428 posted

    I wanted to find out if there was an adavance way of seeing it through thanks..

    Use FullText search and rank by relevancy. 

    How do I get an input text not accept empty inputs (like spacebars "whitespace"),

    Use jquery/javascript for that, and filter your input field on the client side. 

    Of course, don't rely on this, you still need to validate on the server side.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, June 7, 2014 3:42 PM
  • User-371148474 posted

    I tried using the OR logic ...with a FieldName LIKE '%@0%' OR FieldName2 LIKE '%@0%' OR..

    It didnt work though.. the sql Query output was empty. I tried replacing '%@0' with 

    '%'+@0+'%' and it did work out ....Can I continue with this or its bad practice.. 
    
    Sunday, June 8, 2014 5:08 AM
  • User-1416423428 posted

    ....Can I continue with this or its bad practice.. 

    The "bad" about this LIKE %keyword% search is it cannot use any indexes that you may have setup for that column in your SQL database. So just take note. SQL has to do a brute force search for each and every record.  

    Unlike a LIKE 'keyword%', where it can still take advantage of the indexes.  

    But in my experience, even a 60K records brute force search is still pretty fast doing a LIKE %keyword% search. (caveat: depends on your cpu/#cores/memory, # of simultaneous visitors, etc.)  I don't know how many records you have, and I don't know the upper limit when this kind of search starts to bog down.  

    Try it and see. Couldn't hurt.  If you have a bigger database, then definitely take a look at the FullText Indexing search. 

    Sunday, June 8, 2014 8:06 AM