www.tamertolba.4t.com

Chapter 8

Database Access Component

ActiveX server components extend your scripting capabilities by providing a reusable means of gaining access to information. For example, the Database Access component enables scripts to query databases. Thus, whenever you want to query a database from a script, you can use the Database Access component; you need not write complex scripts to perform this task.

The Database Access component uses ADO (ActiveXش Data Objects) to access information stored in a database or other tabular data structure. It also uses the connection pooling feature of ODBC 3.0 to make database access more efficient.

8.1 ODBC, OLE DB and ADO:

ODBC and OLE DB are Microsoft Windows® application programming interfaces (APIs) for accessing data. The older ODBC specification provides data access to primarily relational SQL-based databases, and it does this well. OLE DB, Microsoft's next-generation data-access specification, allows data access to a much broader set of data providers. Those providers include non-relational database systems, e-mail systems, and CAD/CAM data stores, as well as the classic relational database system.

OLE DB does not replace ODBC. In fact, OLE DB includes a data provider that allows you to use it with ODBC data sources. The important point to realize, however, is that Microsoft plans for OLE DB to go far beyond ODBC in providing universal access to data, no matter how or where it gets stored on the enterprise.

Where does ADO fit into this picture? ADO is a high-level interface to OLE DB. For now, you can conduct business as usual because you can use ADO to access OBDC data sources through OLE DB's ODBC data provider. In the future, however, you will be able to use ADO to access your e-mail system or some other non-relational data provider.

Microsoft has publicly stated that ADO eventually will replace the company's current myriad of data-access models, including DAO and RDO, so keep in mind that ADO is not just for Internet/intranet data access. ADO is the Microsoft data access model for the future!

8.2 Active Data Objects( ADO ):

The ADO component installed with Active Server Pages provides a simplified, high-powered data object model that enables you to access databases using an OLE DB provider, including the Microsoft ODBC provider. OLE DB is Microsoft's next-generation specification for data access.

The general idea of OLE DB is for data consumers (applications) to access a wide variety of data through service providers. OLE DB is based on the need to access and manipulate other forms of data besides relational databases using a native dialect (not necessarily SQL). If you want to retrieve spreadsheet cells from an Excel spreadsheet, you would use the service provider for Excel spreadsheets; for ODBC databases, you would use the ODBC database service provider. OLE DB is an API set that enables C++ developers to create data providers, service providers, and data consumers. ADO enables Visual Basic developers to use the OLE DB ODBC service provider to access and manipulate database. ADO resembles RDO and ODBC Direct in some ways, but it has a much simpler object

model, as The figure below shows.

To simplify scripting and programming, many of the ADO objects can be created independent of other objects. The Recordset object, for example, does not require the programmer to create a Connection object first (a loose object hierarchy). Using independent objects might seem a bit strange to an experienced DAO programmer, but once you get used to the ADO model, you will find it simple to use. By using ADO, you can create fully scrollable recordsets that can be updated. ADO supports the following features:

1- Batch updates

2- Stored procedures (including multiple recordsets)

3- Free-threaded objects (see Chapter 23 for threading models)

4- Various cursor types

5- Recordset cache management

For a Visual Basic database Web developer, ADO provides one of the simplest methods to use database information across the World Wide Web.

Because ADO uses the OLE DB ODBC service provider, you can access any ODBC database to which your Web server has access. The only requirement for using a database with ADO on your IIS 3.0(or above) Web server is a system ODBC data source name (DSN). After the system DSN is added, you can use ADO to access the database.

There are 3 top-level ADO objects that you need to be concerned with: The Command, Connection, and RecordSet objects.

One of the coolest things about ADO is that when you use one of these objects, the other 2 are created automatically! So, why have 3 to start with? Well, because each object has different properties, events, and methods, and from time to time you may want to do something with some of them. So, which object you invoke, how you use it, etc., are more or less dictated by what your needs are. So, let's start with a brief description of each object:

Connection object: You use the ADO Connection object to create a connection to a data provider. The ADO Connection object is similar to the DAO database object or the RDO rdoConnection object.

Recordset object: You use the ADO Recordset object to create a set of records from a query. As in the DAO Recordset and the RDO rdoResultset objects, you can move forward and backward through ADO recordsets. Sometimes, ADO recordsets are called cursors. ADO recordset cursors are always built on the server.

· Command object: You use the ADO Command object to point to SQL strings, stored procedures, or action queries that you can execute. The Command object is similar to the DAO QueryDef object or the RDO rdoPreparedStatement.

· Errors collection: The ADO Errors collection allows you to loop through the collection of errors that may occur from a failed data-access attempt. Because one data-access statement may produce multiple errors, ADO defines the Errors collection rather than a single Error object. The ADO Errors collection is similar to the DAO collection of the same name and the rdoErrors collection.

8.3 Database Open:

Any ASP script that needs to connect to a database must open it on the server first.There are two ways:

a connection via a DSN

a DSNless connection

A DSN is the Data Source Name .

A DSN connection requires the server administrator setup a DSN on the server using the control panel, or, that you use a 3rd party ASP component so your ASP scripts can make the registry changes on the server to create their DSNs as needed.

