Zip Codes and Data Entry
A common aspect of almost any website is a page where a user must enter an address. In the US we have these nifty things called Zip Codes (other countries have similar systems) that map to a city, state, and county. If a user knows the Zip Code of an address, an application should be able to lookup the corresponding city, state, and county for that address, thus keeping the user from having to enter it. Few thick-client and almost no thin-client applications actually implement this feature, however. The following is a description of how to implement this Zip Code information lookup feature for a website.
Database
In order to implement a Zip Code information lookup feature we need a database or XML file that contains the Zip Codes, Cities, Counties, and States in the US. This example uses a database table called ZipCode that has a ZipCode, a City, a State, and a County column. This example also uses a County table that contains a State and a County Name column.
The User Interface
The following is a screenshot of the HTML page that is the user interface for this example. In this example when a user enters a valid Zip Code, the City, State, and County fields will automatically be populated.
The following is the HTML used to create this form (the list of states has been truncated to save space):
<FORM name=frmMain id=frmMain>
<P>
<TABLE BORDER=0 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD NOWRAP><label for=txtCity>City:</label></TD>
<TD NOWRAP><label for=cboState>State:</label></TD>
<TD NOWRAP><label for=cboCounty>County:</label></TD>
<TD NOWRAP><label for=txtZip>Zip:</label></TD>
</TR>
<TR>
<TD NOWRAP><INPUT id=txtCity name=txtCity maxlength=25 tabindex=2></TD>
<TD NOWRAP><SELECT id=cboState name=cboState style="WIDTH: 50px" onchange="LoadCounties()" tabindex=3>
<option></option>
<option value="AK">AK</option>
…
<option value="WY">WY</option>
</SELECT></TD>
<TD NOWRAP><SELECT id=cboCounty name=cboCounty style="WIDTH: 225px" tabindex=4><OPTION value=-1></OPTION></SELECT></TD>
<TD NOWRAP><INPUT id=txtZip name=txtZip maxlength="5" size="5" onblur="LookupZip()" tabindex=1></TD>
</TR>
</TABLE>
</P>
<P>
<INPUT type="BUTTON" onClick="ClearForm()" value=Clear tabindex=5>
</P>
</FORM>
Dynamic County List
The first thing to note in the HTML example above is only the State option list is populated. The County list is empty. Rather than download all of the counties for all of the states when the page loads, this page only downloads the counties for the select State when the State changes. The following JavaScript is used to dynamically load the County list:
//Removes all content from combo box
function RemoveAll(ComboBox)
{
if (ComboBox == null)
return;
ComboBox.selectedIndex = -1;
var ComboBoxLength = ComboBox.options.length;
for (var i = ComboBoxLength - 1; i >= 0; i--)
ComboBox.options.remove(i);
}
function LoadCounties()
{
var objHTTP;
var szURL = "GetCounties.asp?State=" +
document.frmMain.cboState.value;
var szHttpMethod = "GET";
try
{
objHTTP = new ActiveXObject("Microsoft.XMLHTTP");
objHTTP.Open(szHttpMethod, szURL, false);
objHTTP.Send(null);
}
catch (errorObject)
{
alert("The following error occurred loading the Counties:\n" +
"Error Code: " + errorObject.number + "\n" +
"Error Description: " + errorObject.description);
return;
}
if (objHTTP.status != 200)
{
alert("The following error occurred loading the Counties:\n" +
"Status: " + objHTTP.status + " " + objHTTP.statusText);
return;
}
// Load XML
var objXmlDOM = objHTTP.responseXML;
// Find All Counties
objXmlDOM.setProperty("SelectionLanguage", "XPath");
var objCounties = objXmlDOM.selectNodes("/Counties/County");
// Remove existing counties
RemoveAll(document.frmMain.cboCounty);
// Create new options
for (var i = 0; i < objCounties.length; i++)
{
var objOption = document.createElement("option");
objOption.text = objCounties[i].getAttribute("Name");
objOption.value = objCounties[i].getAttribute("Name");
document.frmMain.cboCounty.add(objOption);
}
}
The RemoveAll function removes all existing options from the list. The items are removed in reverse order to prevent the screen flicker that occurs in the control if they are removed in forward order. The LoadCounties function uses the Microsoft.XMLHTTP object to call the GetCounties.asp page. The GetCounties.asp page returns an XML document containing the names of all the counties in a give state. The LoadCounties function uses this data to create the county options. The following is the source code used in the GetCounties.asp page:
<%@ Language=VBScript %>
<%
Option Explicit
Response.Expires = 0
Response.CacheControl = "no-cache"
Response.AddHeader "pragma", "no-cache"
Response.ContentType = "text/XML"
' Constants
Const adUseClient = 3 ' As Integer
Const adCmdStoredProc = 4 ' As Integer
Const adExecuteStream = 1024 ' As Integer (&H400)
Const strConnectionString = "<Your DB Connection String Here>"
' Variables
Dim objDatabaseConnection ' AS ADODB.Connection
Dim objCommand ' AS ADODB.Command
Dim objStream ' As ADODB.Stream
' Begin
' Create Database Object
Set objDatabaseConnection = Server.CreateObject("ADODB.Connection")
' Open Connection
objDatabaseConnection.CursorLocation = adUseClient
objDatabaseConnection.Open strConnectionString
' Create Database Command
Set objCommand = Server.CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objDatabaseConnection
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "GetCountiesXml('" & Left(Request.QueryString("State"), 2) & "')"
' Set up the Stream object
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
' Tell the Command object to use the Stream.
objCommand.Properties("Output Stream").Value = objStream
' Execute the command
objCommand.Execute , , adExecuteStream
' Return stream results
Response.Write "<?xml version=""1.0"" ?>"
Response.Write objStream.ReadText
' Clean Up
objStream.Close
Set objStream = Nothing
Set objCommand = Nothing
objDatabaseConnection.Close
Set objDatabaseConnection = Nothing
Response.End
%>
This page calls the GetCountiesXml stored procedure on the database and returns the results to the caller. The following is the GetCountiesXml stored procedure:
-- Description: Returns an XML document containing the list of
-- Counties for a given State.
-------------------------------------------------------------------
CREATE PROCEDURE [dbo].[GetCountiesXml]
@State CHAR(2) = ''
AS
BEGIN
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [Counties!1],
NULL AS [County!2!Name]
UNION
SELECT 2 AS Tag,
1 AS Parent,
NULL AS [Counties!1],
'' AS [County!2!Name]
UNION
SELECT 2 AS Tag,
1 AS Parent,
NULL AS [Counties!1],
Name AS [County!2!Name]
FROM County
WHERE State = @State
ORDER BY [County!2!Name]
FOR XML EXPLICIT
END
The following is an example XML document generated by the GetCounties.asp page:
<?xml version="1.0" ?>
<Counties>
<County Name=""/>
<County Name="HAWAII"/>
<County Name="HONOLULU"/>
<County Name="KAUAI"/>
<County Name="MAUI"/>
</Counties>
This technique reduces the amount of data that has to be downloaded to the client when the page is first loaded, thus speeding up the page. It also reduces the overall network traffic.
Auto-filled City, State, and County
The next thing to note about the HTML code shown above is that when the user leaves the txtZip element, the LookupZip method is called. The code for this method is shown below:
function LookupZip()
{
var strZipCode;
var ZipCodeRegularExpression;
ZipCodeRegularExpression = new RegExp("^\\d{5}$");
strZipCode = document.frmMain.txtZip.value;
if ((strZipCode.match(ZipCodeRegularExpression) != null) &&
((document.frmMain.txtCity.value.length == 0) ||
(document.frmMain.cboState.value.length == 0) ||
(document.frmMain.cboCounty.value.length == 0)))
{
var objHTTP;
var szURL = "GetZipInfo.asp?ZipCode=" + strZipCode;
var szHttpMethod = "GET";
try
{
objHTTP = new ActiveXObject("Microsoft.XMLHTTP");
objHTTP.Open(szHttpMethod, szURL, false);
objHTTP.Send(null);
}
catch (errorObject)
{
// Ignore Errors
return;
}
if (objHTTP.status != 200)
{
// Ignore Errors
return;
}
// Load XML
var objXmlDOM = objHTTP.responseXML;
objXmlDOM.setProperty("SelectionLanguage", "XPath");
// Find City
var objCity = objXmlDOM.selectSingleNode("/ZipCodeInfo/City")
if (objCity != null)
{
if (document.frmMain.txtCity.value.length == 0)
{
document.frmMain.txtCity.value = objCity.text;
}
} // Find City
// Find State
var objState = objXmlDOM.selectSingleNode("/ZipCodeInfo/State")
if (objState != null)
{
if (document.frmMain.cboState.value.length == 0)
{
document.frmMain.cboState.value = objState.text;
// Reset counties
LoadCounties();
}
} // Find State
// Find County
var objCounty = objXmlDOM.selectSingleNode("/ZipCodeInfo/County")
if (objCounty != null)
{
if ((document.frmMain.cboCounty.value.length == 0) &&
(document.frmMain.cboState.value == objState.text))
{
document.frmMain.cboCounty.value = objCounty.text;
}
} // Find County
} // if ((strZipCode.match(ZipCodeRegularExpression) != null)
}
This method first verifies the Zip Code entered is a five-digit string. Then it uses the Microsoft.XMLHTTP object to call the GetZipInfo.asp page which returns an XML document containing the City, State, and County for the given Zip Code. It then fills in the City, State, and County elements on the form with the data returned. Only blank fields are populated. Unlike the LoadCounties function, the LookupZip function is not critical to the operation of the page, so it ignores all errors. The GetZipInfo.asp page is pretty much the same as the GetCounties.asp page with the exception that is calls the GetZipInfo stored procedure instead of the GetCountiesXml stored procedure. The following is the code for the GetZipInfo stored procedure:
-- Description: Returns an XML document containing the City,
-- State, and County for a given Zip Code.
-------------------------------------------------------------------
CREATE PROCEDURE [dbo].[GetZipInfo]
@ZipCode CHAR(5)
AS
BEGIN
SELECT TOP 1 City, County, State
FROM ZipCode AS ZipCodeInfo
WHERE ZipCode = @ZipCode
FOR XML AUTO, ELEMENTS
END
The following is an example XML document generated by the GetZipInfo.asp page:
<?xml version="1.0" ?>
<ZipCodeInfo>
<City>HAZELWOOD</City>
<County>SAINT LOUIS</County>
<State>MO</State>
</ZipCodeInfo>
Caveats
- The version shown in this example only works for IE. This can be done in the other browsers (Firefox, Opera, etc.) using the XMLHttpRequest object instead of the Microsoft.XMLHTTP object.
- There may be security issues associated with receiving data in client-side JavaScript. This technique may be appropriate for intranet sites, but not for internet sites depending on the security requirements of the system.
