Database Discussion - Database Connection In ADOCON

Print Page | Close Window

Database Connection In ADOCON

Printed From: S2H.co.uk
Category: Programming & Web Design
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: http://www.s2h.co.uk/forum/forum_posts.asp?TID=68
Printed Date: 26 Mar 2026 at 11:50pm

Topic: Database Connection In ADOCON

Posted By: Jeet Chowdhury
Subject: Database Connection In ADOCON
Date Posted: 13 Nov 2007 at 8:22pm

Create a DSN-less Database Connection

The easiest way to connect to a database is to use a DSN-less connection. A DSN-less connection can be used against any Microsoft Access database on your web site.

If you have a database called "northwind.mdb" located in a web directory like "c:/webdata/", you can connect to the database with the following ASP code:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

%>

Note, from the example above, that you have to specify the Microsoft Access database driver (Provider) and the physical path to the database on your computer.

Create an ODBC Database Connection

If you have an ODBC database called "northwind" you can connect to the database with the following ASP code:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Open "northwind"

%>

With an ODBC connection, you can connect to any database, on any computer in your network, as long as an ODBC connection is available.

An ODBC Connection to an MS Access Database

Here is how to create a connection to a MS Access Database:

1. Open the ODBC icon in your Control Panel.

2. Choose the System DSN tab.

3. Click on Add in the System DSN tab.

4. Select the Microsoft Access Driver. Click Finish.

5. In the next screen, click Select to locate the database.

6. Give the database a Data Source Name (DSN).

7. Click OK.

Note that this configuration has to be done on the computer where your web site is located. If you are running Personal Web Server (PWS) or Internet Information Server (IIS) on your own computer, the instructions above will work, but if your web site is located on a remote server, you have to have physical access to that server, or ask your web host to do this for you.

The ADO Connection Object

The ADO Connection object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database.

Create an ADO Table Recordset

After an ADO Database Connection has been created, as demonstrated in the previous chapter, it is possible to create an ADO Recordset.

Suppose we have a database named "Northwind", we can get access to the "Customers" table inside the database with the following lines:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs=Server.CreateObject("ADODB.recordset")

rs.Open "Customers", conn

%>

Create an ADO SQL Recordset

We can also get access to the data in the "Customers" table using SQL:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs=Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Customers", conn

%>

Extract Data from the Recordset

After a recordset is opened, we can extract data from recordset.

Suppose we have a database named "Northwind", we can get access to the "Customers" table inside the database with the following lines:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs=Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Customers", conn

for each x in rs.fields

   response.write(x.name)

   response.write(" = ")

   response.write(x.value)

next

%>

The ADO Recordset Object

The ADO Recordset object is used to hold a set of records from a database table.

Display the Field Names and Field Values

We have a database named "Northwind" and we want to display the data from the "Customers" table (remember to save the file with an .asp extension):

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT * FROM Customers", conn

do until rs.EOF

  for each x in rs.Fields

    Response.Write(x.name)

    Response.Write(" = ")

    Response.Write(x.value & "<br />")

  next

  Response.Write("<br />")

  rs.MoveNext

loop

rs.close

conn.close

%>

</body>

</html>

Here is the result:

CustomerID = ALFKI
CompanyName = Alfreds Futterkiste
ContactName = Maria Anders
ContactTitle = Sales Representative
Address = Obere Str. 57
City = Berlin
PostalCode = 12209
Country = Germany

CustomerID = BERGS
CompanyName = Berglunds snabbköp
ContactName = Christina Berglund
ContactTitle = Order Administrator
Address = Berguvsvägen 8
City = Luleå
PostalCode = S-958 22
Country = Sweden

CustomerID = CENTC
CompanyName = Centro comercial Moctezuma
ContactName = Francisco Chang
ContactTitle = Marketing Manager
Address = Sierras de Granada 9993
City = México D.F.
PostalCode = 05022
Country = Mexico

....
....
....

Display the Field Names and Field Values in an HTML Table

We can also display the data from the "Customers" table inside an HTML table with the following lines (remember to save the file with an .asp extension):

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT Companyname, Contactname FROM Customers", conn

%>

<table border="1" width="100%">

