Ajax with Classic ASP using jQuery

My simple article on Ajax with Classic ASP is one of the most popular on this site. So I thought it's about time I updated it to show how to use jQuery to Ajaxify a Classic ASP page. Since I did that, the jQuery version became even more popular but needed to be brought up to date. This latest version uses a couple of suggestions that have been provided by commentors to improve the code. I have also added a download which contains all the code needed to run the samples.

First of all, why use jQuery when the previous article works? Well, jQuery is a library that is designed to help web developers work with Javascript in a much more streamlined way. Internally, it handles a lot of the nonsense that developers have to work with in terms of cross-browser incompatibilities and it's syntax and chaining abilities generally results in far less code being written. A lot more information about jQuery can be found here along with the downloads.

The scenario I shall use will stay the same as previous examples- an initial Select box containing the Company Names from the Northwind database, with the address and other details being retrieved asynchronously when a company is selected. These will be displayed in a specific area on the page. There are two approaches shown here - one shows the AJAX repsonse being generated as a snippet of HTML, and the other shopws the response being generated as JSON using a third party ASP library. But let's start with the page that the user will see:

<% @LANGUAGE="VBSCRIPT" CODEPAGE="65001" %>
<!DOCTYPE html>

<html lang="en">
<head>
  <meta charset="utf-8" />
  <title>Untitled Document</title>

</head>
 
<body>
<%
  strConn = "Provider=SQLNCLI10;Server=localhost;Database=NorthWind;Trusted_Connection=Yes;"
  Set Conn = Server.CreateObject("ADODB.Connection")
  Conn.Open strConn
  Set rs = Conn.Execute("SELECT [CustomerID], [CompanyName] FROM [Customers]")
  If Not rs.EOF Then
    arrCustomer = rs.GetRows
    rs.Close : Set rs = Nothing : Conn.Close : Set Conn = Nothing
%>
  <select name="CustomerID" id="CustomerID">
  <option> -- Select Customer -- </option>
