Another remote scripting example
Because we're working with JavaScript on the client and ASP on the server, we can put JavaScript's even handling methods and ADO on the server side to good use and create another remote scripting example.
We're going to create a HTML form that contains one text box and a list box. We will also create an SQL Server 2000 database that contains a list of people. When the user enters some text into the text box, we will capture this event using the onKeyUp event and use remote scripting to get a list of people from a database whose names contain the text entered into the text box.
Our example will use two files: people.html (which will hold the HTML form) and people.asp (which will retrieve the people from the database). Both files will implement remote scripting to make our example possible.
[Note] All of the files that are required for the examples displayed in this article are available for download from the support material for this article. [End Note]
Start by creating a new SQL Server 2000 database called people. Inside the people database, create a table called workers. Use query analyzer and the following code to do so (I am assuming that SQL Server 2000 is installed locally):
create database people
go
use people
go
create table workers
(
workerId int identity(1, 1) primary key,
firstName varchar(20),
lastName varchar(20),
)
After you've created the people database, add some records to the workers table:
insert into workers(firstName, lastName) values('Fred', 'Smith')
insert into workers(firstName, lastName) values('John', 'Fitzpatrick')
insert into workers(firstName, lastName) values('Roger', 'Eggerton')
insert into workers(firstName, lastName) values('Russell', 'Laugher')
insert into workers(firstName, lastName) values('Sally', 'Rushington')
insert into workers(firstName, lastName) values('Dorothy', 'Hurth')
insert into workers(firstName, lastName) values('Freida', 'Koolos')
insert into workers(firstName, lastName) values('Ronald', 'Jones')
insert into workers(firstName, lastName) values('Randal', 'Hughes')
insert into workers(firstName, lastName) values('Luke', 'Russell')
We're now ready to setup our HTML form. Create a file called people.html and enter the following code into it:
<html>
<head>
<title> Remote Scripting Example </title>
<script language="JavaScript" src="_ScriptLibrary/RS.HTM"></script>
<script language="JavaScript">
function findNames(theText)
{
if(theText != "")
{
var objRS = RSGetASPObject("people.asp");
var objResult = objRS.getPeople(theText, 5);
var strNames = objResult.return_value;
if(strNames != "undefined")
{
var arrNames = strNames.split("|");
document.frmPeople.names.length = 0
for(i = 0; i < arrNames.length-1; i++)
{
document.frmPeople.names.options[i] = new Option();
document.frmPeople.names.options[i].text = arrNames[i];
}
}
else
{
// No results found, clear the list
document.frmPeople.names.length = 0;
}
}
else
{
// No text entered, clear the list
for(i = 0; i < document.frmPeople.names.length-1; i++)
document.frmPeople.names.length = 0;
}
}
</script>
</head>
<body>
<h2> Find People </h2>
<form name="frmPeople" action="#" method="post">
<input onKeyUp="findNames(this.value)" type="text" maxlength="40" id="keyword" style="width:200"><br>
<select multiple size="5" id="names" name="names" style="width:200">
<option value="null">-- Enter Keywords Above --</option>
</select>
</form>
<script language="JavaScript">
RSEnableRemoteScripting("/_ScriptLibrary");
</script>
</body>
</html>
When the user enters any text in the text box, its onKeyUp event calls the JavaScript findNames function, passing its text value as the first argument to the findNames function. The second argument is 5, which tells our ASP function the maximum number of names to return.
We then use JavaScript and remote scripting to create an object from people.asp and we call that objects getPeople method, which returns a list of people separated by a pipe character ("|"). If a list has been returned then we split that list into an array and add each name to the drop down list.
Our people.html file is not too useful without its complementary people.asp file, so let's look at that now. Create a new file called people.asp and enter the following code into it:
<!--#INCLUDE FILE="_ScriptLibrary/RS.ASP"-->
<% RSDispatch %>
<script language="JavaScript" runat="server">
var public_description = new MainMethod();
function MainMethod()
{
this.getPeople = Function('searchString', 'maxRecords','return PeopleList(searchString, maxRecords)');
}
</script>
<script language="VBScript" runat="server">
function PeopleList(searchString, maxRecords)
on error resume next
dim conn
dim rs
dim names
set conn = server.createobject("ADODB.Connection")
set rs = server.createobject("ADODB.Recordset")
conn.open "provider=SQLOLEDB; Data Source=(local); Initial Catalog=people; UId=sa; Pwd="
rs.activeconnection = conn
rs.open "select top " + maxRecords + " firstName + ' ' + lastName as name from workers where firstName + ' ' + lastName like '%" & searchString & "%'"
while not rs.eof
names = names & rs("name") & "|"
rs.movenext
wend
PeopleList = names
end function
</script>
We've created one VBScript function called PeopleList. PeopleList accepts two arguments and returns a string variable. In this example we're passing in two arguments to our PeopleList function, so these are specified inside of our MainMethod constructor, like this:
this.getPeople = Function('searchString', 'maxRecords','return PeopleList(searchString, maxRecords)');
Taking a look at the JavaScript code in people.html, we can see that the function maps just nicely:
var objResult = objRS.getPeople(theText, 5);
There's nothing fancy about our PeopleList function: it simply connects to our SQL Server 2000 database, retrieves a list of people whose names are like the searchString argument and returns them as a list of separated values:
rs.open "select top " + maxRecords + " firstName + ' ' + lastName as name from workers where firstName + ' ' + lastName like '%" & searchString & "%'"
while not rs.eof
names = names & rs("name") & "|"
rs.movenext
wend
PeopleList = names
Loading people.html into my web browser, I played around with the remote scripting capabilities of our example. As I enter a different character into the text box, all records matching that text were returned and were automatically displayed in the list.