Wednesday 31 August 2011

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

   


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.

33 comments:

  1. Great job mate! :)
    However I got a question....do you know if there is a way to multiple data the output? I mean username on username, usersurname on usersurname and so on ?

    ReplyDelete
    Replies
    1. I'm sorry but I don't understand what you mean... can you elaborate, please?

      Delete
  2. Hi Marco!
    First of all thx for the "hyper-fast" reply :)

    I know....I was bit messy while trying to write my problem.
    The question is this:
    For instance I got a Table called TABLE with tre columns A1,A2,A3. With jquery autocomplete I'm able to find value of A1 and/or A2 and/or A3 via JSON and put one them in an input text or textarea.
    The question is....how can I get A1 , A2 and A3 and put them all in 3 different input text or textarea?

    p.s. your name seems italian....are you from italy :)?

    ReplyDelete
    Replies
    1. Hey!
      First of all you can go to jqueryui.com/demos/autocomplete/ and see all the available example for the autocomplete widgets.
      In any case, in order to do what you say, you need to "link" the three inputs. What I would do is use the autocomplete on just one of the input boxes and populate the other two according to the results of the first. That is if A1, A2 and A3 are all linked and the choice is not a free combination of the three. If it is indeed what you're looking for (free combination of the 3 values), I believe you should create three different input boxes and three different autocomplete procedures (as if you were query 3 different tables): this is the easiest and fastest way of doing it, I believe.
      I hope that helps.

      p.s. Yep! I'm italian...

      Delete
  3. I've read all the docs but some examples are just terrible:P
    I've found better things (like this blog!) searching around via google.

    The real problem? A1, A2 and A3 are "linked" but I cannot split them from the json.
    For istances when I'm searching by A1 I would love to have the A2 and A3 in another 2 different box but I'm unable to do this since the JSON must contain A2 and A3 (and this mess hard my search)

    I'm still searching a solution and maybe it's just the fact I'm getting crazy with this since 1 day :P

    Really thx for your help.

    p.s. [ita on] Sei davvero gentilissimo. Sono anni (parecchi) che giro su forum e blog ma il tuo è scritto perfettamente e riesci ad entrare nel problema pratico senza confondere le idee come capita su almeno il 90% dei blog dedicati alla programmazione [ita off]



    :)
    Grazie mille Marco.
    Gentilissimo davvero. Purtroppo i valori sono "correlati"

    ReplyDelete
    Replies
    1. I will answer in English for everyone's benefit ;-)
      If the 3 values are linked, I would use the autocomplete just for the A1 value. Stick to that and make it work.
      When the user select a value from the first input, he/she will benefit from the autocomplete feature. When a value is selected, the other two input boxes will be populated accordingly using dynamically linked input boxes (did I publish something about it? Hmmm... I really don't remember). In any case, if you use Google and search for "asp dynamically linked input boxes" you will find tons of examples... If you need more help just ask...

      [ita on] Grazie mille per i complimenti. Faccio una fatica bestia a mantenere attivo il blog, e il mio principale intento è proprio quello di arrivare al dunque senza fronzoli inutili. Questo comporta il fatto che diventa sempre più difficile trovare argomenti nuovi per il Blog... 3 articoli la settimana sono un grosso impegno che non avevo assolutamente previsto all'inizio... Grazie ancora! [ita off]

      Delete
  4. Hi, After spending many hours googling I finally came across your blog and it looks very well put together and nicely explained....however..I cannot get your code to work. When I type in the textbox in autocomplete.asp nothing happens ? If i go directly to source.asp it is generating some code. Am I doing something wrong ?

    [{"RMA_ID":"17500","value":"17500"},{"RMA_ID":"17501","value":"17501"},{"RMA_ID":"17502","value":"17502"},{"RMA_ID":"17503","value":"17503"},{"RMA_ID":"17504","value":"17504"},{"RMA_ID":"17505","value":"17505"},{"RMA_ID":"17507","value":"17507"},{"RMA_ID":"17508","value":"17508"},{"RMA_ID":"17509","value":"17509"},{"RMA_ID":"17510","value":"17510"}]

    I have tried it in IE8 and google chrome. No error messages it just doesnt do anything ? Unfortunately I cant post the code in this reply but can send it if you can help ? (fingers crossed)

    ReplyDelete
    Replies
    1. Hello, and thanks for sharing your problem.
      At first sight the JSON lokks good, so it must be something else in your code. I can't obviously know what it is without looking into it. Please send me the two asp pages (source and autocomplete) so that I can have a proper look.

      Delete
    2. Great, what a lifesaver... where shall i send the pages to please ? cheers.

      Delete
    3. It's on its way !

      Delete
    4. Found what was wrong, and probably I should have been more clear in the article. The source.asp page should contain only the above code. No head, metas and other stuff. Start your page with <%@LANGUAGE="VBSCRIPT"%> and place the above code... that's all.

      Delete
  5. Dude, This script is great, "You Rock"! I have one question but first let me say I don't have a lot of experience with jquery. I am building a form that contains a dropdown box with all USA states and then under it I have the Cities box that uses your "jquery autocomplete" script. Is there any way to pass the value of the State dropdown value after is has been updated as a second URL Parameter so as to limit the amount of records returned?

    So if you select "Georgia" from the states dropdown I want "jquery autocomplete" in the "City" dropdown to query the cities in Georgia instead of the cities from all 52 states as it does now.

    ReplyDelete
    Replies
    1. Hey! Thanks man.
      What you need to do is a little bit more complex. The autocomplete is working great with just one list box. Now that you have the cities dropdown box with autocomplete... just forget about it :-)
      See this article for linking two listboxes.
      What I would do, is use the autocomplete on the States dropdown and then, according to the above post, link the two listboxes.
      Hope that helps!

      Delete
  6. Hi Marco, I wrote you on 20 September 2012 02:03 once again thanks for your quick reply. I have another request that would functionality to this script. As it is now, the "username" textbox is populated with values based on the "source.asp" page. I need a hidden "userid" textbox that will populate with the records "primary key" when you select the text string in the "username" textbox. The JSON output from source.asp looks like this;

    {"id":"1","value":"A P'S Hideaway"},
    {"id":"2","value":"Acapulco"},
    etc...

    How would you go about extracting the “id” value for the “userid” textbox and still retain the string “value” for the “username” textbox?

    ReplyDelete
    Replies
    1. Hi,

      as I explained just a few comments above, I believe you have to elaborate a bit your code. Because autocomplete is just getting one value from the JSON output, you probably need to link two selectbox: when the a value is selected from the first listbox (with autocomplete) a second listbox is populated with possibile choice (yours is just one). Then, I suppose you can hide the second listbox. Have a look at the following post for ideas...

      Delete
  7. Thank you!

    Very straightforward and helpful tutorial!

    ReplyDelete
  8. Excellent! Great Job Marco.. That made my day!

    It would be great if you had mentioned how to restrict the number of entries listed to say 10, for eg: it is usually not essential to list 100 potential entries from the DB when you type 'a'

    Anyway, thanks a lot!

    ReplyDelete
  9. Not working for me. Have done same things which you have mentioned. Please Help.!!:(

    ReplyDelete
  10. Thank you for an excellent tutorial. I had my auto complete connected to Access and working in no time.

    ReplyDelete
  11. Marco I followed your tutorial and I even used JSon lint to validate my json it says its fine but in the auto complete it only displays bullets no data. I know my source page is quite a bit different because I am pulling in the json dynamically from an include, but I do not think it matters because my json is validated. Could I send you the pages and maybe you have a look at them I have had sever people in my office look at it we are all stumped.

    ReplyDelete
    Replies
    1. It would be better if I can see the page live.

      Delete
    2. unfortunately I am not going to be allowed to publicly publish this yet until it is all fixed. I wouldnt mind sending you all the files even the include to make it easier do you use google drive? so we don't have to email large files!!

      Delete
    3. Wait! Did you say that the Json file is included?
      I think that's the problem. Try without an include: the add on works with an external source.

      Delete
    4. no source is the json file it is just calling back to an include to pull data from. we dynamically reuse classes so basically I am doing everything you are doing just when I am pulling the data from database I am doing it dynamic. but at the end output is the same and jsonlint said it is good json.

      Delete
    5. The Json file is created dynamically with a .asp page in any case. Whatever, send the files, but I'm not sure I can really help looking just at the code. As I said thousand of times, it's difficult when you have just have a portion of a picture to imagine the rest :-)

      Delete
  12. Sorry Marco I sent you a few pictures of what the pages look like I hope that helps if you cant help me thank you for your time though

    ReplyDelete
    Replies
    1. "http://testm.wingfootct.com/tools/tasks/source.asp"
      That is the problem. The source must be on the server, same folder as the default.asp.
      The rest looks good to me.

      Delete
  13. the source page is validated so I am convinced it is in the javascript of the default page I just do not see where. I am new to json so I do not know if it is because I am pulling back to much information from source page or if I need to parse the json somewhere in my code. Maybe it is because I am using jquery mobile? So I am declaring two different jquery and the dom is not loading I am not sure I know if I hardcode json strings in the source they display just how I want them.

    ReplyDelete
  14. I sent you another email they are in the same folder on same server it was doing the same thing as just "source.asp" as it is with the full path I was just trying anything to get it to work.

    ReplyDelete
  15. Ciao Marco, ti scrivo in Italiano perchè non ho molta dimestichezza con l'inglese.
    Ho seguito il tuo tutorial alla lettera ma qualcosa non funziona. La query estrae qualcosa ma l'output è formattato male...
    Se puoi aiutarmi ti mando una email con il link alla pagina di test che ho realizzato.
    Fammi sapere
    Lorenzo
    Lorenzo

    ReplyDelete

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.