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 %>