<%do until rs.EOF%>

   <tr>

   <%for each x in rs.Fields%>

      <td><%Response.Write(x.value)%></td>

   <%next

   rs.MoveNext%>

   </tr>

<%loop

rs.close

conn.close

%>

</table>

</body>

</html>

Here is the result:

Alfreds Futterkiste	Maria Anders
Berglunds snabbköp	Christina Berglund
Centro comercial Moctezuma	Francisco Chang
Ernst Handel	Roland Mendel
FISSA Fabrica Inter. Salchichas S.A.	Diego Roel
Galería del gastrónomo	Eduardo Saavedra
Island Trading	Helen Bennett
Königlich Essen	Philip Cramer
Laughing Bacchus Wine Cellars	Yoshi Tannamuri
Magazzini Alimentari Riuniti	Giovanni Rovelli
North/South	Simon Crowther
Paris spécialités	Marie Bertrand
Rattlesnake Canyon Grocery	Paula Wilson
Simons bistro	Jytte Petersen
The Big Cheese	Liz Nixon
Vaffeljernet	Palle Ibsen
Wolski Zajazd	Zbyszek Piestrzeniewicz

Add Headers to the HTML Table

We want to add headers to the HTML table to make it more readable (remember to save the file with an .asp extension):

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs = Server.CreateObject("ADODB.recordset")

sql="SELECT Companyname, Contactname FROM Customers"

rs.Open sql, conn

%>

<table border="1" width="100%">

  <tr>

  <%for each x in rs.Fields

    response.write("<th>" & x.name & "</th>")

  next%>

  </tr>

  <%do until rs.EOF%>

    <tr>

    <%for each x in rs.Fields%>

      <td><%Response.Write(x.value)%></td>

    <%next

    rs.MoveNext%>

    </tr>

  <%loop

  rs.close

  conn.close

%>

</table>

</body>

</html>

Here is the result:

Companyname	Contactname
Alfreds Futterkiste	Maria Anders
Berglunds snabbköp	Christina Berglund
Centro comercial Moctezuma	Francisco Chang
Ernst Handel	Roland Mendel
FISSA Fabrica Inter. Salchichas S.A.	Diego Roel
Galería del gastrónomo	Eduardo Saavedra
Island Trading	Helen Bennett
Königlich Essen	Philip Cramer
Laughing Bacchus Wine Cellars	Yoshi Tannamuri
Magazzini Alimentari Riuniti	Giovanni Rovelli
North/South	Simon Crowther
Paris spécialités	Marie Bertrand
Rattlesnake Canyon Grocery	Paula Wilson
Simons bistro	Jytte Petersen
The Big Cheese	Liz Nixon
Vaffeljernet	Palle Ibsen
Wolski Zajazd	Zbyszek Piestrzeniewicz

Display Selected Data

We want to display only the records from the "Customers" table that have a "Companyname" that starts with an A (remember to save the file with an .asp extension):

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs=Server.CreateObject("ADODB.recordset")

sql="SELECT Companyname, Contactname FROM Customers

WHERE CompanyName LIKE 'A%'"

rs.Open sql, conn

%>

<table border="1" width="100%">

  <tr>

  <%for each x in rs.Fields

    response.write("<th>" & x.name & "</th>")

  next%>

  </tr>

  <%do until rs.EOF%>

    <tr>

    <%for each x in rs.Fields%>

      <td><%Response.Write(x.value)%></td>

    <%next

    rs.MoveNext%>

    </tr>

  <%loop

  rs.close

  conn.close%>

</table>

</body>

</html>

Here is the result:

Companyname	Contactname
Alfreds Futterkiste	Maria Anders

Sort the Data

We want to display the "Companyname" and "Contactname" fields from the "Customers" table, ordered by "Companyname" (remember to save the file with an .asp extension):

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs = Server.CreateObject("ADODB.recordset")

sql="SELECT Companyname, Contactname FROM

Customers ORDER BY CompanyName"

rs.Open sql, conn

%>

