Ajax with Classic ASP using jQuery
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"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<%
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
%>
<form>
<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>
</form>
<%
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. 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 GROSELLE-Restaurante:
{"j":[{"__type":"Customer","CompanyName":"GROSELLA-Restaurante","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>
<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('<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].Tel + '</p>');
}
});
}
</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 CusomterID (which is the <select>, 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 <select> 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.
Currently rated 4.51 by 39 people
Rate Now!
Date Posted:
17 January 2009 23:13
Last Updated:
24 January 2009 18:18
Posted by:
Mikesdotnetting
Total Views to date:
19344
Printer Friendly Version
Comments
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?
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