Monday 21 May 2012

ASP & jQuery: linked list boxes with AJAX

   


Today, I would like to share a quick and easy solution to link 2 list boxes with jQuery and AJAX.
Situation: we have two list boxes. One (number) is already populated. The second (letter) is populated according to what is selected on the first list box, via AJAX (not reloading the page).
We need to build two ASP pages.
Let's start and have fun!

The main page
The main page will have the relevant jQuery code and the HTML.
We will use jQuery and specifically its AJAX API. We first link to the library and then create a function triggered by a change in the first list box:
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
$(".number").change(function()
{
var id=$(this).val();
var dataString = '?id='+ id;

$.ajax
({
type: "POST",
url: "source.asp"+dataString,
cache: false,
success: function(html)
{
$(".letter").html(html);
}
});

});

});
</script>
The above snippet should be place in the head of your main page. What is the function doing? It is fetching data (in the form of HTML tags) from another page (which we will create afterwards and which we will call "source.asp"). In order to retrieve the information we need, we are passing a parameter (ID). This parameter is taken from the first list box and appended to the url (so that our request will be "source.asp?id=1" for example).
I believe that the code is simple enough. No strange or magic things there.
In the body of the page we insert the two list boxes. As said, one is already populated. The second is empty.
Number:
<select name="number" class="number">
<option selected="selected">--Select--</option>
<option value="1">1</option>
<option value="2">2</option>
</select> <br/><br/>
Letter:
<select name="letter" class="letter">
</select>
When something changes in the "number" list box, the jQuery function is triggered and the second list box is populated. As said that is done via AJAX, calling the "source.asp" page. So we need to build the "source.asp" page.

The source.asp page
The second asp page is very simple. It fetches data from the database and create a simple list of <option> tags which will be inserted inside the second <select> in the main page. At the beginning of the page, we query the database:
<%
Dim PopDP
Dim PopDP_numRows

Set PopDP = Server.CreateObject("ADODB.Recordset")
PopDP.ActiveConnection = yourconnectionstring
PopDP.Source = "SELECT * FROM lettersTable WHERE id = " + Replace(Request.QueryString("id"), "'", "''") + ""
PopDP.CursorType = 0
PopDP.CursorLocation = 2
PopDP.LockType = 1
PopDP.Open()

PopDP_numRows = 0
%>
As usual, the above is just an example, so please remember to insert your connection string (yourconnectionstring) and your letters table (lettersTable).
In the body of your page, we insert the <option> tags using the data from the above recordset:
<%While (NOT PopDP.EOF)%>
    <option value="<%=(PopDP.Fields.Item("id").Value)%>"><%=(PopDP.Fields.Item("id").Value)%></option>
<%
  PopDP.MoveNext()
  Wend
   If (PopDP.CursorType > 0) Then
     PopDP.MoveFirst
   Else
     PopDP.Requery
   End If
%>
Again, nothing special here. Finally, we close the connection:
<%
PopDP.Close()
Set PopDP = Nothing
%>
Save your page as "source.asp".

Now you're ready to test the example.

Some further considerations
The first list box is not dynamically populated. If you want to do so, just fetch the data from your database.
In the example we use the ID field as filter. It is obvious that all the fields used in the example are just because I need to show you "something". Please be sure to use your field names, connection, table names etc.

Ok. I believe that's all. Simple, straight forward and effective.
As usual, please share your thoughts!

20 comments:

  1. Hi Marco, I posted to "ASP & jQuery UI: autocomplete with json source (part: 2)" and took your suggestion to try the script for the article you wrote "for linking two listboxes" but I am struggling on this one. The source.asp seems to work fine as I can see the results by appending the "id" and a value in the querystring. I created the main page from your instructions but selecting the number box has no effect on the letters box. Can you offer any suggestions?

    ReplyDelete
    Replies
    1. The source.asp file should not contain any HTML, just the code mentioned in the article.

      Delete
    2. Once again, dude you "Rock", that was it. You have given us ammo that blows new life into Classic ASP. May God Bless you, May God Keep you, and May God Shine His Face upon you.

      Delete
    3. Thanks Shammock. That's too kind of you...

      Delete
  2. got this to work OK, but now working on if possible a 3rd select box based on the 2nd results? How would I go about doing that. Thanks

    ReplyDelete
  3. As well how would i add these list books to a form to post the data to another page?

    ReplyDelete
    Replies
    1. Build a form around the two list boxes and place a submit button.

      Delete
  4. I have tried that but it doesn't show anything after the 2 list boxes. eg...

    The button doesn't show up why?

    Here is the page... http://www.euroadventures.net/list-box-search.asp

    how can I send you the code HTML

    Thanks alot if you can help.

    ReplyDelete
  5. Sorry I am a new to all this. Thanks, Sean

    ReplyDelete
  6. I think I found the problem. I will get back to you. thanks, sean

    ReplyDelete
  7. got it to work was an error in the code I made... http://www.euroadventures.net/list-box-search.asp

    I am hoping now to be able to add a 3rd box and as well if I can have the per-selected but I think that will be a big task.

    ReplyDelete
  8. Hi Marco,
    The above code worked like magic. I was struggling to find a good working example - but your code and explanation was great!
    Thanks for your post and also keep up the good work.
    SH

    ReplyDelete
  9. how do i get back the id value in the main page ?

    ReplyDelete
  10. hi,
    i want the query string value to be passed again to the next select..

    if i am searching for country in the first select from the database.. i want to search for country and state in the next select and again countr, state and district in the next select..

    ReplyDelete
    Replies
    1. There's another post on that. Linked drop box.

      Delete
  11. hi,
    can u give me the link of the post.. for linked drop box..

    ReplyDelete

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

However, I do answer to all the comments.