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.
