Setting up database webs in FrontPage

There are various approaches ( see bottom of page for worked examples).....

1.      ODBC.  

In this approach a connection is made to a database  using an ODBC  DSN (Data Source Name)
ODBC provides three levels of connection – File, User, System.

	A File DSN allows all users with the same ODBC drivers installed to access the database
	A User DSN allows a single user on the local computer to access the database
	A System DSN allows any user to access the database

2.      Using a Global.asa file  

“When an IIS application starts, it runs the Global.asa file, allowing initialization code to be executed. Specifically, the Application_OnStart routine is invoked, within which all initialization code for the application can be called. It is common practice to create and initialize objects and services when the application first starts, and then store references to those objects and services within the Application object. Once this is done, any Active Server Page (ASP) in the application can access those objects and services through the Application object. Such access is often faster than creating and initializing, and then destroying, the requisite objects within each ASP page” (Microsoft)

Adopt the following FrontPage routine to automatically create a global.asa file in your web:

	In a new page click on Tools > Web Settings > Database tab > Add
	Select ‘File or folder in current web’ and browse to its location (e.g. _private)
	Click OK, then click the Verify button in the dialog box (you should get a green tick !)
	When you OK out of this dialog box you will see a global.asa file added to your web.

You can now proceed to manipulate your database using VBScript, or alternatively, use the FrontPage database wizard. To access the wizard, click on Insert > Database > Results. In step 1 of the 5 steps select ‘Use an Existing Database Connection’ (the one you’ve just set up in global.asa). The remaining 4 steps are self-explanatory.

(note that  if you've published  the database application to a sub-web then you must copy the global.asa file to the web root, or make an appropriate entry in the existing one. This requirement does does not apply to virtual folders)

For a fuller description of these first two methods click here (opens in new window)

3.    Using VBScript (1)

<%   
        set MyConn = server.createobject("adodb.connection")
        DBpath = Server.MapPath("/myweb\_private")
        str_conn = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
        str_conn = str_conn & DBpath
        str_conn = str_conn & "\mydatabase.mdb;"
        MyConn.Open (str_conn)
%>
     Where ‘myweb’ is the web and ‘mydatabase.mdb’ is held in its _private’ sub-directory.
      (Now  make your sql calls etc etc)
<%
        mySQL="select * from customers"
        call query2table(mySQL,str_conn)
%>

4.  Using VBScript (2)

This is my preferred method - creating a database connection 'on the fly' using a session variable. For a complete explanation visit http://www.15seconds.com/howto/pg000070.htm The advantage of this approach is that you can make a database connection and still use the FrontPage Database Results Wizard to manipulate the database.

Here's an example (opens in new window)

And here's how to do it:

1.  Create a virtual directory anywhere on your hard disk
2.  Place your database file in the virtual directory
3.  In FrontPage in HTML view insert this code between the head tags (substituting your directory name & database name)

       <%
              Session("Conn_String") = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=/myvirtualdirectory/mydatabase.mdb"
              Set DBConn = Server.CreateObject("ADODB.Connection")
              DBConn.Open Session("Conn_String")
          %>

4.  Place the cursor between the body tags and change to normal view
5.  Use the Database Results Wizard to create the desired output 
6.  To learn how to include images in your output click here (opens in new window)

note: if there are calls to the database from a number of pages, then place the first line of the code above in the GLOBAL.ASA file and the two further lines in pages requiring database access. 

You will also need to use the session variable in manipulating recordsets - here is an example:

      <%
             set price = server.createobject("adodb.recordset")
             price.open pricesql, Session("Conn_String"), 1
        %>

Check out  Adventure Works  (new window) and Computer Sales (new window). In both cases the above coding has replaced the ODBC system data source (DSN) used in the origonal Microsoft examples.

Learning Resources Page