ASP & jQuery UI: autocomplete with json source (part: 2)

Tweet

In my previous post, I've introduced the creation of an input box with an autocomplete feature. We used jQuery and jQuery UI in order to build the main asp page. Now we need to create the source for the autocomplete items list.
Because the jQuery UI autocomplete widget needs to have a source in JSON format, we have to retrieve the needed data from a database and feed it to the widget in a properly formatted way.
I don't know if you are familiar with JSON. I wasn't. One web site that helped me a bit in checking the output of the source.asp page has been JSONLint. Please refer to it if you need to test your JSON output.

Now, let's see the code needed to get the data and properly format it.
First thing to do is declare some variables:

<%
Dim keywords
Dim keywords_cmd
Dim output 

Then we prepare the command object and get the data:

Set keywords_cmd = Server.CreateObject ("ADODB.Command")
keywords_cmd.ActiveConnection = your_connection_string
keywords_cmd.CommandText = "SELECT id, name FROM dbo.users where name like '%"  & Request.QueryString("term") & "%'"
keywords_cmd.Prepared = true

Set keywords = keywords_cmd.Execute

I think we need some explanations here.
1) Change your_connection_string with a proper connection string to your database (SQL Server, Ms Access of whatever).
2) Change the query according to your needs. In the example we are getting data from a table called "dbo.users". The important part is that we need to filter the data somehow. Because in our example we are creating an autocomplete list with names, we need to filter the recordset accordingly. The autocomplete widget passes an URL parameter (term) in order to achieve that. Then, please be careful with that part. Just to be more clear, the filter in the example is getting all the names that contains the search string, If we want to filter data using the search string as the first characters, we need to remove % just after like in the WHERE clause.

After the above, we use the variable called "output" in order to format the retrieved information. Just to be clear, JSON has an easy structure, which can be explained as:

[
{"label1": "value1", "label2": "value2"},
{"label1": "value3", "label2": "value4"}
]

Let's go on and see how to build it:

output = "["

While (NOT keywords.EOF)
    output = output & "{""id"":""" & keywords.Fields.item("id") & """,""value"":""" & keywords.Fields.Item("name") & """},"
     keywords.MoveNext()
Wend

keywords.Close()
Set keywords = Nothing

output=Left(output,Len(output)-1)
output = output & "]"
response.write output

%>

In the first line we start filling the output variable. We then open a repeat region and append values retrieved from our database.
At the end of the repeat region, we close the command object and set it to nothing.
We finally remove the last comma in our string and close it with "]". After that we output the resulting variable (with response.write).

And that is all. Save the file as "source.asp" (in the same folder where the "autocomplete.asp" file is), and open autocomplete.asp in your favourite browser.

Enjoy and let me know what you think about it.