<%   
    For i = 0 To Ubound(arrCustomer,2)
      Response.Write "<option value=""" & arrCustomer(0,i) & """>"
      Response.Write arrCustomer(1,i) & "</option>" & VbCrLf
    Next
 
%>
  </select>
<% 
  Else 
    rs.Close : Set rs = Nothing : Conn.Close : Set Conn = Nothing
    Response.Write "<p>Something bad went wrong</p>"
  End If 
%> 
<div id="CustomerDetails"></div>
 
</body> 
</html>

The VBScript connects to a local SQL Server Northwind database and obtains the ID and the Company Name for all the Customers. Assuming that they were retrieved succesfully, they are placed in an array through the RecordSet.GetRows() method. The array is iterated through, and <option> elements are dynamically added to the page with the ID as the value, and the CompanyName as the text that the user sees. In the original example, the <select> had an onchange event handler hard-coded in it. This time it doesn't. jQuery is all about "unobtrusive" Javascript and has a nice way to manage the registration of an event handler with an html element. But before we get to the Javascript, we'll deal with the code that returns individual Customer Details as a snippet of HTML. This will be a separate .asp file called FetchCustomers.asp:

<% @LANGUAGE="VBSCRIPT" CODEPAGE="65001" %>

<% 
  strConn = "Provider=SQLNCLI10;Server=localhost;Database=NorthWind;Trusted_Connection=Yes;"
  Set Conn = Server.CreateObject("ADODB.Connection")
  Conn.Open strConn
  query = "SELECT * FROM Customers WHERE CustomerID = ?"
  CustomerID = Request.QueryString("CustomerID")
  arParams = array(CustomerID)
  Set cmd = Server.CreateObject("ADODB.Command")
  cmd.CommandText = query
  Set cmd.ActiveConnection = Conn
  Set rs = cmd.Execute(,arParams,1)
  If Not rs.EOF Then
    Response.Write "<p><strong>" & rs("CompanyName") & "</strong><br />" & _
      "Address: " & rs("Address") &  "<br />" & _
      "City: " & rs("City") & "<br />" & _
      "Region: " & rs("Region") & "<br />" & _
      "PostalCode: " & rs("PostalCode") & "<br />" & _
      "Country: " & rs("Country") & "<br />" & _
      "Tel: " & rs("Phone") & "</p>"
  End If
  rs.Close : Set rs = Nothing : Set cmd = Nothing : Conn.Close : Set Conn = Nothing
  Response.End()
%>

This is a fairly standard piece of VBScript data access. It connects to the database and retrieves the company record associated with the CustomerID value passed in the QueryString. It uses parameters to protect against any chance of SQL Injection. If successfully retrieved, a snippet of HTML is generated. The following code will go just before the closing </head> tag:

    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
    <script type="text/javascript">
        $(function() {
            $('#CustomerID').change(function(){
                $('#CustomerDetails').load('FetchCustomer.asp?CustomerID=' + $('#CustomerID').val());
            });
        });
    </script>

After linking to the minimised jQuery file that's available from Google Code, we get to the script that it specific to the page. The first instruction finds the element with the id of CustomerID which is the <select>, and adds an event handler to the onchange event. Within that handler, the jQuery load command is used. This "loads" the response into the element it is called on (the CustomerDetails div). The response is obtained from the URL which is the parameter passed into the method.

One of the commentors - marlin - pointed to a resource apparently called "QueryToJson", which I found is a method of the ASPJson Project. This code library is built using VBScript and is a JSON serializer. I haven't tested it thoroughly, but I managed to get it to work quite easily for this article. To use it, you need to download the JSON_2.0.4.asp file and include that in your code. There is also a utility file (JSON_UTIL_0.1.1.asp) which contains a function to serialize an ADO RecordSet to JSON. Problem is that the function expects SQL and a connection. It doesn't cater very well for parameters or dispose of the RecordSet obejct it creates. So I have added an amended version of the function to the top of the script which gets the data and returns it to the JSON Serializer. Here is the file contents:

<% @LANGUAGE="VBSCRIPT" CODEPAGE="65001" %>
<!--#include file="JSON_2.0.4.asp"-->
<%
Function QueryToJSON(dbcomm, params)
        Dim rs, jsa
        Set rs = dbcomm.Execute(,params,1)
        Set jsa = jsArray()
        Do While Not (rs.EOF Or rs.BOF)
                Set jsa(Null) = jsObject()
                For Each col In rs.Fields
                        jsa(Null)(col.Name) = col.Value
                Next
        rs.MoveNext
        Loop
        Set QueryToJSON = jsa
        rs.Close
End Function
%>
<% 
  strConn = "Provider=SQLNCLI10;Server=localhost;Database=NorthWind;Trusted_Connection=Yes;"
  Set conn = Server.CreateObject("ADODB.Connection")
  conn.Open strConn
  query = "SELECT * FROM Customers WHERE CustomerID = ?"
  CustomerID = Request.QueryString("CustomerID")
  arParams = array(CustomerID)
  Set cmd = Server.CreateObject("ADODB.Command")
  cmd.CommandText = query
  Set cmd.ActiveConnection = conn
  QueryToJSON(cmd, arParams).Flush
  conn.Close : Set Conn = Nothing
%>

The only change needed to the original calling page is the second <script> block which now makes use of the jQuery getJson command and constructs the HTML there from the result:

    <script type="text/javascript">
        $(function() {
            $('#CustomerID').change(function(){
                $.getJSON('CustomerJson.asp?CustomerId=' + $('#CustomerID').val(), function(customer) {
                    $('#CustomerDetails').empty();
                    $('#CustomerDetails').append('<p><strong>' + customer[0].CompanyName + '</strong><br />' +
                             customer[0].Address + '<br />' +
                             customer[0].City + '<br />' +
                             customer[0].Region + '<br />' +
                             customer[0].PostalCode + '<br />' +
                             customer[0].Country + '<br />' +
                   'Tel: ' + customer[0].Phone + '</p>');
                });
            });
        });
    </script>

Do you need JSON? Well, if you are looking at using the new templates in jQuery, you will for a start. Is this little library up to the job? As I said earlier, I haven't tested it thoroughly but I have identified a couple of weaknesses which were relatively simple to put right. JSON is just a string. Here's how it looks if the customer selected is White Clover Markets:

[
	{
    	"CustomerID":"WHITC",
        "CompanyName":"White Clover Markets",
        "ContactName":"Karl Jablonski",
        "ContactTitle":"Owner",
        "Address":"305 - 14th Ave. S. Suite 3B",
        "City":"Seattle",
        "Region":"WA",
        "PostalCode":"98128",
        "Country":"USA",
        "Phone":"(206) 555-4112",
        "Fax":"(206) 555-4115"
    }
]

I have formatted the JSON so that it is easier to see its structure, but it isn't difficult to serialize simple objects like that to JSON manually in code. Where the library may help is in serializing more complex structures.

The download is in a format that's easy to work with using WebMatrix. If you have already installed WebMatrix, just unzip the folder, right-click and choose "Open as web site with Microsoft WebMatrix". If you haven't installed WebMatrix yet, do so.

Date Posted: 17 January 2009 23:13
Last Updated: 28 April 2011 11:38
Posted by: Mikesdotnetting
Total Views to date: 104775

Comments

06 May 2009 14:07 from frederic

Hi,

I rather like to use your other "coding". I've tried this one out and it works like a charm.. But what happens if you have more entries?

for ex. : 2 customers to show => Do you have to work as {"j":[{"ID":"1"}],[{"ID":"2"}]} ???

And what if you have a "BIG" text to show with Quotes and such?

Kind Regards,
Frederic

07 May 2009 21:00 from Mikesdotnetting

@Frederic

Actually in hindsight, there is a much easier way to manage this altogether. Rather than using VBScript to generate JSON, you can use it to generate html, and then simply use the jQuery load() method:

$(document).ready(function() {
$('#CustomerID').change(function() {
$('#CustomerDetails').load("FetchCustomer.asp?CustomerID=" + $('#CustomerID').val());
});
});

14 May 2009 14:14 from Srinivas

Hi,
One thing I would like to know here the classic ASP response is html. But why you use JSON for this activity.

Let me know the response type I can use is "html" or not?

I appreciate if you have more samples on Classic ASP, JQuery and SQL Server Database.

Thanks
Srinivas

15 May 2009 07:40 from Mikesdotnetting

@Srinivas

Please read the reply I made to Frederic's comment.

17 December 2009 13:58 from David

Can the VBScript that gets the customer detail be in a VBScript function in the same .asp file or does it have to be in a separate .asp file. Everything I've read so far about using jquery built-in AJAX functions and jquery AJAX plugins shows the server-side code that gets the data as a separate .asp file.

Thanks.

17 December 2009 18:50 from Mikesdotnetting

@David

You can't make calls to individual functions within an asp file. When you make a request for a resource that ends in .asp, the entire file is executed, which means that all the other gubbins is executed and sent as part of the response. That's why you need a separate file.

ASP.NET is different. In that, you have not only web methods, but also Page Methods. The second of those is much closer to what you hope to achieve within classic ASP - a method in a page file that can be called and executed on its own, leaving all the other stuff in the page file alone.

10 February 2010 21:23 from edgardo

I do not understand, I wonder where goes jabascript and ASP code
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

<%

strConn = "Data Source=127.0.0.1;Initial Catalog=NorthWind;Integrated Security=SSPI;"

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open strConn

query = "SELECT * FROM Customers WHERE CustomerID = ?"

CustomerID = Request.QueryString("CustomerID")

arParams = array(CustomerID)

Set cmd = Server.CreateObject("ADODB.Command")

cmd.CommandText = query

Set cmd.ActiveConnection = Conn

Set rs = cmd.Execute(,arParams,1)

If Not rs.EOF Then

Response.Write "{""j"":[{""__type"":""Customer""," & _

"""CompanyName"":""" & rs("CompanyName") & """," & _