1.At the computer running your Web server, open Control Panel.

2.Double-click the ODBC icon, and then click System DSN.

There are two types of data sources: User, which is available only to you, and System, which is available to anyone using the computer. Data sources for use with the Web server need to be of the System type.

3.Click Add, choose the Microsoft Access Driver, and then click Finish.

4.In the Data Source Name box, type student, and then click Select. Select the \AspSamp\Students\student.mdb file (in the Inetpub directory by default), and click OK.

5.Click OK to close the dialog boxes.

DSN connections generally at a minimum require a DSN name, user, and password. This line would open a DSN named "student" as a user named "student" with a password of "magic".

1 set conntemp=server.createobject("adodb.connection")

2 conntemp.open "DSN=Student; uid=student; pwd=magic"

3 set rstemp=conntemp.execute("select * from authors")

What if we didn't have a DSN? And we knew the name of the file (i.e. file based databases like Access, Paradox, FoxPro, etc.) or the name of the data source (SQLserver for example). Here is the way we could open a data source without a DSN! Note that you must know the actual filepath on the server, i.e.nwind.mdb is not good enough it needs to be "C:\thatserver\account17\ nwind.mdb". Fortunately the server.mappath function can turn a filename into the proper fully qualified filename with path on the server.

1 set conntemp=server.createobject("adodb.connection")

2 cnpath="DBQ=" & server.mappath("yourtable.mdb")

3 conntemp.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & cnpath

4 set rstemp=conntemp.execute("select * from authors")

Here is a sample connection to an access database named "nwind.mdb" in the root of the web site.

<TITLE>nwind.asp</TITLE>

set conntemp=server.createobject("adodb.connection")

' DSNless connection to Access Database

DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "

DSNtemp=dsntemp & "DBQ=" & server.mappath("nwind.mdb")

conntemp.Open DSNtemp

' DSNless connection to Access Database

set rstemp=conntemp.execute("select * from customers where country='germany'")

howmanyfields=rstemp.fields.count -1

<tr>

<% 'Put Headings On The Table of Field Names

for i=0 to howmanyfields %>

<% next %>

</tr>

<% ' Now lets grab all the records

do while not rstemp.eof %>

<tr>

<% for i = 0 to howmanyfields%>

<% next %>

</tr>

<% rstemp.movenext

loop

rstemp.close

set rstemp=nothing

conntemp.close

set conntemp=nothing%>

</table>

</BODY>

</HTML>

8.4 Accessing Data Using ADO:

The following ASP file (emp1.asp) uses ADO to grab a record from the Access 97

WebMeals.mdb database using the WebMeals ODBC system data source.

Following is the complete emp1.asp page:

<HTML>

<HEAD>

<TITLE>Emp1.asp Example</TITLE>

</HEAD>

<BODY>

<H1> Employee #1 </H1>

' Declare the variables

Dim rst

Dim varSQL

' Create an empty recordset object

Set rst = Server.CreateObject("ADODB.Recordset")

' Create the SQL statement and stick it in varSQL

varSQL = "SELECT * FROM qryCurrentEmployees " & _

"WHERE EmployeeID = 1"

' Fill the recordset with records based on the

' SQL statement in varSQL and the WebMeals

' data source

rst.Open varSQL, "WebMeals"

' Display the data

  <%= rst("LastName") %>

<BR><B>Address:</B> <%= rst("Address") %>

<BR><B>City/State/Zip:</B> <%= rst("City") %>,

<%= rst("State") %>

  <%= rst("ZipCode") %>

' Clean up the objects

rst.Close

</BODY>

</HTML>

You can see the preceding code in action in Figure 2.

Figure 2: This page uses ADO code to extract a single record from the

qryCurrentEmployees query and display it on the Web page.

Note: If you want to run this example, you need to create a System data source

called WebMeals on your Web server machine that points to the WebMeals.mdb

database.

Pure HTML. The emp1.asp file seems as if it contains a lot of code, but it’s

actually quite simple if you break it down into several smaller parts. The first part of the ASP page is just pure HTML:

<HTML>

<HEAD>

<TITLE>Emp1.asp Example</TITLE>

</HEAD>

<BODY>

<H1> Employee #1 </H1>

Declaring variables. In this stretch of code, I declare two variables. Remember

that all variables are variants in VBScript (the default ASP scripting language), so

you don’t find As Datatype in the VBScript Dim statement.

' Declare the variables

Dim rst

Dim varSQL

Creating the Recordset object. You create the recordset in several steps:

1.Create an empty Recordset object:

Set rst = Server.CreateObject("ADODB.Recordset")

If you’re used to DAO or RDO in VBA, this way of creating objects may seem

strange. VBA allows you to early bind your objects — when you write your

code, you can set a reference to the object model’s type library. (Sometimes,

this reference gets created automatically.) You avoid having to use the

CreateObject function. Unfortunately, because the VBScript language

doesn’t support early binding, you must create all objects using the

CreateObject function.

You create ASP objects using the Server object, which provides access to

methods and properties of the Web server.

2.Add a SQL statement into the varSQL variable:

varSQL = "SELECT * FROM qryCurrentEmployees " & _

