How to Connect to Database using a Connection String

Search

or browse popular tags

ASP Tutorial

ASP Objects

Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

How to Connect to Database using a Connection String

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
Dim Connection
'create an ADO connection object
Set Connection=Server.CreateObject("ADODB.Connection")

'declare the variable that will hold the connection string
Dim ConnectionString
'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
Connection.Open ConnectionString
%>

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
Dim Recordset
'create an ADO recordset object
Set Recordset=Server.CreateObject("ADODB.Recordset")

'declare the variable that will hold the SQL statement
Dim SQL
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.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Response.write Recordset("Name")
Response.write Recordset("Year")
Response.write Recordset("Price")
Response.write "<br>"
Recordset.MoveNext
Loop
End If
%>

Finally, need to close the objects and free up resources on the server.

<%
Recordset.Close
Set Recordset=Nothing
Connection.Close
Set Connection=Nothing
%>

ADO and ADO.NET Connection Strings

Tags:

Add To: dzone | digg | del.icio.us | stumbleupon

Comments