Ajax With Classic ASP Using jQuery

Ajax with Classic ASP using jQuery http://www.mikesdotnetting.com/Article/98/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.

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 - 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 is one difference - apart from the use of jQuery - in that the data relating to the company details will be generated as a JSON string, rather than a snippet of html. But let's start with the page that the user will see:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<%

strConn = "Data Source=127.0.0.1;Initial Catalog=NorthWind;Integrated Security=SSPI;" 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 Customer -- <% for i = 0 to Ubound(arrCustomer,2) Response.Write "" Response.Write arrCustomer(1,i) & "" & VbCrLf  next

%>

<% Else rs.Close : Set rs = Nothing : Conn.Close : Set Conn = Nothing Response.Write "

Something bad went wrong

"

End If  %>



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 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 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. This will  be a separate .asp file called FetchCustomers.asp:

<%@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() %>

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 JSON string is constructed from the record. All the doubling of  quotes in the VBScript code makes it difficult to see exactly what the format of the output will be, so here's how it would appear if the record was for GROSELLERestaurante:

{"j":[{"__type":"Customer","CompanyName":"GROSELLARestaurante","Address":"5ª Ave. Los Palos Grandes", "City":"Caracas","Region":"DF","PostalCode":"1081","Country":"Venezuela","Tel":"(2 ) 283-2951"}]}

This is a Javascript object, which I have called j, which contains one property. The  property is an array of nested Javascript objects. This array only contains one element or 

object, which has a number of properties with their values set. Now it's time to look at the Javascript that will be responsible for calling the page, passing the querystring values and managing the JSON that's returned in the Response:

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"> <script type="text/javascript"> $(function() { $('#CustomerID').change(getCustomer); });

function getCustomer() {

$.ajax({ type: "GET", url: "FetchCustomer.asp", data: "CustomerID=" + $('#CustomerID').val(), contentType: "application/json; charset=utf-8", dataType: "json", success: function (response) { var customer = (typeof response.j) == 'string' ? eval('(' + response.j + ')') : response.j; $('#CustomerDetails').empty(); $('#CustomerDetails').append('

' + customer[0].CompanyName + '
' + customer[0].Address + '
' + customer[0].City + '
' + customer[0].Region + '
' + customer[0].PostalCode + '
' +

customer[0].Country + '
' + 'Tel: ' + customer[0].Tel + '

'); } }); }

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 CusomterID (which is the , and adds an event handler to the onchange event. In this case, the getCustomer() function is called. Then the getCustomer() function is defined. Using jQuery's built-in AJAX functionality, a GET request is prepared which calls the FetchCustomer.asp page. The selected value from the list is passed in as a QueryString parameter. If the call is successful, the Response is first validated then eval() is used to deserialise the JSON string back into Javascript objects. From there, the div with the id "CustomerDetails" is cleared of any existing content, and the properties of the customer object are written to the div instead. Whether you return JSON or formatted html is up to you. JSON carries a significantly smaller overhead in terms of payload over the network, but requires more code to manage on the client. Sometimes you may have no choice, for example if you are usig an external web service that returns JSON. In this case, as you have no control over the returned content, you should not use eval() to deserialise the JSON string. If you dig around among the jQuery plugins, you will find some that have been created purely to validate and deserialise JSON of unknown origin safely.

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()); }); });

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('

' + s[0].LocationName + '
' + s[0].locationID + '
' + s[0].City + '
' +

'Tel: ' + s[0].phone + '

');

}

});

}

$('#dialog').dialog('open'); return false; });

/*$('#dialog').dialog('close') end of click method */

}); /* end document.ready */

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.

×

Report "Ajax With Classic ASP Using jQuery"

Your name