"WHERE EmployeeID = 1"

For Access data sources, you can refer to both tables and queries in your

SQL statements.

3.Use the Recordset object’s Open method to fill it with the records

returned by executing the SQL statement from varSQL.

The second argument, in this example "WebMeals", tells ADO the name of

the data source to use:

rst.Open varSQL, "WebMeals"

Displaying the data. To display the data from the record returned by the

recordset, you must use a mixture of HTML and VBScript. The HTML provides the labels for the data; the VBScript provides the values from the recordset using the names of the fields from the Access query:

  <%= rst("LastName") %>

<BR><B>Address:</B> <%= rst("Address") %>

<BR><B>City/State/Zip:</B> <%= rst("City") %>,

<%= rst("State") %>

  <%= rst("ZipCode") %>

You can insert a nonbreaking space into your HTML by using the   sequence of characters.

Clean-up time. The last section of code closes the Recordset object and ends

the HTML:

rst.Close

</BODY>

</HTML>

Now that wasn’t so bad, was it?

8.5 Flattening the Object Model:

In ADO, the object hierarchy has less emphasis. This means that, unlike DAO and

RDO, you don’t have to work your way down the object hierarchy just to create an

object. I took advantage of this fact in emp1.asp by directly creating the rst

recordset without first creating a connection.

What’s the advantage to this flatter object model? You have fewer lines of code

and less use of memory. I save a bit of memory because I avoid creating a

Connection object. Memory use is especially critical if dozens or even hundreds

of clients simultaneously use your Web page.

In many cases, however, you need to explicitly create a Connection object

because it’s more efficient when you perform multiple recordset or command

operations. Having the flexibility is nice, though.

Recordset options galore. The Open method of the Recordset object allows

you to create different kinds of recordsets. The syntax of the Open method follows:

recordset.Open source, activeconnection, cursortype, _

locktype, options

Source. The first argument of the Open method, Source, represents the source of

the recordset. In this case, I used an SQL string in emp1.asp, but I also could have

used the name of a table, or a server-stored procedure.

ActiveConnection. In the second argument, ActiveConnection, you can do one

of the following:

Use a connection string, which creates a new connection for the sole

purpose of creating the recordset.

Point to an active Connection object that you already created.

CursorType. You use the third argument, CursorType, to specify the type of

recordset to create. A summary of the possible values of this argument appears in

Figure 3.

Value	Constant	Description
0	adOpenForwardOnly	Forward-only recordset cursor (the default)
1	adOpenKeyset	Keyset recordset cursor
2	adOpenDynamic	Dynamic recordset cursor
3	adOpenStatic	Static recordset cursor

Figure 3: Possible cursor type values.

The default recordset cursor is a forward-only recordset. Unlike DAO recordsets,

you The different types of recordsets vary in how your recordset reflects changes made by other users.

Changes to the records made by other users in static and forward-only recordsets

are invisible to you. That is, when you create a static or forward-only recordset, you are totally unaware of any changes made by other users. If you don’t need to move backward through the recordset, then use the forward-only recordset because it consumes less memory and is faster than the static cursor.

Forward-only recordsets don’t support methods such as MovePrevious,

MoveFirst, or MoveLast. Also, forward-only recordsets don’t support the use of

the RecordCount property.

The keyset recordset is very similar in behavior to the DAO dynaset. The number

of that other users make, nor do records deleted by other users disappear from your

recordset (although the deleted records become inaccessible). However, you do

get notification when existing records get updated.

The dynamic recordset has no DAO counterpart. This type of recordset is totally

dynamic. When you have a dynamic recordset open, you get notices of recordset

additions, deletions, and updates. While the dynamic recordset is the most

functional, be aware that it’s also the most expensive in terms of memory and

speed.

LockType. The fourth argument specifies the type of locking to employ. A

summary of the possible lock types appears in Figure 4.

Value	Constant	Description
1	adLockReadOnly	Read only
2	adLockPessimistic	Pessimistic locking
3	adLockOptimistic	Optimistic locking
4	adLockBatchOptimistic	Optimistic locking with batch updates

Figure 4: Possible LockType values.

Set LockType to 1, read only, if you aren’t updating records.

Generally, if you’re updating records, you should set LockType to 3, optimistic

locking. All data providers support this type of locking. When you choose optimistic locking, the record locks only while ADO physically updates the record. In contrast, a Locktype of 2 provides pessimistic locking (if the data provider supports it). In this type of locking, the record locks while you edit the record. Some data providers may not offer pessimistic locking.

Setting the LockType argument to 4 tells ADO you want to employ batch updating.

This form of updating allows you to download multiple records, update them locally, and submit them back to the data provider in a single batch. Many data providers do not support batch updating.

Options. You use the optional Options argument to indicate the type of Source

argument you provided. You can set Options to any of the options in Figure 5.

Value	Constant	Description
1	adCmdText	Command text (SQL statement)
2	adCmdTable	Table, view, or saved select query
4	adCmdStoredProc	Stored procedure or saved action query
8	adCmdUnknown	Unknown (the default)

Figure 5: Possible options values.

If you submit an SQL string as the Source argument, then set Options to 1.

