Chapter 15. Using ASP with JavaScript

CONTENTS

CONTENTS>>

•  Creating ASP Pages
•  Variables in VBScript
•  Operators and Conditional Statements
•  Loop Structures
•  Arrays
•  Passing Data from JavaScript to ASP
•  Controlling Multiple ASP Pages with JavaScript
•  Microsoft Access, ASP, and JavaScript
•  Setting Up the Access 2000 File
•  Placing the Access 2000 File on the Server and Preparing the DSN
•  Making the Connection Between Your ASP Page and Database File
•  Reading an Access 2000 Database with ASP
•  Reading and Displaying Multiple Fields
•  Inserting Records into Access from HTML
•  Summary

In this book, JavaScript has been examined as a client-side scripting language, and in this chapter it will continue to be so. As with PHP, discussed in Chapter 14, "Using PHP with JavaScript," the role of JavaScript is that of a preprocessor in dealing with Active Server Pages (ASP). Before data are sent to a back-end or server-side script, you want to make sure that the data entered are what you want. If your data are examined by a server-side script, a good deal of bandwidth is wasted as messages are passed back and forth between the client's browser and the server. However, if JavaScript makes sure that the data are clean, then all of the preprocessing is done in the client's browser, with nothing wasted in between.

ASP works with Microsoft NT Servers. ASP pages are usually associated with a language called VBScript, but you can find JavaScript or XML in ASP pages. Like the PHP pages discussed in the previous chapter, ASP pages are saved in the web server's root directory, but with the .asp extension instead of .php. Depending on the server you're using, your root directory will vary. The domain that I use for Active Server Pages is active1.hartford.edu, and I keep my pages in a folder named hotjava. If my ASP page is saved as whatzUP.asp, to access it I would enter this:

http://active1.hartford.edu/hotjava/whatzUP.asp

The addressing looks a lot like what you use for HTML, except that the extensions end with .asp instead of .html.

NOTE

Before you get started, you will need an NT server. On some versions of Windows (such as Windows 2000), you can run Windows NT software, but on others you cannot. To save yourself some grief, I recommend signing up for a professional Windows NT hosting service. Not only will you get real-world experience using ASP, but you also can use any version of Windows, any Macintosh OS, or your Linux box to learn how to work with ASP and JavaScript.

To find a hosting service, use "Windows NT Hosting" as a keyword in any of the search engines, such as Excite, Yahoo! or AltaVista. Prices will vary widely, but because you do not need much server space for the short examples in this chapter, you can get the cheapest one available. To try it out, sign up for a service that has low (or no) setup fees as well as low monthly service fees. You don't need a domain name, just an IP address that the hosting service can provide. (An IP address to access an ASP file looks something like http://323.64.12.543.23/hotJava/good.asp.) For example, www.hostek.com/plans. shtml has one plan with a monthly fee of $12.95 for 50MB of disk space with no setup fee. So, for about $13, you can spend a month learning ASP in a real-world environment to see if it's what you need. (The price quoted was at the time of this writing and might change.)

Creating ASP Pages

An ASP script has a beginning tag, <%, and an ending tag, %>, that work like the containers in HTML. In some ASP pages, you will mix HTML and JavaScript or even some XML, but here the focus is on using VBScript as the main scripting language for the server-side script and using JavaScript for the client-side script. The basic ASP container using VBScript has the following format:

<% VBScript %> 

Writing VBScripts

Use your favorite JavaScript text editor to create your ASP scripts. If you use Notepad, place quotation marks around the name of your file when you save it, and Notepad will add no unwanted .txt extension. The following script will get you started:

<% Dim WhatzUP WhatzUP="Where did I put my code?" Response.write WhatzUP  %>

Save the file as whatzUP.asp in your server root directory or subdirectory. For example, I saved my ASP file in a folder (directory) named hotJava in my web server's root directory, active1.hartford.edu. To launch the program, I would type this:

http:// active1.hartford.edu /hotJava/whatzUP.asp 

Use your domain and subdirectory names instead of active1.hartford.edu and hotJava. When you call up the script in your browser, you will see this in the browser window:

Where did I put my code? 

As in JavaScript, if you get an error, check your code and check your directory.

Basic Screen Display Format

To display information to the screen, VBScript uses the statement Response.write, not unlike document.write() in JavaScript. The statement works like document.write() in that it accepts literals, variables, functions, or expressions. For example, this line:

Response.write "I like JavaScript." 

works like this:

document.write("I like JavaScript.")' 

Variables in VBScript

