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 usually only available on Windows server hosted accounts.
Want to use ASP 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
Setting up a Connection to the Database using ODBCYou 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 (ie on a remote server)
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 the code for an Access database connected via ADODB and displayed using an ASP processing page. It would need a Windows based server to display it

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

    Example Working Database

    Look at this Access database This is hosted on the Windows server running this page  Honest Dave   

 

    

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