"""Address"":""" & rs("Address") & """," & _

"""City"":""" & rs("City") & """," & _

"""Region"":""" & rs("Region") & """," & _

"""PostalCode"":""" & rs("PostalCode") & """," & _

"""Country"":""" & rs("Country") & """," & _

"""Tel"":""" & rs("Phone") & """}]}"

End If

rs.Close : Set rs = Nothing : Set cmd = Nothing : Conn.Close : Set Conn = Nothing

Response.End()

%>

or file names if they are separately.
thanks

12 February 2010 20:53 from Mikesdotnetting

@ edgardo

I believe your questions are answered within the article.

15 March 2010 05:45 from Craig Derington

This has been extremely useful. Let's say I want the JSON data to be returned to a jquery ui dialog. I then need for my opener click event to show the returned data in the div store details. Here is my code. It's not returning the json data to my container. Check this out...


<script type="text/javascript">

$(document).ready(function(){                                          

    $('#dialog').dialog({

	autoOpen: false,

	show: 'blind',

	hide: 'blind'

	});

        /* end #dialog */



   /* do some processing and add the following to a click method */

   $('#opener').click(function() {

			

	function getStore() {

  	$.ajax({

	  type: "GET",

	  url: "aspToJSON.asp",

    	  data: "sidID=" + $('#opener').val(),

    	  contentType: "application/json; charset=utf-8",

    	  dataType: "json",

    	  success: function(response) {

      	  var s = (typeof response.p) == 'string' ? eval('(' + response.p + ')') : response.p;

          $('#storeDetails').empty();

       	  $('#storeDetails').append('<p><strong>' + s[0].LocationName + '</strong><br />' +

                 s[0].locationID + '<br />' +

                 s[0].City + '<br />' +

                 'Tel: ' + s[0].phone + '</p>');



    		}



  	    });



	}

			 

	 $('#dialog').dialog('open');

			return false;

   });

    

	 /*$('#dialog').dialog('close')

    end of click method */



}); 

