3

Searchfield with autocomplete

I would like to make a searchfield with autocomplete, so when you write like 2 or 3 letters it will show a dropdownlist with results that match a column from a table in the database.

I have been reading all over the internet, and i have found out that it can be done with jQuery and AJAX Control Toolkit. But i don't know how to make it compare the search textbox and the database and show results that match?

UPDATE:

I have been trying to make it work with Autocomplete extender from Ajax control toolkit, but i don't know how to make the Webservice with SQL.

UPDATE2

Can't get the webservice to work, so have been looking at jQuery UI, which can do autocomplete but don't know how to get that working with the database either.. :p

UPDATE3

I'm still stuck on this, so if anyone who knows how to make a datasource(webservice) that works with either Autocomplete extender or jQuery UI, please help me.

I want the autocomplete to show: SELECT * FROM TABEL WHERE Title LIKE + '%' @Search + '%' And its an MSSQL Database

Fogh
  • 1,275
  • 1
  • 21
  • 29
  • Even with jQuery UI you will need some form of datasource. You have not provided enough info on you DB schema to help in that regards. – Dustin Laine Mar 29 '10 at 16:06
  • I want the autocomplete to show: SELECT * FROM TABEL WHERE Title LIKE + '%' @Search + '%'. And its an MSSQL Database. – Fogh Mar 29 '10 at 16:44
  • Side note: That query will require a full index/table scan on each invocation. Depending on the table size, you might want to look into fulltext indexes and operators. – Ryan Bair Mar 30 '10 at 17:24
  • Your best bet is to create a web service that you use to interact with. Here is a pretty detailed SO post on it. https://stackoverflow.com/questions/240721/jquery-autocomplete-in-asp-net-webforms – Dustin Laine Mar 28 '10 at 22:18

1 Answers1

1

You can use Asp.net Ajax control toolkit.

http://www.asp.net/ajax/ajaxcontroltoolkit/Samples/AutoComplete/AutoComplete.aspx

The database can be searched as shown below.

select * from tbl_Country where countryName like '%xxx%'

You can call sql statement from a Service method. Service method can be a web service.

<ajaxToolkit:AutoCompleteExtender 
    runat="server" 
    ID="autoComplete1" 
    TargetControlID="myTextBox"
    ServiceMethod="GetCompletionList"
    ServicePath="AutoComplete.asmx"
</ajaxToolkit:AutoCompleteExtender>

Create an asmx web service in your web application named AutoComplete.asmx. Then create a web method GetCompletionList. Inside this method you can include System.Data.SqlClient and fire the query to the database.

Amitabh
  • 59,111
  • 42
  • 110
  • 159