This article illustrates how you can easily connect to and manipulate SQL databases using ActiveX Data Objects (ADO) with Active Server Pages (ASP)

Connection

To access a database we first need to open a connection to it, which involves creating an ADO Connection object.

We then specify the connection string and call the Connection object’s Open method.

I tend to create a seperate file called OpenDataConnection.asp and include this file with every page that needs a database connection.

Dim SQLConnection
Set SQLConnection = Server.CreateObject("ADODB.Connection")
SQLConnection.ConnectionString = "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; UID=BlogIT; PWD=BlotIT"
SQLConnection.Open

Disconnection

Disconnecting from the database is very important as it releases any resources tied to it, Use the objects Close method at the end of your code, or alternativly create a file called CloseDataConnection.asp and include it as a footer at the end of each file that has OpenDataConnection.asp included.

SQLConnection.Close
set SQLConnection=nothing

Retrieve Data

One of the most common tasks in ASP web application is the retrieval of data from a database. This is achieved via the ADO RecordSet object. Using this objects Open method we can pass in any SQL string that our database driver supports.

Here we will retrieve the contents of the Customer table from the Northwind database, and place them in an array.

<%
Dim rsCustomer, strSQL, intCustomerCount, arrCustomerDataSet rsCustomer = Server.CreateObject("ADODB.RecordSet")
rsCustomer.CursorLocation = 3
strSQL = "SELECT * FROM Customers "
rsCustomer.Open strSQL, SQLConnection
if not (rsCustomer.BOF or rsCustomer.EOF) then
 intCustomerCount = rsCustomer.RecordCount
 arrCustomerData = rsCustomer.GetRows()
else
 intCustomerCount = 0
end if
rsCustomer.Close
set rsCustomer = nothing
%>

Now that we have the data in our array arrCustomerData, we can simply loop through this dataset and display on screen

<%
Dim intCustomerLoop
intCustomerLoop = 0
if intCustomerCount <> 0 then
 do until intCustomerLoop = intCustomerCount
  response.write(arrCustomerData(0, intCustomerLoop))
  response.write(arrCustomerData(1, intCustomerLoop))
  response.write(arrCustomerData(2, intCustomerLoop))
  response.write(arrCustomerData(3, intCustomerLoop))
  intCustomerLoop = intCustomerLoop +1
 loop
end if
%>

Manipulate Data

In order to use “INSERT INTO”, “UPDATE” or “DELETE” SQL statements, we need to amend the above RecordSet object to allow updates. This is achieved by using two properties of the RecordSet object, CursorType and LockType

Insert

<%
 Dim rsCustomer, strSQLSet rsCustomer = Server.CreateObject("ADODB.RecordSet")
strSQL = "INSERT INTO Customers(CompanyName, ContactName, ContactTitle) VALUES('BlogIT','CodeMonkey','Administrator')"
rsCustomer.CursorType = 2
rsCustomer.LockType =3
rsCustomer.Open strSQL, SQLConnection
set rsCustomer = nothing
%>

Update

<%
Dim rsCustomer, strSQLSet rsCustomer = Server.CreateObject("ADODB.RecordSet")
strSQL = "UPDATE Customers SET ContactName='CodeMonkey' WHERE CustomerID='ALFKI'"
rsCustomer.CursorType = 2
rsCustomer.LockType =3
rsCustomer.Open strSQL, SQLConnection
set rsCustomer = nothing
%>

Delete

<%
Dim rsCustomer, strSQLSet rsCustomer = Server.CreateObject("ADODB.RecordSet")
strSQL = "DELETE FROM Customers WHERE CustomerID='ALFKI'"
rsCustomer.CursorType = 2
rsCustomer.LockType =3
rsCustomer.Open strSQL, SQLConnection
set rsCustomer = nothing
%>