/* end document.ready */

</script>

20 March 2010 09:18 from Mikesdotnetting

@Craig

I'm not clear what you are trying to do, but you will get a quicker response and help if you post your question to a classic ASP forum, or a jQuery one.

30 March 2010 22:51 from marlin

Here's something that you might find useful: QueryToJSON which runs in Classic ASP. I'm using 2.0.2. One you include it your database queries look like this:


	strSQL = "select  datepart(m, TheDate) as month, datepart(d, TheDate) as day, datepart(yyyy, TheDate) as year, AllRoomsBookedClosed, 

AllRoomsClosedToArrival, AllMinimumStay from BERoomAvail where  HotelId = " & IHHID & " order by theDate"

	QueryToJSON(objConn, strSQL).Flush

That .Flush sends the output via a Response.write in JSON. I just started using JQuery and there may be an issue with how JQuery expects it's JSON to appear so I haven't gotten JQuery to parse the JSON but the browsers like it fine so you can always do a web browser eval() on QueryToJSON's output.

02 April 2010 15:48 from Sean

I am new to jQuery so I appreciate this article, though I'm running into a problem when trying to pull html code as you described in your answer to frederic. When I set up my page that way nothing happens -- no errors or anything. I am expecting at least some change since I set the ASP page I called to Response.Write "test" and still nothing. Is there something else that I need to know when using that code?

Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

Not relevant to the article
Gratuitous links to your own site or product
Anything abusive or libellous
Spam
Anything in a language I don't understand (English is best)

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Your name:

Your email address:

Your comments:

9 + 6

Mikesdotnetting

Tags

My Book

Blogs

Ajax with Classic ASP using jQuery

Comments