<table border="1" width="100%">

  <tr>

  <%for each x in rs.Fields

    response.write("<th>" & x.name & "</th>")

  next%>

  </tr>

  <%do until rs.EOF%>

    <tr>

    <%for each x in rs.Fields%>

      <td><%Response.Write(x.value)%></td>

    <%next

    rs.MoveNext%>

    </tr>

  <%loop

  rs.close

  conn.close%>

</table>

</body>

</html>

Here is the result:

Companyname	Contactname
Alfreds Futterkiste	Maria Anders
Berglunds snabbköp	Christina Berglund
Centro comercial Moctezuma	Francisco Chang
Ernst Handel	Roland Mendel
FISSA Fabrica Inter. Salchichas S.A.	Diego Roel
Galería del gastrónomo	Eduardo Saavedra
Island Trading	Helen Bennett
Königlich Essen	Philip Cramer
Laughing Bacchus Wine Cellars	Yoshi Tannamuri
Magazzini Alimentari Riuniti	Giovanni Rovelli
North/South	Simon Crowther
Paris spécialités	Marie Bertrand
Rattlesnake Canyon Grocery	Paula Wilson
Simons bistro	Jytte Petersen
The Big Cheese	Liz Nixon
Vaffeljernet	Palle Ibsen
Wolski Zajazd	Zbyszek Piestrzeniewicz

Add a Record to a Table in a Database

We want to add a new record to the Customers table in the Northwind database. We first create a form that contains the fields we want to collect data from:

<html>

<body>

<form method="post" action="demo_add.asp">

<table>

<tr>

<td>CustomerID:</td>

<td><input name="custid"></td>

</tr><tr>

<td>Company Name:</td>

<td><input name="compname"></td>

</tr><tr>

<td>Contact Name:</td>

<td><input name="contname"></td>

</tr><tr>

<td>Address:</td>

<td><input name="address"></td>

</tr><tr>

<td>City:</td>

<td><input name="city"></td>

</tr><tr>

<td>Postal Code:</td>

<td><input name="postcode"></td>

</tr><tr>

<td>Country:</td>

<td><input name="country"></td>

</tr>

</table>

<br /><br />

<input type="submit" value="Add New">

<input type="reset" value="Cancel">

</form>

</body>

</html>

When the user presses the submit button the form is sent to a file called "demo_add.asp". The "demo_add.asp" file contains the code that will add a new record to the Customers table:

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

sql="INSERT INTO customers (customerID,companyname,"

sql=sql & "contactname,address,city,postalcode,country)"

sql=sql & " VALUES "

sql=sql & "('" & Request.Form("custid") & "',"

sql=sql & "'" & Request.Form("compname") & "',"

sql=sql & "'" & Request.Form("contname") & "',"

sql=sql & "'" & Request.Form("address") & "',"

sql=sql & "'" & Request.Form("city") & "',"

sql=sql & "'" & Request.Form("postcode") & "',"

sql=sql & "'" & Request.Form("country") & "')"

on error resume next

conn.Execute sql,recaffected

if err<>0 then

  Response.Write("No update permissions!")

else

  Response.Write("<h3>" & recaffected & " record added</h3>")

end if

conn.close

%>

</body>

</html>

Important

If you use the SQL INSERT command be aware of the following:

If the table contains a primary key, make sure to append a unique, non-Null value to the primary key field (if not, the provider may not append the record, or an error occurs)
If the table contains an AutoNumber field, do not include this field in the SQL INSERT command (the value of this field will be taken care of automatically by the provider)

What about Fields With no Data?

In a MS Access database, you can enter zero-length strings ("") in Text, Hyperlink, and Memo fields IF you set the AllowZeroLength property to Yes.

Note: Not all databases support zero-length strings and may cause an error when a record with blank fields is added. It is important to check what data types your database supports.

Update a Record in a Table

We want to update a record in the Customers table in the Northwind database. We first create a table that lists all records in the Customers table:

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs=Server.CreateObject("ADODB.Recordset")

rs.open "SELECT * FROM customers",conn

%>

<h2>List Database</h2>

<table border="1" width="100%">

<tr>

<%

for each x in rs.Fields

  response.write("<th>" & ucase(x.name) & "</th>")

next

%>

</tr>

<% do until rs.EOF %>

<tr>

<form method="post" action="demo_update.asp">

<%

