Asp Material 3

Jatin Soni : 9904181899 : Hi Friends.

Part III

Databases and ASP

Introduction At one time, connecting to a database was difficult. Databases came in a variety of formats, and you had to know a low-level API (Application Programming Interface) for every database you wanted to use. There was a push for a universal API that would work for a wide variety of data stores. ODBC, or Open Data Base Connectivity, was developed as an attempt to create this universal API. ODBC provides a common standard for communicating with a database using SQL. Many databases conformed to the standard and became known as ODBC-compliant databases, such as Access, MS-SQL Server, Oracle, Informix, and so on. ODBC has made life easier for database developers by creating common standard. However, ODBC had some faults. It still contained many low-level calls and was difficult to develop with. Developers still had all focus on lowlevel communications with the database. Also another problem with ODBC is that it was developed with narrow purpose of working with databases that support SQL. People wanted a way just to get the data they needed and use it how they saw fit. Microsoft’s solution was DAO, or Data Access Objects. DAO was followed by RDO (Remote Data Objects, targeted for distributed database architecture), and the ADO (ActiveX Data Objects). All these have shortcomings, however. According to Microsoft, “ODBC Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

provides native access to SQL data” and “DAO provides high-level objects to data.” Even DAO and RDO require the data in a data store to be in SQL (Structured Query Language) format. In response to these shortcomings, Microsoft introduced OLEDB, a COM-based data access object that provides access to all types of data and even provides access to disconnected data stores. OLEDB was designed to work with a much wider range of data sources. For example, Microsoft Exchange, Microsoft Index Server, ADSI and so on. OLEDB sort of replaces ODBC. It includes and ODBC driver so that it is compatible with all the ODBC data sources. You can see how OLEDB works with your data source in Figure. Active Server Page

ActiveX Data Objects (ADO)

OLEDB

ODBC

Access

SQL

Figure: The hierarchy of data access.

Jatin Soni : 9904181899 : Hi Friends.

Other Data Providers

Jatin Soni : 9904181899 : Hi Friends.

Working With Connections And Data Sources The most commonly used Data Sources are: 1. ADO – The ActiveX Data Objects provides an application-

level interface to data providers such as Microsoft SQL Server or Microsoft Access. ADO is directly used within Active Server Pages TO communicate with such databases. To access a database with an ASP page, ADO has to be used. The ADO contains the actual collection of objects that are used within scripts to create a connection to a database and read records from database table. Thus, ADO enables the retrieval and storage of data in database. 2. OLEDB – OLEDB is a System-level interface to data providers

such as Microsoft SQL Server of Microsoft Access. For example, ADO

use

a

set

of

interfaces

provided

by

OLEDB

to

communicate with Microsoft SQL Server. OLEDB is never used directly within Active Server Pages. Instead ADO is used as a higher-level

interface

to

OLE

DB.

The

ADO

does

not

communicate directly with the data source. Instead, the ADO communicates through an intermediary interface called OLEDB. After making the connection to a database, OLEDB does all its work in the background. Accessing OLEDB within an ASP page directly is not allowed. However, while making the connection to the database, knowledge of OLEDB driver is a must because there are choices available to access the database through different OLEDB providers. There are two ways that an OLEDB Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

provider may provide access to a database: either indirectly, through an ODBC driver, or directly, if it’s a native OLEDB provider. 3. ODBC

–

Once

Connectivity

upon

Interface

a

time,

was

the

used

Open

as

the

Data

Base

standard

for

communicating with databases. Almost all database on the market today are compatible with ODBC. However, Microsoft is gradually replacing ODBC with OLEDB. Currently, Microsoft has OLEDB providers for such databases as Microsoft SQL Server, Oracle, and Microsoft Jet (MS Access). Microsoft has also developed an OLEDB provider for ODBC to use with databases that do not have their own OLEDB provides. Therefore, there are currently two methods for using the ADO to create a connection to a database. On one hand, create a connection using the OLEDB provider for ODBC drivers. This type of connection will work with any database that has an ODBC driver. On the other hand, create a connection using only a native OLEDB provider.

Communicating with a database Using Active Data Objects (ADO) ActiveX Data Objects (ADO) comes with ASP and allows your pages to easily connect to databases. ADO works with any OLEDB source, which includes ODBC-compliant sources. So it will work with most databases currently being used. The ADO contains all Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

the objects that will be normally used within the Active Server Pages. The ADO model contains six objects. Here’s a brief overview of the main objects contained in the ADO:

1.The Connection Object The Connection object represents all the features of a connection to the data source. The Connection object connects you to the data source. Obtaining a connection is the first step to working with

databases.

A

connection

must

be

opened

with

the

Connection object before communication with the data source can

