DevNet > ASP > Databases

	Sign In Sign-Up

Home > ASP > Databases

Quick Links
T-SQL Reference Recordset Object reference

Using databases with ASP

ASP is the ideal tool for manipulating databases to create web-enabled applications. This section demonstrates some of the methods for accessing databases from ASP pages.

A knowledge of the SQL database query language is recommended before reading this section.

Database connectivity
Executing queries
Processing query results

Search

Site Map

Database Connectivity

A pre-requisite for performing database operations is connectivity to the database. From ASP, this is done through the use of a provider string. A provider string contains all the necessary information to allow the database driver to connect to the database. It contains the following information:

Driver identification
Database machine identification
User name
Password
Database name

Here is an example provider string:

strProvider = "provider=sqloledb;data source=database_machine.developer.4mg.com;uid=myusername;pwd=mypw;database=mydatabase"

provider: Identifies the driver used to access to the database. In this case, sqloledb identifies a Microsoft SQL server driver.
data dource: Identifies the name (partially- or fully-qualified DNS, or WINS) or IP address of the machine on which the database is hosted.
uid: The username used to log into the database.
pwd: The password used to log into the database.
database: The database to be used.

Executing queries

Queries are executed using an object of type ADODB.Recordset. The following code sample shows how a recordset is created, using the results from a query.

<%

strProvider = "provider=sqloledb;data source=database_machine.developer.4mg.com;uid=myusername;pwd=mypw;database=mydatabase"

'create the Recordset object
Set queryResults = Server.CreateObject("ADODB.Recordset")

'Open the recordset object and fill it with the results of the query "SELECT * FROM USERS"
queryResults.Open "SELECT * FROM USERS", strProvider, adUseClient, adOpenDynamic

'do some processing with the results

'delete the object
Set queryResults = Nothing

Processing query results

Once a query has been executed, the results can be processed. Results can only be accessed one row at a time, and the data in each column can be accessed using a numeric column index (0 is the left-most column index), or the column alias name.

The following code sample illustrates this process.

<%

strProvider = "provider=sqloledb;data source=database_machine.developer.4mg.com;uid=myusername;pwd=mypw;database=mydatabase"

'create the Recordset object
Set queryResults = Server.CreateObject("ADODB.Recordset")

'Open the recordset object and fill it with the results of the query "SELECT * FROM USERS"
queryResults.Open "SELECT * FROM USERS", strProvider, adUseClient, adOpenDynamic

'do some processing with the results
Response.Write("The following users are in the database:")

'execute a loop to access each row in the recordset
'when queryResults.EOF returns True, there are no more records in the recordset

Do until queryResults.EOF

Response.Write "<p>User name: " & RTrim(queryResults(0)) & "</p>" &
Response.Write vbCrLf
Response.Write "<p>Email address: " & RTrim(queryResults("EmailAddress")) & "</p>"
Response.Write vbCrLf

'move to the next row in the recordset
queryResults.MoveNext()

Loop

'delete the object
Set queryResults = Nothing

An SQL query will return results that are the same length as the space allocated in the database, regardless of how much space is actually occupied by the data. Therefore, the RTrim function is used to remove the trailing whitespace (spaces & tabs) from the right-hand end of the string argument passed to it.