Using ASP pages to link to a database      

What is needed to to use ASP to connect to a database?

  • ASP enabled hosting - cheaper hosting accounts will probably not support ASP or JSP
  • Support for MS Access or whatever database you are using.. Access is the most widely used and cheapest relational database but is only available on Windows server hosted accounts. (this website is hosted on an Windows server and can connect to Access databases)
 
Want to use ASP or JSP to connect to a database?

If you want to connect to a relational database such as Access, FoxPro, Oracle or dBase you will need to connect through ASP or JSP, OLE DB, ColdFusion or ASP.NET

ASP is simplest to set up and use

  • You will need a Windows based hosting account
  • OLE DB is available only on Windows NT, 2000, or XP
  • A ColdFusion web application connects to a database by choosing a ColdFusion data source defined in ColdFusion Administrator
  • ASP.NET is only available on Windows hosting and more expensive accounts
 

Making an ASP database connection

If you want to search the database you can build the form on an HTML page and connect to the ASP page which will connect to the database to display the results

If you are building a page to add, delete or amend records you can start with the ASP page

  • File New - Dynamic page- ASP VBScript
  • Make a connection to your database using the Databases tab on the Application Panel (see below)
  • Set up your 'testing server' connection in the Site menu Test that the connection works.
  • Select the Application icon you want to use and follow the dialogue. A dynamic table will need a Recordset.
 
Database Connections

You create a database connection by providing the information the application needs to establish contact with the database.

You need a 'driver' to establish contact with the software For ASP this is likely to be an open database connectivity (ODBC) driver or an object linking and embedding database (OLE DB) provider A JSP application must connect to a database through a JDBC driver or a a JDBC-ODBC Bridge driver.

You will also need to create a 'connection string' or a DSN (a one-word identifier) to tell the page how to find and connect to your database

If you are using a remotely hosted database construct a connection string as explained below.

 

Connectivity for databases jargon

Microsoft Data Access Components (MDAC)

Web based databases can be connected to the end user with a variety of components

  • ActiveX Data Objects (ADO)
  • Object Linked Embedding for databases (OLE DB) - includes OLE DB Provider for ODBC Drivers
  • Open Database Connectivity (ODBC)
  • Jet Replication Objects (JRO)
Setting up a Connection to the Database (using ODBBC or OLEDB)

You have 2 choices

  1. A DSN (Data Source Name) - you must be connecting through an ODBC driver on a Windows system. Go to Control Panel and ODBC drivers to set up a System DSN. Go to Application Panel/Databases and select DSN from the + button to set it up for the page
  2. A Connection String - if you are connecting through OLE or a ODBC driver not installed on a windows system remotely

Example - setting up a connection string in Dreamweaver editor

Go to Go to Application Panel/Databases and select Custom Connection String The following dialogue box appears

Any meaningful connection name will do but the choice of interface for the connection string is more complicated depending on whether you are using ADO or ODBC

Here is a a typical ODBC statement.

<%Dim Conn, RS
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")

"DRIVER=Microsoft Access Driver (*.mdb);DBQ="
Server.MapPath("/USERNAME/database/mydatabase.mdb")
Conn.Open

ASP applications are fluent ODBC speakers thanks to a built-in OLE DB/ODBC interpreter. in the Microsoft Data Access Components (MDAC) 2.7 package on the computer. For example, suppose you want your application to communicate with a Microsoft Access database by using an ODBC interface. In ASP, if you specify only the ODBC interface and no OLE DB interface, by default the application will use an OLE DB/ODBC interpreter to translate the OLE DB into ODBC, then it will use the ODBC/Access interpreter you specified to translate. - courtesy of Dreamweaver Help

You may need to use an ADO (ADODB)and OLE BD connection string. It depends on what your hosting company supports

Here is the provider statements for car333.asp

conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath

The connection and recordsets use ADO as below

set conn=server.createobject("ADODB.Connection")
set rsuser=server.createobject("ADODB.Recordset")

Sample Access Database from Webdevonline.co.uk

Honest Dave's Car Database - this is an Access database connected viaADODB and displayed using an ASP processing page.This database is hosted by a host supporting ASP processing and is hosted on Windows servers.

Look at detailed example code for an ASP connection as used in the database

Web Wiz GuideDatabase Example

Web Wiz Guide Internet Search Engine

This search engine will link to and search a database of useful web sites for clued up web users. It links to an Access database called search_engine.mdb

You can keyword search for topics. Try anthing Internet related.

           < ComplexPages Index