If you set the Source argument to the name of a table, select query or view, then

you need to use a value of 2. Not all data providers allow you to use the

adCmdTable option.

If you want to call a stored procedure or saved action query, then you need to set

Options to 4.

By default, Options is set to a value of 8 (unknown). In this case, ADO attempts to

infer the source type from its contents.

Constants — what constants? The constants listed in Figures 3, 4, and 5 are

not predefined for you by VBScript as they are when you use ADO from VBA (with a reference to the ADO type library). You can use these constants in two ways in your code:

Define your own constants using these suggested names.

Include the file adovbs.inc "include" in your code. (If you use JavaScript, you

can include the file adojavas.inc include instead).

You can find these include files in the Program Files\Common Files\System\Ado

folder on your Web server. (Older versions of the Microsoft Web servers place

these files in the \Inetpub\wwwroot\ASPSamp\Samples folder.)

To incorporate an include file in your ASP file, use a server-side include statement

like the following:

You need to place this preceding statement outside of any script. For example,

take a look at the next block of code:

Dim con

Dim rst

Dim strSQL

Set con = Server.CreateObject("ADODB.Connection")

If you need only a few constants, however, using the entire advobs.inc include file is probably overkill and wastes a lot of memory.

Another Recordset example. The emp1.asp example displays a single record

on the Web page. More typically, you want to display a group of records on a

single page using an HTML table — as shown in the second example, cust1.asp.

The second example draws its records from the SQL Server version of the Meals

database.

Following is the complete ASP file:

<HTML>

<HEAD>

<TITLE>Cust1.asp Example</TITLE>

</HEAD>

<BODY>

<H1> Our Customers </H1>

' Declare the variables

Dim con

Dim rst

Dim strSQL

' Declare the Open method constants

Const adOpenStatic = 3

Const adLockReadOnly = 1

Const adCmdText = 1

' Create a Connection object and point it to

' the WebMealsSQL data source passing it

' a username and password

Set con = Server.CreateObject("ADODB.Connection")

con.Open "WebMealsSQL", "WebUser", ""

' Create the Recordset object and fill it with

' the records from the SQL statement, using the

' already established connection

' Since this page is used to create an HTML table,

' a static, read-only recordset is created

strSQL = "SELECT * FROM tblCustomers " & _

"ORDER BY LastName, FirstName"

Set rst = Server.CreateObject("ADODB.Recordset")

rst.Open strSQL, con, adOpenStatic, _

adLockReadOnly, adCmdText

' Only display table if there are records

' in the recordset

If Not rst.EOF Then

' Display the header

<TR>

<TD><B>Firstname</B></TD>

<TD><B>Lastname</B></TD>

</TR>

' Loop through the recordset until there

' aren't anymore records

Do While Not rst.EOF

' Display the record values

<TR>

</TR>

' If you forget this next statement,

' you'll keep printing the same record

' and create a very, very large table!

rst.MoveNext

Loop

Else

<B>No records in recordset!</B>

End If

' Clean up time

rst.Close

con.Close

</TABLE>

</BODY>

</HTML>

You can see the preceding block of code in Figure 6 as it appears in Microsoft

Internet Explorer version 4.0.

Figure 6: This page uses ADO code to fill an HTML table with records from the

tblCustomers table.

Note: If you want to run this example, you need to create a System data source

called WebMealsSQL on your Web server machine that points to the WebMeals

SQL Server database. (Alternately, if you don’t have access to SQL Server you

could point this data source to the Access version of the Meals database instead.)

In the next few sections, I highlight some of the key pieces of code in cust1.asp.

Declaring variables and constants. After the requisite HTML header, the

VBScript code in cust1.asp declares its variables and several constants that the

Open method uses:

Dim con

Dim rst

Dim strSQL

Const adOpenStatic = 3

Const adLockReadOnly = 1

Const adCmdText = 1

Using constants in your VBScript code instead of arbitrary numbers is always a

good idea. This practice makes your code more readable.

Opening a connection. The following code from cust1.asp creates a

Connection object and opens it:

Set con = Server.CreateObject("ADODB.Connection")

con.Open "WebMealsSQL", "WebUser", ""

Because the WebMealsSQL data source points to a SQL Server database, you

must provide username and password arguments. For Access databases, these

arguments are optional. If you’re using Access instead of SQL Server, you’ll need

to change the last line to:

con.Open "WebMealsSQL"

The first argument of the Connection object’s Open method, ConnectionString,

may refer to either an ODBC data source name (DSN) or a detailed ADO

connection string. All the examples in this paper use the former, but in some cases

you may need to provide further information. You need a detailed ADO connection

string when you’re using a non-ODBC data provider.

Creating the Recordset object. This section of code creates the Recordset

object using an SQL statement and links it up to the existing Connection object:

strSQL = "SELECT * FROM tblCustomers " & _

"ORDER BY LastName, FirstName"

Set rst = Server.CreateObject("ADODB.Recordset")

rst.Open strSQL, con, adOpenStatic, _

adLockReadOnly, adCmdText

What if there aren’t any records? Cust1.asp includes the following If...Then

statement to handle the case where the query returns no records:

