TexasWebDevelopers Home Page

AJAX "key-up" to check username availability from Excel spreadsheet data source

This form sends a request to a database every time there is a "KeyUP".
It uses the HTTP RequestObject and javascript.
Using AJAX the form sends the information via a query string
and "talks" to the Excel data source without reloading the page.

I've added several usernames to an Excel spreadsheet used as the data source to check against this form field entry:
group14
rsmith
jgould
mmacnaughton
Typing in one of the above names will return "USERNAME ALREADY TAKEN"

Here is the code for the database connection to show you how easy it is (this page is named "ajax_username.asp") —

<%
Set username = Request.QueryString("username")
Set DBConn = Nothing
Set DBConn = Server.CreateObject("ADODB.Connection")
DBconn.CommandTimeout = 0
ConnectStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\your-path\users.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
DBConn.Open ConnectStr
SQL = "SELECT * FROM [sheet1$] WHERE usernames='" & username & "' "
Set chk_username = Server.CreateObject("ADODB.Recordset")
chk_username.Open SQL, DBconn, 3, 3
If chk_username.EOF = False then
response.write "USERNAME ALREADY TAKEN"
Else
response.write "USERNAME IS AVAILABLE"
End if
chk_username.close
set chk_username = nothing
DBconn.close
set DBconn = nothing
%>

And here is the code for the form field (This page may be named anything you like) —

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title></title>
<script language="javascript">
function OnChangedUsername()
{
if(document.form1.newuserid.value == "")
{
document.form1.btnCheckAvailability.disabled = true;
}
else
{
document.form1.btnCheckAvailability.disabled = false;
}
}
function createRequestObject() {
var ro;
var browser = navigator.appName;
if(browser == "Microsoft Internet Explorer"){
ro = new ActiveXObject("Microsoft.XMLHTTP");
}else{
ro = new XMLHttpRequest();
}
return ro;
}
var http = createRequestObject();
function sndReq() {
http.open('get', 'ajax_username.asp?username='+document.form1.newuserid.value);
http.onreadystatechange = handleResponse;
http.send(null);
}
function handleResponse() {
if(http.readyState == 4){
var response = http.responseText;
var update = new Array();
if(response.indexOf('|' != -1)) {
update = response.split('|');
document.getElementById("username_chk").innerHTML = update[0];
}
}
}
</script>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form method="post" action="javascript:void(0);" name="form1">
<table>
<tr>
<td><input type="newuserid" name="newuserid" id="newuserid" size="20" onKeyUp="sndReq();" /></td>
</tr>
<tr>
<td><input id="btnCheckAvailability" type="button" disabled="disabled" value="Check Availability" onClick="sndReq();"></td>
</tr>
<tr>
<td><div ID="username_chk"></div></td>
</tr>
</table>
</form>
</body>
</html>