for each x in rs.Fields

  if lcase(x.name)="customerid" then%>

    <td>

    <input type="submit" name="customerID" value="<%=x.value%>">

    </td>

  <%else%>

    <td><%Response.Write(x.value)%></td>

  <%end if

next

%>

</form>

<%rs.MoveNext%>

</tr>

<%

loop

conn.close

%>

</table>

</body>

</html>

If the user clicks on the button in the "customerID" column he or she will be taken to a new file called "demo_update.asp". The "demo_update.asp" file contains the source code on how to create input fields based on the fields from one record in the database table. It also contains a "Update record" button that will save your changes:

<html>

<body>

<h2>Update Record</h2>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

cid=Request.Form("customerID")

if Request.form("companyname")="" then

  set rs=Server.CreateObject("ADODB.Recordset")

  rs.open "SELECT * FROM customers WHERE customerID='" & cid & "'",conn

%>

  <form method="post" action="demo_update.asp">

  <table>

  <%for each x in rs.Fields%>

  <tr>

  <td><%=x.name%></td>

  <td><input name="<%=x.name%>" value="<%=x.value%>"></td>

  <%next%>

  </tr>

  </table>

  <br /><br />

  <input type="submit" value="Update record">

  </form>

<%

else

  sql="UPDATE customers SET "

  sql=sql & "companyname='" & Request.Form("companyname") & "',"

  sql=sql & "contactname='" & Request.Form("contactname") & "',"

  sql=sql & "address='" & Request.Form("address") & "',"

  sql=sql & "city='" & Request.Form("city") & "',"

  sql=sql & "postalcode='" & Request.Form("postalcode") & "',"

  sql=sql & "country='" & Request.Form("country") & "'"

  sql=sql & " WHERE customerID='" & cid & "'"

  on error resume next

  conn.Execute sql

  if err<>0 then

    response.write("No update permissions!")

  else

    response.write("Record " & cid & " was updated!")

  end if

end if

conn.close

%>

</body>

</html>

Delete a Record in a Table

We want to delete a record in the Customers table in the Northwind database. We first create a table that lists all records in the Customers table:

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

set rs=Server.CreateObject("ADODB.Recordset")

rs.open "SELECT * FROM customers",conn

%>

<h2>List Database</h2>

<table border="1" width="100%">

<tr>

<%

for each x in rs.Fields

  response.write("<th>" & ucase(x.name) & "</th>")

next

%>

</tr>

<% do until rs.EOF %>

<tr>

<form method="post" action="demo_delete.asp">

<%

for each x in rs.Fields

  if x.name="customerID" then%>

    <td>

    <input type="submit" name="customerID" value="<%=x.value%>">

    </td>

  <%else%>

    <td><%Response.Write(x.value)%></td>

  <%end if

next

%>

</form>

<%rs.MoveNext%>

</tr>

<%

loop

conn.close

%>

</table>

</body>

</html>

If the user clicks on the button in the "customerID" column he or she will be taken to a new file called "demo_delete.asp". The "demo_delete.asp" file contains the source code on how to create input fields based on the fields from one record in the database table. It also contains a "Delete record" button that will delete the current record:

<html>

<body>

<h2>Delete Record</h2>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "c:/webdata/northwind.mdb"

cid=Request.Form("customerID")

if Request.form("companyname")="" then

  set rs=Server.CreateObject("ADODB.Recordset")

  rs.open "SELECT * FROM customers WHERE customerID='" & cid & "'",conn

%>

  <form method="post" action="demo_delete.asp">

  <table>

  <%for each x in rs.Fields%>

  <tr>

  <td><%=x.name%></td>

  <td><input name="<%=x.name%>" value="<%=x.value%>"></td>

  <%next%>

  </tr>

  </table>

  <br /><br />

  <input type="submit" value="Delete record">

  </form>

<%

else

  sql="DELETE FROM customers"

  sql=sql & " WHERE customerID='" & cid & "'"

  on error resume next

  conn.Execute sql

  if err<>0 then

    response.write("No update permissions!")

  else

    response.write("Record " & cid & " was deleted!")

  end if

end if

conn.close

%>

</body>

</html>

Print Page | Close Window