If Not rst.EOF Then

' ...create the table ...

Else

<B>No records in recordset!</B>

End If

The preceding code uses the EOF property of the Recordset object to check if

any records are present. If the query returns an empty recordset, ADO sets the

EOF property to True, otherwise it sets EOF to False.

Displaying the table. If you have records in the recordset (rst.EOF = True), then

you create the table header with this code:

<TR>

<TD><B>Firstname</B></TD>

<TD><B>Lastname</B></TD>

</TR>

After you create the header, you need to loop through each of the records and plug

the FirstName, LastName, and City values into the rows of the HTML table. Do this by using a Do While loop and the MoveNext method of the recordset:

Do While Not rst.EOF

<TR>

</TR>

rst.MoveNext

Loop

Don’t forget the MoveNext method. (Does this sound like a voice of experience

speaking?) If you do, then the Do While loop prints the same record in an infinite

loop, producing a Web page of infinite length — probably not what you intended.

8.6 Updating Data:

Using ADO, you can update records, add new records, and delete records. The

following sections demonstrate how to accomplish these feats with several

examples.

8.6.1 Adding records to a Recordset.:

To add a new record to an ADO recordset, you

use the AddNew and Update methods of a Recordset object. First, though, you

need to create a Recordset object. After you create a non-read-only recordset,

you’re ready to add a new record. The code to add a record should look something

like this:

rst.AddNew

rst("field1") = value1

rst("field2") = value2

' ...

rst.Update

Adding a new record is always a three-step process:

1.Call the AddNew method to tell ADO you want to add a new blank record.

2.Set the values of one or more fields for the new record.

3.Call the Update method to save the new record to the database.

The next example consists of two files: an HTML file that captures a new record

using an HTML form (addcust1.html); and an ASP file (addcust1.asp) that adds the

new record to the tblCustomers table using the AddNew and Update methods.

Following is the addcust1.html file:

<HTML>

<HEAD>

<TITLE>Addcust1.html Example</TITLE>

</HEAD>

<BODY>

<H1> Adding A New Customer </H1>

<TABLE>

<TR><TD>First Name:</TD>

<TR><TD>Address:</TD>

<TR><TD>State:</TD>

<TR><TD>ZipCode:</TD>

</TABLE>

<BR><BR><A HREF="adomenu.html">Abandon edits

and return to home page</A>

</FORM>

</BODY>

</HTML>

The addcust1.html page appears in Figure 7.

Figure 7: Adding a new record to the tblCustomers table.

And following is the addcust1.asp file that addcust1.html posts its data to:

<HTML>

<HEAD>

<TITLE>Addcust1.asp Example</TITLE>

</HEAD>

<BODY>

<H1> Adding A New Customer </H1>

Dim con

Dim rst

Const adOpenKeyset = 1

Const adLockOptimistic = 3

' Create the objects

Set con = Server.CreateObject("ADODB.Connection")

Set rst = Server.CreateObject("ADODB.Recordset")

' Open the connection to the Meals database

con.Open "WebMealsSQL", "WebUser", ""

' Create a keyset recordset based on the

' tblCustomers table with optimistic locking

rst.Open "SELECT * FROM tblCustomers", con, _

adOpenKeyset, adLockOptimistic

' Create a new blank record

rst.AddNew

' Set the fields to the user-entered values

' from the html form used to collect the data

rst("FirstName")= Request.Form("FirstName")

rst("LastName")= Request.Form("LastName")

rst("Address")= Request.Form("Address")

rst("City")= Request.Form("City")

rst("State")= Request.Form("State")

rst("ZipCode")= Request.Form("ZipCode")

' Save the new record to the database

rst.Update

Response.Write "<B>Thank you!</B>"

rst.Close

con.Close

<BR><A HREF="adomenu.html">Return to home page</A>

</BODY>

</HTML>

Because addcust1.asp needs to update data, I created a keyset recordset with

optimistic locking enabled:

rst.Open "SELECT * FROM tblCustomers", con, _

adOpenKeyset, adLockOptimistic

Values from the calling HTML form get sucked into the code using the Response

object, as in:

rst("FirstName")= Request.Form("FirstName")

When you successfully submit the new record, a confirmation page, addcust1.asp,

is displayed.

What about handling errors? The code in update1.asp assumes that the new

record is always successfully added to the tblCustomers table. Unfortunately, the

fact that the record was added at this point is not a foregone conclusion. Several

things can and will (from time to time) go wrong. For example, the update may fail

for the following reasons:

You lack the correct permissions to update the records.

A field that doesn’t accept nulls is left blank.

You saved a string value to a numeric field.

You attempt to save a value that is too large for a field.

A check constraint (SQL Server) or a validation rule (Access) fails.

Always remember the one rule in computing: If something can go wrong, it will go

wrong — especially when you’re on vacation!

So what happens if you encounter an error while saving the record? The ASP

scripting engine prints an error to the page, as shown in Figure 8. The message

varies, depending on the type of error. No matter what the error, the result isn’t very professional looking.

Figure 8: An error message as displayed by default.

VBScript error handling. Unlike its cousin VBA, VBScript has limited custom

