Using ADO with ASP

by Jan Verhoeven, 2 November 2001

This article provides some common routines that you will use very often when using Active Data Objects (ADO) together with Active Server Pages (ASP). With little or no modification the routines can also be used in e.g. VB, Delphi, VBA etc.

Connecting to the database.

The first thing you have to do when using a database is connect to it. Use a seperate connect.asp page that you include with every page that needs a database connection.

appldir = Request.ServerVariables("APPL_PHYSICAL_PATH")
appldb = appldir & "..\db\mysurvey.mdb"
'allow data shaping
set db = server.createobject("ADODB.connection")
db.Provider = "MSDataShape"
constr="DATA PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & appldb db.open(constr)

First we retrieve the physical path to our web site. Next we construct to full filename of our database. It is best not to put your database in the path of your web site. Put the database in a parrallel folder:

mySurvey\db\mysurvey.mdb
mySurvey\site\default.asp

Then we create the Connection object. In this case I use both the Microsoft datashape privider and the Jet engine provider. Using both providers in one connection has no performance penalty and offers the benefit of datashaping which you will use a lot with hierarchical data structures. If you do not want to use data shaping you can use:

appldir = Request.ServerVariables("APPL_PHYSICAL_PATH")
appldb = appldir & "..\db\mysurvey.mdb"
set db = server.createobject("ADODB.connection")
constr="PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & appldb db.open(constr)

Within your asp pages you use the db variable for all your database queries.

Disconnecting from the database

Disconnecting from the database is very important to release resources. At the end of your code or before any redirect in your page use the following code:

db.close
set db=nothing

Retrieving data

One of the common tasks in a ASP web application is the retrieval of data from a database.

strSQL="SELECT * FROM t_programs"
set rs=db.execute(strSQL)
if not rs.eof then
  do
    id_program=rs("id_program").value
    strProgram=rs("strProgram").value
    rs.movenext
  loop until rs.eof
end if
rs.close
set rs=nothing

The above code demonstrates how to pick up data from a database table.

Updating data

Updating data can be done via a SQL insert statement or via an updatable recordset.

strSQL="UPDATE t_programs SET strProgram='BackEdit' WHERE id_program=6"
db.execute(strSQL)

The above asumes that the id_program field is an autonumber field.

When you want to update via a recordset you first have to open an updatable recordset.

  strSQL="SELECT * FROM wst_questiongroup WHERE id_questiongroup=5"
  set rs = server.createObject("ADODB.Recordset") 
  rs.open strSQL, db, 2, 2
  rs("strCode").value=request.form("strCode")
  rs("strTitle").value=request.form("strTitle")
  rs.update

The parameters 2, 2 mean adOpenDynamic and adLockPessimistic respectively.

Retrieving Autonumber

When we insert a new record into a table with an autonumber field, using (INSERT INTO) we can retrieve the value of this field for use in subsequent queries with:

  strSQL="SELECT @@Identity"
  set rs=db.execute(strSQL)
  new_id=rs(0).value
  rs.close
 

This is a safe way, much better than the select max(id) FROM atable that is sometimes used.