The power of Active Server Pages comes through when we tie databases to our web sites. To connect to a database, ASP uses what is called ActiveX Data Objects, or ADO for short. ADO comes with ASP, and can be used easily from your ASP pages.
In this tutorial we will show you how to connect to our Access database called ‘examples.mdb’ and retrieve all the records from the table ‘cars’.
At first we must create an instance of the connection object and feed it the location of the database we wish to connect to and the driver that we intend to use. For these purposes we will use a connection string. You can choose an ODBC or an OLEDB connection string. We will use OLEDB for our example as it’s faster and more stable. Next, we should open the connection to the database:
'declare the variable that will hold new connection object
'create an ADO connection object
'declare the variable that will hold the connection string
'define connection string, specify database driver and location of the database
ConnectionString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:\inetpub\wwwroot\db\examples.mdb"
'open the connection to the database
Great, now we have an active connection to our database. Let’s retrieve all the records from the ‘Cars’ table. For that we have to create an instance of the recordset object and feed it an SQL statement.
'declare the variable that will hold our new object
'create an ADO recordset object
'declare the variable that will hold the SQL statement
SQL="SELECT * FROM CARS"
'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL, Connection
We have returned a recordset based on our SQL statement so let’s now print out them in the browser.
'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Finally, need to close the objects and free up resources on the server.