error-handling capabilities. VBScript doesn’t support the use of procedure-level

error handlers. The error-handling capabilities of VBScript consist of the ability to

turn off the default error handling (which allows you to intercept the errors and

handle them yourself) with:

On Error Resume Next

and the ability to turn default error handling back on with

On Error GoTo 0

In addition, when you turn off the default error handling, you can use the VBScript

Err object to get information about the error. For example, you can check if an error has occurred with an If...Then statement like the following:

If Err.Number <> 0 Then

' You know an error has occurred.

End If

Whenever you check its value, Err.Number equals:

0 if no error has occurred, or

a long integer containing the error number of the error that has occurred

You also have access to the error message that VBScript would have displayed.

You can find this message stored in the Description property of the Err object. You

can display the message with the following code:

Response.Write Err.Description

You must test the value of Err.Number immediately because Err.Number resets

after each statement and thus contains only information regarding the most recent

error.

The ADO errors collection. In addition to the VBScript Err object, the ADO

Connection object has an Errors collection that may contain multiple entries

many errors, if any, are in the collection. Thus, with code like this you can display all the errors that may have occurred from an ADO operation. (In the following

example, con points to an active ADO Connection object):

varErrorCount = con.Errors.Count

If varErrorCount > 0 Then

For varI = 0 To varErrorCount - 1

Response.Write "<BR><I>" & _

con.Errors(varI).Description & "</I>"

End If

Which one should I use? Why use the ADO Errors collection when you can use

the simpler VBScript Err object? A particular ADO operation sometimes may

generate a series of errors. In these cases, the ADO Errors collection provides

additional information on the problem. In most cases, however, the VBScript Err

object and the ADO Errors collection return the same information, so you’re fine,

in most cases, if you use the simpler Err object.

No matter which form of error reporting you decide to use, you must use the

VBScript On Error Resume Next statement to tell VBScript you want to intercept

the errors that VBScript normally handles.

An error-handling example. If you decide to implement custom error handling in

your ASP pages, be prepared for a bunch of extra code. Because VBScript lacks

any way to define a procedure-level error handler, you need to check for the

occurrence of an error after every VBScript statement — or at least after every

VBScript statement that you think may cause an error.

One way to lessen the error-handling load is to create a function to take care of

some of the tedium of handling errors. I create such a function in a modification to

the addcust1.asp file named addcust2.asp.

The HandleErrors function takes care of the error-handling in the following code:

Function HandleErrors(varNum, varDesc)

On Error Resume Next

If varNum <> 0 Then

Response.Write "<BR><B>The following " & _

"error occurred:</B><BR>"

Response.Write varDesc

HandleErrors = 1

Else

HandleErrors = 0

End If

End Function

The main code from addcust2.asp calls HandleErrors immediately after every

statement that may cause an error. For example, the code calls HandleErrors after

the Update method:

rst.Update

varErrors = varErrors + _

HandleErrors(Err.Number, Err.Description)

The main code passes HandleErrors the error number and the description. I define

a variable, varErrors, to keep track of the number of errors that occurred. This

approach works because HandleErrors returns a 0 if no error occurs or a 1 if an

error occurs. At the end of the main code, I know whether the record addition

succeeded and I can display the status of the record addition to the user.

Response.Write "<BR><BR>"

If varErrors = 0 Then

Response.Write "<B>Record added!</B>"

Else

Response.Write "<B>Record could not be " & _

"added because of errors!</B>"

End If

The same error that occurs in Figure 8 appears in Figure 9 using a custom

error-handling routine.

Figure 9: This error message is more professional looking and makes it clearer

that the record didn’t get added.

Going to the trouble of handling errors has two advantages:

You can display better, more user-friendly information to the user.

You can react to the error and branch in your code appropriately.

You don’t have to display the VBScript or ADO error message to the user. You can always check to see which error has occurred (using Err.Number) and then

display more user-friendly versions of some of the most common errors.

8.6.2 Updating a Record in a Recordset:

Unlike DAO and RDO, there is no Edit method of a recordset in ADO to indicate

when you want to begin editing a record. The code to edit a record should look

something like the following:

rst("field1") = value1

rst("field2") = value2

' ...

rst.Update

Editing a record is a three-step process:

1.Move to the record you want to edit.

2.Set the values of one or more fields for the record.

3.Call the Update method to save the changes to the database.

In fact, even Step 3 is optional. If you forget to use the Update method to save your

changes and move to another record, ADO automatically saves your edits for you.

Wow!

How then do you abandon your edits? Fortunately, ADO recordsets have a

method, CancelUpdate, which you can use to throw away your edits. Using the

CancelUpdate method can’t be easier:

rst.CancelUpdate

I’ve created an example that you can use to update records in the tblCustomer

table of the SQL Server Meals database. This example uses three files:

Update.asp: This page displays a list of customers and lets you click on a

customer to edit the associated record.

UpdRec.asp: This page displays the current data from the customer record

using an HTML form and includes buttons to edit or delete the record.

UpdAct.asp: This page either saves the changes or deletes the record.

In this section, I discuss the updating portion of the example files. Later in this

paper, I discuss the delete functionality of the example files.