happen.

Typically,

a

Connection

object

is

used

to

communicate with the databases such as Microsoft SQL Server or Microsoft Access. However, connections can be opened to other data sources such as normal text file Microsoft Excel, Microsoft Exchange, Microsoft Index

2.The Recordset Object The Recordset object allows you to work with the data in a table. The Recordset object contains a set of rows from a table. It can be used to read through the rows of a table, modify the rows of a table, or collect new data to be added to the table. A Recordset object represents the rows of data returned from the data source. For example, a select query is fired on a database, the rows of data returned from the database are returned as a set of records represented by the Recoedset object.

Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

3.The Error Object The Error object represents and error generated by the data source. The errors collection is used when a single failed method call is allowed to generate multiple errors.

4.The Field Object The Field object represents a single column in the table.

5.The Command Object The Command object provides another way to create a Recordset object. It combines the Recordset object and the Connection object. The Command object represents a command that can be executed against a data source. Typically, the Command object is used to execute a SQL stored procedure. The Command object represents a single column in the table.

6.The Parameter Object The Parameters collection contains any parameters needed by the command. The parameters are stored in Parameter object. You can see a diagram of how these objects relate to each other in below figure. Figure: The ADO objects and their relations.

Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

Parameter Object

Parameter Object

Parameter Object

Parameter Object

Parameters Collection Command Object

Error Object

Connection Object

Recordset Object

Errors Collection

Fields Collection

Error Object

Error Object

Field Object

Field Object

Field Object

Field Object

I. Connecting to a DataBase: First you need to create a new database. Before you can do anything (e.g. like talking) to the database, you need to get a connection to it. 

The Connection Object

The Connection objects used to hold information about the data store you want to access. It is created the same way your other components have been, using

Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

Server.CreateObject Dim objConn Set objConn = Server.CreateObject(“ADODB. Connection”) There may be times when you want connections to several data stores open at once. You might be using multiple Access databases, or mixing Access, Oracle, SQL, or whatever. In such cases, you would use multiple instances of the Connection object.

Using a System DSN



A System DSN is a file that contains information about the database such as where it is and what kind of database it is DSN stands for Data Source Name. Creating a System DSN is easy: i.

Close Access.

ii.

In Windows 2000, click Start button; go to Settings, Control Panel, Administrative Tools, Data Sources (ODBC). Or, go to Start, Administrative Tools, Data Sources.

iii.

Go the System DSN tab. You should see something like Figure below. There is a listing of the system data source currently configured. Along the right are three buttons that allow you to add a new System DSN, remove the selected one, or change the settings of the currently selected one. Figure: Viewing the Data Source Administrator System DSN tab.

Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

iv.

You want to create a new DSN, so click Add.

v.

You will see a list of drivers, like in Figure. Choose Microsoft Access Driver (*.mdb) and click Finish. Figure: Creating a new System DSN for the Widget World

database.

Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

vi.

Now the setup box pops up. Enter WidgetWorld.dsn for the Data Source Name. This is the name you will use to reference the DSN in your ASP pages. You may enter a description as well. It should be something to remind you what this database and DSN are for.

vii.

Click Select. Now choose the .mdb file for the database. We named it WidgetWorld.mdb.

viii.

Click OK to select the database. Your screen should look similar to Figure. Click OK again to finish creating the DSN. You should see the DSN you just created listed under System Data Sources.

ix.

Click OK to exit.

Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

Now you have a System DSN. You can tell the Connection object about

it

like

this:

ObjConn.ConnectionString

=

“DSN=WidgetWorld.dsn” 

Using a DSN-less Connection

There is an alternative to using a System DSN. Instead of putting the connection information into the DSN, you can put it into the connection string. You saw an example of this kind of connection yesterday. Here is the connection string we used: ObjConn.ConnectionString=”DRIVER={MicrosoftAccessDriver (*.mdb)};” & “DBQ=C:\My Documents\WidgetWorld.mdb” The DRIVER=line tells the Connection object what kind of database it is connecting to. The DBQ=line indicates where on the server the database resides. This must be a complete physical path. If you do not know the complete physical path, use Server.MapPath. You can specify other values in the connection Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

string. If you are connecting to a SQL database, you may need a username and password to access it. You can specify them with UID and PWD. You would also need to specify DATABASE, DRIVER, and SERVER information. There are drawbacks to using DSN-less connections. First of all, it requires you to figure out all the information you need. Also, if you use a DSN-less connection, the connection information must be revalidated every time. With a System DSN, this must only be done once, when creating the System DSN. 

Opening The Connection

