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:
ConnectionString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:\inetpub\wwwroot\db\examples.mdb"
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.
SQL="SELECT * FROM CARS"
Recordset.Open SQL, Connection
We have returned a recordset based on our SQL statement so let's now print out them in the browser.
If Recordset.EOF Then
Response.Write("No records returned.")
Do While NOT Recordset.Eof
Finally, need to close the objects and free up resources on the server.
ADO and ADO.NET Connection Strings