Update.asp. Much of the code in Update.asp is identical to the code from

cust1.asp. You can use the following section of code to display the values in a

standard HTML table:

' Loop through the recordset

Do While Not rst.EOF

' Display the record values

<TR>

<!--Create a hyperlink in the left column

that passes the CustomerId to the

UpdRec.asp page -->

<TD><A HREF="UpdRec.asp?CustomerId=

<%= rst("CustomerId") %>">

<%= rst("Name") %></A></TD>

</TR>

' Move to the next record

rst.MoveNext

Loop

The only way the preceding code differs from the code in cust1.asp is the part that

creates the first column of the table. The preceding example uses the <A> tag to

create a hyperlink that displays the customer’s name from the Name field, but sets

up a hyperlink to UpdRec.asp, passing this page the string "CustomerId=" and the

value of the CustomerId for that record.

This sends HTML to the browser that looks like the following (for the record with a CustomerId = 7):

<A HREF="UpdRec.asp?CustomerId=7">Alicia Brown</A>

Take a look at Figure 10 to see how update.asp appears in Netscape Navigator.

Figure 10: The Update.asp page lets you select any customer for updating.

UpdRec.asp. The code behind UpdRec.asp is responsible for displaying the

values from the requested record, as well as two submit buttons that determine the

action to take on the record.

Following is the complete UpdRec.asp file:

<HTML>

<HEAD>

<TITLE>UpdRec.asp Example</TITLE>

</HEAD>

<BODY>

<H1> Customer Record Update Page </H1>

' Declare the variables

Dim con

Dim rst

Dim strSQL

' Declare the Open method constants

Const adOpenStatic = 3

Const adLockReadOnly = 1

Const adCmdText = 1

' Create a Connection object and point it to

' the WebMealsSQL data source

Set con = Server.CreateObject("ADODB.Connection")

con.Open "WebMealsSQL", "WebUser", ""

' Create a recordset with one record in it

' based on the CustomerId passed to this page

' from the update.asp page

strSQL = "SELECT * FROM tblCustomers " & _

"WHERE CustomerId =" & Request("CustomerID")

Set rst = Server.CreateObject("ADODB.Recordset")

rst.Open strSQL, con, adOpenStatic, _

adLockReadOnly, adCmdText

' Only display form if there is a matching

' record

If Not rst.EOF Then

<INPUT Type="HIDDEN" NAME="CustomerId"

Value="<% = rst("CustomerId") %>">

<TABLE>

<TR><TD>CustomerId:</TD><TD>

</TD></TR>

<TR><TD>First Name:</TD><TD>

<INPUT NAME="FirstName"

Size=20 Value="<% = rst("FirstName") %>">

</TD></TR>

<INPUT NAME="LastName"

Size =20 Value="<% = rst("LastName") %>">

</TD></TR>

<TR><TD>Address:</TD><TD>

<INPUT NAME="Address"

Size=40 Value="<% = rst("Address") %>">

</TD></TR>

<INPUT NAME="City"

Size =20 Value="<% = rst("City") %>">

</TD></TR>

<TR><TD>State:</TD><TD>

<INPUT NAME="State"

Size =6 Value="<% = rst("State") %>">

</TD></TR>

<TR><TD>ZipCode:</TD><TD>

<INPUT NAME="ZipCode"

Size =10 Value="<% = rst("ZipCode") %>">

</TD></TR>

</TABLE>

<BR><INPUT TYPE="SUBMIT" Name="Submit"

VALUE="Save Changes">

<INPUT TYPE="SUBMIT" Name="Submit"

VALUE="Delete Record">

</FORM>

Else

<B>No record was found matching this CustomerID!</B>

End If

' Clean up time

rst.Close

con.Close

<A HREF="update.asp">[Abandon edits and select another customer]</A>

<A HREF="adomenu.html">[Return to home page]</A>

</BODY>

</HTML>

The preceding page creates a recordset based on the CustomerId value passed

to the page by the update.asp page. Following is the SQL for the recordset:

strSQL = "SELECT * FROM tblCustomers " & _

"WHERE CustomerId =" & Request("CustomerID")

The following statement sets up the form to post its data to the UpdAct.asp page

when the user clicks the Submit button:

The form displays the CustomerId value as regular HTML text rather than as a form field to prevent the user from changing it. I also include, however, a hidden control on the form that contains the CustomerId value so it can be passed along to the UpdAct.asp page:

<INPUT Type="HIDDEN" NAME="CustomerId"

Value="<% = rst("CustomerId") %>">

A good way to pass along information between pages is to store it in hidden

controls.

The UpdRec.asp page contains two Submit buttons. Because I include Name

subtags for both Submit buttons and give them the same name — in this example

Submit (but there’s nothing special about this name) — the name and value pair

post to UpdAct.asp (see Figure 11). Thus, the code in UpdAct.asp can tell which

button the user clicks. This trick doesn’t work unless you include the Name subtag

in the Submit button tags.

Figure 11: You can edit a customer record using the UpdRec.asp page.

UpdAct.asp. The UpdAct.asp page completes the update operation by opening a

recordset based on the edited record from UpdRec.asp and saving the updated