So far, all you have done is create the Connection object and tell it about your database. You have yet to actually open the connection. To do this, use the Open method of the Connection object: ObjConn.Open This is the same whether or not you use a System DSN. So let’s put it all together now: <% Dim objConn Set objConn = Server.CreateObject(“ADODB.Connection”) objConn.ConnectionString = “DSN=WidgetWorld.dsn” objConn.Open %> This creates the Connection object, sets the connection string, and opens the connection. This assumes that a System DSN was

Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

used. If you prefer, you may change the connection string for a DSN-less connection. 

Closing the Connection

As with any object, you need to free the memory associated with it when you are finished. However, before you do that, you need to close the connection. You close a connection and then free the object like this: ObjConn.Close Set objConn=Nothing Always close and free record sets associated with a connection before closing and freeing the connection. If you are finished with one connection but want to open up another, you can reuse the Connection object you just finished with. Simply close it, set the connection information appropriately, and reopens it.

II. Reading data from a database So now you know how to connect to a database. Aside from looking at all the properties, there is not much you can do with just the Connection object. To work with the data in the database, you will need another object. The Recordset object is another of ADO’s objects. 

The Recordset Object

A recordset is simply a set of records. The Recordset object may be used to contain a subset of the records in a table, or even all the records in the table. Often, you will use the Recordset object to host a specially chosen set of records. For example, in a table Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

containing information about users of your product, you might want to retrieve all the records pertaining to users in the Chicago area. First, you need to instantiate the Recordset object. Here is how you do it: Dim objRS Set objRS=Server.CreateObject(“ADODB.Recordset”) Simply declare the variable that will hold the object and then use Server.CreateObject. The Recordset object is part of the ADODB package. Creating the object instance does not fill it with any data. To do that, you need Open. 

The Open Method

The Recordset object is filled with records by using the Open method. The Open method can accept many different sets of arguments and can be used in many different ways. Its general form is like this: Recordset.Open source, connection, cursortype, locktype, commandtype Source is either a Command object or a string containing a recognized command. Connection is either a Connection object or a string containing the connection information. If the string is used, it must contain all the information that would go in the ConnectionString property of the Connection object. Cursortype indicates the way you want to move through the recordset.

Its

default

Jatin Soni : 9904181899 : Hi Friends.

value

is

the

ADO

constant

Jatin Soni : 9904181899 : Hi Friends.

adOpenForwardOnly, which is the most commonly used cursor type. adOpenForwardOnly indicates that you can only move forward through the recordset. The cursor type also controls how changes other users make to the table affect what you see. Locktype affects whether you can write to the table, and if so, how. Because several clients may be accessing the table at once, you need to be careful how you make changes to it. The default value of Locktype is the ADO constant adLockReadOnly. This means that you can only read from the table, not write to it. Commandtype indicates how the source parameter should be evaluated. We will use the Ado constant adCmdTable. This specifies that source should be evaluated as a table name. Here is an example of a common use of the Open method: ObjRS.Open “mytable”, objConn, , , adCmdTable Notice first that the command type is specified as adCmdTable. This means that the source string should be evaluated as a table name. So if there is a table named “mytable” with the database indicated by the Connnection object objConn, that table will be copied into the Recordset object objRS. The arguments for the cursor type and lock type are not specified. This means that you will only be able to move forward through objRs and that you can only read from the table. 

Reading

and

Displaying

Database Table

Jatin Soni : 9904181899 : Hi Friends.

the

Contents

of

a

Jatin Soni : 9904181899 : Hi Friends.

Now you know how to open a recordset. The next topic is how to move through it and retrieve values from it. To do this, you will need a few more methods and properties of the Recordset object. The Recordset object may contain several records, but you can only access one at a time. The Recordset object maintains a pointer to the current record, and you can move that pointer forward. If you have not set the recordset to forward-only movement, you can also move pointer backward. MoveNext advances to the next record in the recordset. MovePrevious goes back to the previous record in the recordset. This is not valid if the recordset is set to forward-only movement. MoveFirst is a method that moves to the first record in the recordset. MoveFirst may be used even if movement is set to forward-only movement, but it may result in re-executing the original Open method. MoveLast goes directly to the last record in the recordset. Move number goes to number records from the current one. Because trying to move to either after the last record or before the first one causes errors, you need some way of finding out whether you are at the beginning or end of the recordset. A pair of properties tells you this. BOF is a Boolean value that is True while you are still at the beginning of the recordset. EOF is a Boolean value that is set to True when you reach the end of the recordset. You access data from the current record like this: Jatin Soni : 9904181899 : Hi Friends.

Jatin Soni : 9904181899 : Hi Friends.

RecordsetObjectName(“FieldName”) This returns the value of the field specified by FieldName in the current row of the recordset.

Jatin Soni : 9904181899 : Hi Friends.