While declaring a variable in JavaScript is optional, it is not in VBScript. You must use a Dim (for dimension) statement to first declare the variable.

<% Dim animal animal = "Zebra"  %>

To declare multiple variables, you can use a single Dim statement:

Dim customers, products, prices 

So, remember that while declaring a variable is optional in JavaScript, it is not in VBScript.

VBScript Data Types

As in JavaScript, data are not typed. That is, the variables are "smart" and will change type with the context of their use. Also as in JavaScript, you can use HTML tags as values in VBScript.

<% Dim newpar, one, two, three newpar = "<p>" one = "The loneliest number." two=222.222 three= 3 Response.write one Response.write newpar Response.write two Response.write newpar Response.write three  %>

The output of the previous example will be this:

The loneliest number 22.222 3

If you treat variables the same way in VBScript as you do in JavaScript, you should be fine. However, you must remember to use the Dim statement to declare all variables first.

VBScript Comments

Comments in VBScript work like those in JavaScript. They are ignored by the parser, but they inform the programmer. VBScript uses a single quotation mark (') instead of double slashes (//); otherwise, they work the same. The following example shows a comment in VBScript:

<% Dim customer customer="Frank Talker" 'Each customer name is first placed into a variable. Response.write customer  %>

Operators and Conditional Statements

You have to pay close attention to the operators in JavaScript and VBScript. Some are the same, and some are different. Critical differences can be found in the use of the assignment variable (=) in both assignment statements and conditional statements. Table 15.1 shows the operators used in VBScript.

Table 15.1. VBScript Operators

Operator	Use	Format Example
=	Assignment	inven=832
+	Addition	total=item+tax+shipping
-	Subtraction	discount=regPrice-salePrice
*	Multiplication	itemTotal=item * units
/	Division	distrib=all/part
\	Integer division	hack=433.33\32
Mod	Modulus	leftOvr = 87 Mod 6
u	Exponentiation	cube = side ^ 3
&	Concatenation	FullName = "Java" & "Script"
=	Equal to	if alpha = beta Then .
<>	Not equal to	if Jack <> Jill Then .
>	Greater than	if elephant > mouse Then .
<	Less than	if subtotal < 85 Then .
>=	Greater than or equal to	if counter >= sumNow Then .
<=	Less than or equal to	if 300 <= fullAmount Then .
Not	Negation	if not (alpha > beta) Then .
And	Logical AND	if (alpha > beta) And (delta < gamma) Then .
Or	Logical OR	if (alpha=beta) Or (delta=gamma) Then .
Xor	Logical XOR	if (sum >=1000) Xor (tax > .08) Then .
Eqv	Logical equivalence	if (alpha = beta) Eqv (delta=gamma) Then .
Imp	Logical implication	if alpha Imp beta Then .

VBScript Conditionals

VBScript conditionals are similar to those found in the different versions of the Basic language, especially Microsoft's Visual Basic. If you are familiar with just about any kind of Basic programming, you will be on familiar ground. However, the logic of VBScript conditional statements is pretty much the same as that found in JavaScript, so you should not have too much difficulty.

The if/then Statement

VBScript employs this format:

if statement then another statement 

which is equivalent to JavaScript's

if (statement) {       another statement } 

The then keyword replaces the first curly brace, and there is no equivalent to the second curly brace. For example, the following script uses the if/then format in a single line:

<% Dim alpha, beta alpha="JavaScript" beta="VBScript" if alpha <> beta then Response.write "They are different."  %>

As in JavaScript, the else keyword can take the script to a different path when the condition is not met, as the following script shows:

<% Dim designTime, payment designTime=88 payment=5500 if payment > 5000 And designTime < 80 then Response.write "Take the job." Else Response.write "The schedule is too long."  %>

Using ElseIf and Case

The ElseIf keyword can be used when you have several different conditions. The last statement before End If is an Else statement. You can have as many ElseIf statements between the first If condition and the Else condition as you want.

<% dim alpha, beta alpha=10 beta=20 If alpha = beta then       Response.write "They are equal" ElseIf alpha > beta then       Response.write "The first is bigger" ElseIf alpha < beta then       Response.write "The second is bigger!" Else       Response.write "I give up" End If  %>

As in JavaScript, you might not prefer to use the ElseIf structure. VBScript has a similar statement to the switch case statement in JavaScript. It has the following structure:

<% Dim flowers flowers = "rose" Select Case flowers Case "posey" Response.write "Two pence please." Case "daisy" Response.write "Her last name was Miller." Case "rose" Response.write "By any other name ." Case Else Response.write "No flower of that nature is available." End Select  %>

In the previous example, the screen would display this:

By any other name .

This is because the Case variable, flowers, is defined as rose, and the Case with rose prompts a Response.write of By any other name . Whichever structure you prefer, the Elseif or Case, is available for scripts with multiple conditions.

Determining Data Types in Conditional Statements

The VarType() function in VBScript is handy for determining what type of data is in a variable at any given time. This function returns the variable as 1 of 16 coded values. In the following example, a string variable and numeric variable are detected in a conditional statement:

<% Dim stringVar, numVar, typeVar, newline newline="<br>" stringVar="I am a string." numVar=1234 if VarType(stringVar) = 8 then Response.write stringVar & " This is a string." & newline if VarType(numVar) = 2 then Response.write numVar & " This is an integer."  %>

When the program executes, you will see the following on the screen:

I am a string. This is a string. 1234 This is an integer. 

The conditional statement uses the codes in VBScript to sort the precise data type. Table 15.2 shows all of the codes.

Table 15.2. VarType Codes

Code	Data Type in Variable
0	Empty
1	Null
2	Integer
3	Long integer
4	Single
5	Double
6	Currency
7	Data
8	String
9	Object
10	Error
11	Boolean
12	Variant (arrays only)
13	Data access object
17	Byte
8192	Array

TypeName() is another useful function in VBScript for determining data type. However, instead of returning a number, it uses a string. The following script demonstrates some of the TypeName() syntax:

<% dim alpha, beta, gammma, delta,newline alpha=5 beta=5.7 gamma="Alfred" delta=CDate(#06/06/2004#) newline="<br>" Response.write TypeName(alpha) & newline Response.write TypeName(beta) & newline Response.write TypeName(gamma) & newline Response.write TypeName(delta) & newline  %>

When you launch the script, you will see the following data types listed:

Integer Double String Date

Loop Structures

As in JavaScript, VBScript has more than one loop structure. The for/next loop is like the for loop in JavaScript, and the do/while loop is equivalent to the do/while loop in JavaScript. The do/until loop is like the do loop in JavaScript.

For/Next Structure

The for/next structure in VBScript expects a beginning and ending value for the loop. With each iteration of the loop, any statements between the for and next statements are executed. The following example shows a simple application using a loop to place formatted values on the screen. Note how the variable spacer is created using a loop as well.

<% Dim counter, newline,spacer,x newline="<br>" for x=1 to 10       spacer="-" & space next For counter = 1 to 25       Response.write (counter & spacer & counter + 25& spacer & counter + 50&       spacer & counter + 75 & newline) Next  %>

As with JavaScript, the for/next structure is best employed when the beginning and terminating values are known. With the two do loop structures, some kind of independent counter variable signals the end of the loop.

Do/While Loop

The do/while loop begins with the termination condition and keeps looping until that condition is met. The following example shows this loop at work:

<% Dim counter, newline newline="<br>" counter=12 Do While counter >-1       Response.write ( "Current dot com value is $" & counter & " million.")       Response.write newline       counter = counter - 1 Loop  %>

You really need a script that tells you that your dotcom stock is going to drop before it goes through the floor!

Do/Until Loop

The do/until loop keeps repeating until the stop condition is met. It is the logical opposite of the do/while loop. (Using it, your dotcom stock will fare even worse!)

<% Dim counter, newline newline="<br>" counter=12 Do Until counter >-1       Response.write ( "Current dot com value is $" & counter & " million.")       Response.write newline       counter = counter - 1 Loop  %>

The Do Until loop gives one additional iteration after it encounters the termination condition.

Arrays

Arrays in VBScript and JavaScript are the same in concept but are put together a little differently. The multidimensional arrays in VBScript are easier to work with than their counterpart in JavaScript. Like variables, arrays begin with a Dim statement, but an array size is provided as well. The following shows a simple example:

<% Dim item(3) item(0) = "Greater Swiss Mountain Dogs" item(1) = "Bernese Mountain Dogs" item(2) = "Pyrenean Mountain Dogs" Response.write item(2)  %>

Note that, in VBScript, the array elements are in parentheses and not brackets, but, as in JavaScript, the first element is 0 and not 1.

Multidimensional Arrays

If you are working with more than a single dimension and need to put it in an array in VBScript, you will find it easy to do, as is the case in most Basic programming languages. This statement has 51 elements in one dimension and 15 in another:

Dim SalesRep (51,15)

Using two dimensions, it might help to envision the array as follows:

Dim SalesRep (Row,Column)

A database with sales representatives in 50 states has up to 15 representatives in each state. (Fifty-one states are dimensioned because the 0 element will not be used in this particular example.) The states are identified by their admission to the union. Maryland has four representatives listed in the two-dimensional array in the following script:

<% Dim SalesRep(51,15), newline, counter newline="<br>" salesRep(7,0)="Smith" salesRep(7,1)="Jones" salesRep(7,2)="Lee" salesRep(7,3)="Hallohan" salesRep(7,4)="Gonzalez" for counter = 0 to 4       Response.write salesRep(7,counter) & newline next  %>

You should get the following output:

Smith Jones Lee Hallohan Gonzalez 

Using a loop structure, as the previous example shows, finding what you want in the array is simple and keeps everything well organized.

Functions

VBScript has built-in functions and user functions just like JavaScript. (Microsoft keeps a list of the built-in functions at http://msdn.microsoft.com/scripting/default.htm?/scripting/vbscript.) You will find JavaScript and VBScript functions very similar. The user function has this format:

Function functionName(arguments)       Statements End function

The following shows a simple example and includes a built-in function, Space(), to add some space between the first and last names:

<% Function fullName(lastName,firstName)       Response.write firstName & Space(1) & lastName End function Response.write fullName("Sanders","Bill")  %>

Passing Data from JavaScript to ASP

In the role of a confirmation tool, JavaScript can check the content of forms before data is sent to an ASP page. Such a role is important for real-world forms because, if troublesome data are sent to an ASP page, the wrong data could end up in your database.

To see one role of JavaScript in gathering information, these next two scripts, one an HTML page and the other a VBScript script, show how data can originate in JavaScript and be passed to an ASP page.

Two key lines are used to pass data from HTML to ASP in the two scripts. On the HTML side, the <form> tag has two attributes, action and method. The method to pass data is either post or get. With ASP pages, use post. The action is to load the page specified in the URL assigned to the action attribute. Use the following general format:

<form method=post action="myServPag.asp"> 

On the server-side, use the VBScript function Request.form("varName"). The function expects an argument in quotation marks with the name of the variable from the HTML page. Using the Request.form() function, you must remember to place quotes around the variable name. Normally in a function in JavaScript, quotation marks are reserved for string literals, as is the case in VBScript. However, using Request.form(), you find an exception to this rule. The following general format is used:

<% Dim varName varName=Request.form("varName")  %>

The name of the variable in the ASP page can be any name that you want; however, I found that by using the same name in both the HTML and ASP pages, keeping track of everything is easier.

When you submit an HTML form, all variables in the form are passed to the ASP page. Therefore, you can have several variables sent at once, and each can be stored separately in a VBScript variable or array element.

The JavaScript function simply loads a hidden form with a value. Note that the JavaScript variable greet is placed into a hidden form named alpha. The variable that is passed to the ASP page script is alpha because ASP is getting its variable from a form element named alpha. Thus, JavaScript makes a "bank shot" to the form and then to ASP rather than directly to the VBScript script.

On the ASP side of the equation, you will see very little VBScript. However, you can use CSS to style your ASP page. In fact, you will see that most of the code is HTML or CSS, with just a few lines of VBScript.

getData.html

<html> <head> <title>Sending Data to an ASP Page </title> <script language="JavaScript"> function loadIt() {       var greet="Telegram from ASP!";       document.storage.alpha.value=greet;       }  </script> </head> <body onload="loadIt()";> <form name="storage" method=post action="greetings.asp">       <input type=hidden name="alpha" >       <input type=submit value="Submit form and get this page's message back from ASP"> </form> </body> </html>

Save this script as getData.html. It uses the post method to open an ASP page named greetings.asp. The ASP page immediately echoes whatever is in a variable named alpha. As you will see, because alpha is not declared or defined on the ASP page, its content could be only what has been passed to it from the HTML page. Save the following page as greetings.asp, and put it in the root directory or subdirectory in your root directory along with the getData.html page.

greetings.asp

<html> <head> <style type="text/css"> body {       font-family:verdana;       font-size:24pt;       color:#ffff33;       background-color:#f3cc00;       font-weight:bold;       } #blackground {      background-color:black;      } </style> </head> <body> <div ID=blackground>       <strong>             <center>                    <%                    Dim alpha                    alpha=Request.form("alpha")                    Response.write alpha                     %>             </center>       </strong> </div> </body> </html>

When you run the getData.html script, press the Submit button to launch the ASP script. Your screen shows the message "Telegram from ASP!"

Controlling Multiple ASP Pages with JavaScript

One use of JavaScript is to serve as a controller for ASP pages appearing in an HTML environment. Because ASP replaces a page that it is called from, one plan of action is to use JavaScript to create a number of buttons used to launch different ASP pages within a frameset. JavaScript can reside in the menu page, and ASP is called in the body page of a two-column frameset. At the same time, both the JavaScript page and the ASP page share a common external style sheet. Seven scripts are required for this project: an external CSS file, a frameset page, the menu page using JavaScript, a placeholder page, and three ASP pages demonstrating different features of ASP.

Cascading Style Sheet

Using a color scheme from The Designer's Guide to Color Combinations by Leslie Cabarga (North Light Books, 1999), the first step is to create a palette using the following colors (all values are in hexadecimal):

Color A	FFE699	Light tan
Color B	CC994C	Tan
Color C	CC0019	Deep red
Color D	00804C	Dark green
Color E	808080	Gray
Color F	000000	Black

This particular color scheme was selected because it contains black and gray, the colors of the button in HTML forms. The following style sheet incorporates the colors used in both the ASP and HTML pages.

controller.css

.bigRed {        color: #cc0019;        background-color: #ffe699;        font-family: verdana;        font-weight: bold;        font-size: 18pt;        } .midRed {          color: #ffe699;          background-color: #cc0019;          font-family: verdana;          font-weight:bold;          font-size: 14pt;          }

Save the CSS style sheet in the same directory as the files for the HTML and ASP pages. All of the pages will employ the same style sheet.

This next HTML page establishes the frameset where the initial HTML pages and, eventually, the ASP pages will go.

controlSet.html

<html> <frameset cols="25%,*" border=0>       <frame name="menu" src="jMenu.html" frameborder=0 scrolling=0>       <frame name="display" src="jsD.html" frameborder=0 scrolling=0> </frameset> </html>

The left side of the frameset opens with a menu page and a blank HTML page in the right frame. All of the JavaScript controllers are in the menu. A simple function in JavaScript is used to launch any of the ASP pages in the right frame.

jMenu.html

<html> <head> <link rel="stylesheet" href="controller.css" type="text/css"> <script language="JavaScript">       function loadASP(url) {       parent.display.location.href=url;       }  </script> </head> <body bgcolor=#00804c> &nbsp; <table BORDER=0 height=70%>       <tr align=left valign=top>             <td bgcolor="#808080"><center>                   <div class="bigRed">&nbsp; Menu&nbsp;</div>                   </center>                   <br><form>                         <input type=button onClick="loadASP('alpha.asp')" value="Selection1">                         <p><input type=button onClick="loadASP('beta.asp')" value="Selection2">                           <p><input type=button onClick="loadASP('gamma.asp')"value="Selection 3">                       </td>                </tr> </table> </body> </html>

The initial page in the right frame is a "dummy" page used to occupy space until one of the ASP pages is launched. Figure 15.1 shows how the initial page appears when the frameset is loaded.

Figure 15.1. The area on the right is reserved for ASP by an initial placeholder page.

jsD.html

<html> <head> <title>Display Page</title> <link rel="stylesheet" href="controller.css" type="text/css"> </head> <body bgcolor=#cc994c>       <div class=midRed> &nbsp ASP Pages Appear Here &nbsp </div> </body> </html>

The first ASP page simply displays a message in plain text. However, it does load the same style sheet as the two previous HTML pages.

alpha.ASP

<html> <head> <title>Control Menu</title> <link rel="stylesheet" href="controller.css" type="text/css"> </head> <body bgcolor=#808080> <center> <% Response.write "This is plain text."  %> </center> </body> </html>

The second ASP page responds with a text message using one of the style sheet classes.

beta.ASP

<html> <head> <title>Control Menu</title> <link rel="stylesheet" href="controller.css" type="text/css"> </head> <body bgcolor=#808080> <center> <% Response.write "<div class=midRed>&nbsp Here's the other CSS class &nbsp </div>"  %> </center> </body> </html>

The third ASP page, like the second, responds with a message that indicates that yet another of the CSS classes has been employed (see Figure 15.2).

Figure 15.2. An ASP page using the same CSS style sheet as the HTML page appears in the right column.

gamma.ASP

<html> <head> <title>Control Menu</title> <link rel="stylesheet" href="controller.css" type="text/css"> </head> <body bgcolor=#808080> <center> <% Response.write "<div class=bigRed>&nbsp CSS Text like the Menu &nbsp</div>"  %> </center> </body> </html>

Microsoft Access, ASP, and JavaScript

One of the most popular Windows OS database programs in use is Microsoft's Access. Using Access 2000, this section shows how to use HTML and JavaScript as a front end to display, search for, and add records to an Access 2000 file on a server. Most of the work is done using VBScript and Structured Query Language (SQL) commands to query an Access 2000 file on a server using variables in a script for showing what is in the database and as an input source for adding new records. The database is made up of the following nine fields:

• Identity number

• Last name

• First name

• Address

• City

• State

• ZIP code

• Phone

• Email

The purpose is to create a table to show the information of a list of people stored in the database. Each field must include a name, a data type, and the length of the field for fixed-length types.

Three different files are involved in sending data between an HTML page and a database.

Setting Up the Access 2000 File

You will need Microsoft Access 2000 for the rest of this chapter. All of the example files were created using the Windows version of Access; at this time, Microsoft is not selling a Macintosh version of Access. However, the Access files on the book's web site can be used on an NT Server, and all of the ASP pages created with the Macintosh can read and write the files and return data from the files.

For Windows users who have Access, the following set of steps shows how to set up the database. (The steps were written using Access 2000, but older versions can be used as well, with some slight differences. The point is to create a .mdb file with the set of fields shown.)

1. Launch Access 2000 from the Start menu. Select Blank Access database in the dialog box, and click OK.

2. In the File New Database window, first select the desktop as the folder, and then use the filename javaS.mdb in the File Name text window at the bottom of the window. Click Create.

3. Double-click the option Create Table in Design View in the Database window. In the Design View window, enter the following in the Field Name and Data Type columns. Use the pop-up menu in the Data Type column to select the data type. (All of the nine fields are text except for the first, which is AutoNumber.) Unless otherwise noted, all of the field sizes are the default size.

• ID AutoNumber. (Click the Key icon on the toolbar to make this field the primary key.)

• Lname Text (default).

• Fname Text (default).

• Address Text (default).

• City Text (default).

• State Text (default); field size 2.

• Zip Text (default); field size 5.

• Phone Text (default); field size 10.

• Email Text (default).

4. After you define the nine fields, click the Disk icon on the toolbar and save the table using the name JavaStable. Next, select File, Close from the menu bar.

5. When you close the Design View window, you will see a window with your new table in the javaS: Database window. Double-click the JavaStable icon to open it. Enter three names and the rest of the fields in the table. The ID column automatically provides a unique ID number as you enter data into the other two fields.

6. After entering the data, click the Save icon (disk in the toolbar) and then select File, Exit. You're finished with entering data directly into the Access 2000 database table and file.

Placing the Access 2000 File on the Server and Preparing the DSN

The next step is to open the root folder where you have been placing your ASP pages and place your database file. If you are using a remote server, just use FTP to move the Access 2000 file to the root folder or subfolder on the NT Server.

Next, you need to create a data source name (DSN) connection to the database on the server. This connection allows your ASP application to interact with the database. For Windows 2000 and Windows NT, use the following steps.

(Note: These steps are on the server. If you are using your computer as both client and server, then you can follow these steps on your computer. If you are administering another NT server, these steps should be on the remote server.)

1. Select Start, Settings, Control Panel. In the Control Panel, double-click on the ODBC Data Sources icon to open the ODBC Data Source Administrator.

2. Click on the File DSN tab and click Add. Then select Microsoft Access Driver (*mdb) from the menu and click Next.

3. Type in a descriptive name. We used JavaS for the file DSN name. Click Next.

4. Click Finish, and then click Select. Navigate to the directory of your database file. It will appear in the left window under Database Name. Click on your database (for example, javaS.mdb) to select it, and then click OK.

5. Click OK twice. In the ODBC Data Source Administrator window in the File DSN tab, you will see your new DSN name with the extension .dsn added.

Figure 15.3 shows what you should see in the File DSN folder in the ODBC Data Source Administrator when you have correctly configured your data file.

Figure 15.3. Establishing your database file with an appropriate data source is essential when you use your own server.

When you enter the database filename in an ASP script, the DSN name is used by the server software to enable a connection.

Making the Connection Between Your ASP Page and Database File

To make a connection between an ASP page and a database, you need to know the path to the database. The process itself is simple enough if you have the right path. However, when you get the formula, you can reuse it on any database within the same server. The following path was used:

d:\Inetpub\Wwwroot\hotJava\javaS.mdb; 

In the example, drive D is used, but the correct drive could have been drives C or E, or any other drive. The important fact to note in the path is that it is an internal path to the root directories on the server. So, while a different address had to be used to access the ASP pages (such as http://myserver.com) that contained the code, the path in the ASP page references a local, internal path for the server.

The path is just part of a longer driver definition required to connect the ASP script to the database and the data within it. Generally, the path is defined in a variable, and then the VBScript uses that variable in other commands to make the connection. First, the provider needs to be defined. The following single line defines the driver in the variable named hookUp:

hookUp= "Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\Inetpub\Wwwroot\hotJava\javaS.mdb;" 

Because the provider information has been placed into a variable, when the provider has to be used, you need to use only the variable name instead of the long line of code.

The rudiments of setting up an ASP page to make a connection to a database and pull out data can best be seen in a complete page and example. In that way, you will be better able to understand the context of the required code. The following ASP page pulls together the different parts needed to see the contents of a database on a server.

Reading an Access 2000 Database with ASP

After the Access 2000 database has been set up and a DSN has been established for it, you are ready to create an ASP page to pull data from the Access file and send it to JavaScript. To begin, enter the following ASP script and save it in the root directory or a subdirectory within the root directory of your server. Save the file using the name readDB1.asp.

readDB1.asp

<% Dim hookUp, Conn, ViewRecord, Lname, output, display, SQL hookUp= "Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\Inetpub\Wwwroot\hotJava\javaS.mdb;" Set Conn = Server.CreateObject("ADODB.Connection") Set ViewRecord = Server.CreateObject("ADODB.Recordset") Conn.Open hookUp SQL="SELECT * FROM JavaStable" ViewRecord.Open SQL, Conn Do While Not ViewRecord.EOF       Lname=ViewRecord("Lname") & "<br>"       display =display & Lname       ViewRecord.MoveNext Loop Response.Write display ViewRecord.Close Conn.Close Set ViewRecord = Nothing Set Conn = Nothing  %>

The first part of the script is for dimensioning the variables, defining the connections, and then setting the connections. The several variables (such as hookUp and Conn) are declared using the Dim statement. Next, the driver and provider are placed into a variable named hookUp. The ADODB connection object is placed into the variable Conn, and the ADODB recordset object is placed into a variable named ViewRecord.

The next part opens the connection and defines an SQL command. The action query selects all the records (* = wildcard) from the table named JavaStable in the Access 2000 file named javaS.mdb. The next line opens the recordset in the database defined in the connection and issues the SQL command.

One of the nicer features of VBScript is that it can loop through a recordset until it encounters the EOF (end of file) of the recordset. Note also the use of the <br> tag in this block. Used in HTML as a line break, you will find that, in a text field, the break works as well. The display variable concatenates the data with the line break tag through the entire loop. To keep this first example simple, only one of the nine fields, Lname, is passed to a variable, display, that collects all of the data for the first field.

Next, the display variable is used with Response.Write to send the data to the screen. Finally, the open connections are closed and the variables are reset to Nothing.

Reading and Displaying Multiple Fields

The first script showed only one field, to simplify the process and focus on what needed to be done for connection. The following script looks at all nine fields and all the records in the fields. Save the script in your root directory or subdirectory in the root directory.

ReadDB2.asp

<% Dim hookUp, Conn, ViewRecord, ID, Lname, Fname, Address, City, State, Zip, Phone, Email, display, SQL hookUp= "Driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\Inetpub\Wwwroot\hotJava\javaS.mdb;" Set Conn = Server.CreateObject("ADODB.Connection") Set ViewRecord = Server.CreateObject("ADODB.Recordset") Conn.Open hookUp SQL="SELECT * FROM JavaStable" ViewRecord.Open SQL, Conn Do While Not ViewRecord.EOF       ID=ViewRecord("ID")       Lname=ViewRecord("Lname")       Fname=ViewRecord("Fname")       Address=ViewRecord("Address")       City=ViewRecord("City")       State=ViewRecord("State")       Zip=ViewRecord("Zip")       Phone=ViewRecord("Phone")       Email=ViewRecord("Email")       display =display & ID & "<br>" & Fname & Space(1) & Lname & "<br>" & Address & "<br>"       & City & "," & Space(1) & State & Space(1) & Zip &  "<br>" & Phone & "<br>" & Email       & "<p>"       ViewRecord.MoveNext Loop  Response.Write display ViewRecord.Close Conn.Close Set ViewRecord = Nothing Set Conn = Nothing  %>

All of the variables use the same name as the fields in the database. In that way, you are less likely to confuse what is what.

Inserting Records into Access from HTML

The final step is to create an HTML page through which you can add data to your database. JavaScript plays the role of checking your data before submitting it. First, you need to create your web page for data entry, and then you need to create your ASP page to send the data to your Access file on the server. (After you add your data, you can use the script in the previous section to read it and make sure that it's all there.)

addRecords.html

<html> <head> <style type="text/css"> body {       font-family:verdana;       font-size:11pt;       font-weight:bold;       background-color:ffabab;       } #myText {color:ff2626; background-color:black} </style> <script language="JavaScript"> function verify() {       var flag=0;       dv=document.reporter;       for(var counter=0;counter < dv.length-2;counter++) {             if(dv.elements[counter].value=="") {             flag=1;       }       }       if(flag==1) {             alert("Please fill in all parts of the form:");      } else {             alert("Form is ready to submit");       } }  </script> </head> <body onLoad="document.reporter.reset()"> <h3>Fill in all windows in the form </h3> <div ID="myText"> <form name="reporter" method=POST action="http://active1.hartford.edu/hotJava/addRecords.asp">        <input type=text name="ID">ID <br>       <input type=text name="Lname">Last Name <br>       <input type=text name="Fname">First Name <br>       <input type=text name="Address"> Address <br>       <input type=text name="City"> City <br>       <input type=text name="State" size=2>State <br>       <input type=text name="Zip" size=5>Zip <br>       <input type=text name="Phone" size=10> Phone <br>       <input type=text name="Email"> Email <p>       <input type=button value="Verify:" onClick="verify()"> &nbsp;        <input type=submit value="Send information to database:"> </form> </div> </body> </html>

The script for inserting data into the database must access a file on the NT server. An added HTML tag is used to access an ADO (Active Data Objects) file required when you add data. Using a set of double and single quotations, the SQL variable (and, thereby, command sequence) is set up; when that's done, the script follows a familiar path.

addRecords.asp

<!-- METADATA TYPE="typelib"               FILE="C:\Program Files\Common Files\System\ado\ msado15.dll" --> <HTML> <% Dim Conn,Cmd, intNoOfRecords,jsID,jsLname,jsFname,jsAddress,jsCity,jsState,jsZip,jsPhone,jsEmail,SQL jsID=Request.Form("ID") jsLname=Request.Form("Lname") jsFname=Request.Form("Fname") jsAddress=Request.Form("Address") jsCity=Request.Form("City") jsState=Request.Form("State") jsZip=Request.Form("Zip") jsPhone=Request.Form("Phone") jsEmail=Request.Form("Email") Conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data  Source=d: \Inetpub\Wwwroot\hotJava\javaS.mdb;" Set Cmd = Server.CreateObject("ADODB.Command") Cmd.ActiveConnection = Conn SQL= "INSERT INTO javaStable(ID, Lname,Fname,Address,City,State,Zip,Phone,Email) VALUES (" & jsID & ",'" & jsLname & "','"& jsFname & "','" & jsAddress & "','" & jsCity & "','" & jsState & "','" & jsZip & "','" & jsPhone & "','" & jsEmail & "')" Cmd.CommandText =SQL Cmd.CommandType = adCmdText Cmd.Execute intNoOfRecords Set Cmd = Nothing Response.Write "Records entered:"  %> </HTML>

When they're in the VBScript, the variables are dimensioned and then data from the HTML page is pulled into the page using Request.Form(). The nine variables matching the HTML names are inserted as ASP variables using a lowercase js before each of the HTML variable names. Next the connection protocols establish a link with the database. (If you are using Access 97, substitute 4.0 with 3.1 right after OLEDB.)

Using the SQL command INSERT, you list the names of each field and then insert data into them. If you used literals, your VALUES would look like this:

" VALUES (21, 'Adams','John','32 Bath', 'Boston', 'MA', '05432','322-123-4567' '[e-mail]')"

Because you need to use variables instead of numeric and text literals, the variables must be concatenated into the format recognized by VBScript. Everything but the variables themselves needs to be in quotation marks, and all text must have single quotation marks around it. When copying the SQL contents, be very careful.

Summary

As with PHP, JavaScript can play a verification role with ASP pages. A good deal of the scripting work is handled by VBScript, and, on the server side of the equation, that is to be expected. However, as you delve deeper into back-end scripts, you will find that more use can be found in a good JavaScript verification script on the client side.

Much more can be done with both JavaScript and ASP pages than could be included in this chapter. However, the point is that you can effectively use the two together to gather data, send the data to an Access database, and then view the data stored. As a starting point, this chapter should have given you a clear idea of how ASP pages can be effectively used with HTML, JavaScript, and CSS.

CONTENTS