values back to the record. Much of this code is similar to the two other pages, so in

this section I highlight only key portions of the code.

In this portion of code from UpdAct.asp, the LockType argument of the rst.Open

method is set to adLockOptimistic to allow for editing:

rst.Open strSQL, con, adOpenStatic, _

adLockOptimistic, adCmdText

The following If...Then statement determines which of the Submit buttons on

UpdRec.asp the user clicked:

If Request("Submit")= "Save Changes" Then

The updated values carry over from UpdRec.asp, and the Update method saves

the changes to the database in this stretch of code:

rst("LastName") = Request("LastName")

rst("FirstName") = Request("FirstName")

rst("Address") = Request("Address")

rst("City") = Request("City")

rst("State") = Request("State")

rst("ZipCode") = Request("ZipCode")

rst.Update

8.6.3 Deleting a Record from a Recordset:

Deleting a record in ADO is pretty simple. You use the Delete method of the

Recordset object to delete the current record. The code looks like the following:

rst.Delete

Deleting a record is a two-step process:

1.Move to the record you want to delete.

2.Call the Delete method to delete the record from the database.

Following is the code in UpdAct.asp that deletes the record:

If Request("Submit")= "Save Changes" Then

'...

Else

' Delete record

rst.Delete

'...

End If

8.7-Error Trapping Database Code :

Ex.

First add some error code to the script to display the messages and

bad SQL that causes the problem, for example:

on error resume next

...

Set Conn = Server.CreateObject("ADODB.Connection")

...

SQLstmt = "..."

Set RS = Conn.Execute(SQLStmt)

If err.number>0 then%>

VBScript Errors Occured:<P>

Error Number=<%=err.number%><P>

Error Descr.=<%=err.description%><P>

Help Context=<%=err.helpcontext%><P>"

Help Path=<%=err.helppath%><P>

Native Error=<%=err.nativeerror%><P>

Source=<%=err.source%><P>

SQLState=<%=err.sqlstate%><P>

<%else%>

No problems occured!<p>

<%end if

IF conn.errors.count> 0 then%>

Database Errors Occured<P><%=SQLstmt%><P>

<%for counter= 0 to conn.errors.count%>

Error #<%=conn.errors(counter).number%><P>

Error desc. -><%=conn.errors(counter).description%><p>

<%next

else%>

Everything Went Fine

<%end if

Conn.Close%>

8.8 Examples :

8.8.1 The .maxrecords property:

This example demonstrates the capabilities how to display a table from a SQL statement but limit the output to a specific number of

records.

<TITLE>dbmaxrecs.asp</TITLE>

</head><body bgcolor="#FFFFFF">

set rstemp=Server.CreateObject("adodb.Recordset")

rstemp.maxrecords=15

connectme="DSN=Student;uid=student;pwd=magic"

rstemp.open "select * from titles", _

connectme,adopenstatic

' table display

howmanyfields=rstemp.fields.count -1

for i=0 to howmanyfields %>

<% next %>

</tr>

do while not rstemp.eof %>

<tr>

<% for i = 0 to howmanyfields%>

<% next %>

</tr>

rstemp.movenext

loop

</table></body></html>

8.8.2 Paged Table Displays

This example demonstrates the capabilities how to retrieve a recordset divided up into pages, and to only select data from a specified page. It does not in any way store a recordset or connection in memory when the page is not accessed unlike many solutions you will read about. The ADO properties that make this magic possible are .AbsolutePage, .PageCount and .PageSize.

<TITLE>dbtablepaged.asp</TITLE>

</head><body bgcolor="#FFFFFF">

mypage=request.querystring("whichpage")

If mypage="" then

mypage=1

end if

mypagesize=request.querystring("pagesize")

If mypagesize="" then

mypagesize=10

end if

set rstemp=Server.CreateObject("ADODB.Recordset")

connectme="DSN=Student;uid=student;pwd=magic"

sqltemp="select * from publishers"

rstemp.cursorlocation=aduseclient

rstemp.cachesize=5

rstemp.open sqltemp,connectme

rstemp.movefirst

rstemp.pagesize=mypagesize

maxcount=cint(rstemp.pagecount)

rstemp.absolutepage=mypage

howmanyrecs=0

howmanyfields=rstemp.fields.count -1

response.write "Page " & mypage & " of " & maxcount & "<br>"

<% 'Put Headings On The Table of Field Names

for i=0 to howmanyfields %>

<%next%>

</tr>

<%do while not rstemp.eof and howmanyrecs<rstemp.pagesize%>

<tr>

<%for i = 0 to howmanyfields%>

<%next%>

</tr>

rstemp.movenext

howmanyrecs=howmanyrecs+1

loop

rstemp.close

set rstemp=nothing

</table><p>

pad="0"

scriptname=request.servervariables("script_name")

for counter=1 to maxcount

If counter>=10 then

pad=""

end if

ref="<a href='" & scriptname & "?whichpage=" & counter

ref=ref & "&pagesize=" & mypagesize & "'>" & pad & counter & "</a>"

response.write ref & " "

if counter mod 10 = 0 then

response.write "<br>"

end if

</body></html>