Dynamic-CD
Search : 
  Powered by FindinSite

ASP Documentation
Overview
• Introduction
• Getting started
• Dynamic-CD wizard
Passwords & Encryption
• Overview
• Example 1
• Example 2
Scripts
• The script language
• Script examples
• Database scripts
Technical details
• Applications
• Built-in objects
• Character Encoding
• Cookies
• Database CDs
• Development tips
• FAQs
• Future developments
• Global.asa
• Network support
• Object registration
• Resources
• ScriptingContext
• Server-side includes
• Sessions
• Technical limits 		    
Database Scripts
Databases can be queried and searched using the ASP script language and the results formatted as HTML pages.

ASP provides access to the COM objects loaded on a machine through calls to  Server.CreateObject . Database support is provided via the ADODB COM object.

Windows XP, Windows 2000, Windows Me and Windows 98 SE (second edition) provide built-in ADODB support for Microsoft ACCESS® databases.

Windows 95 and Windows 98 (first edition) and Windows NT 4.0 can be upgraded to provide this support by a free download from the Microsoft site.

Note that if you have a multi-boot machine, loading different versions of the ADODB support files (MDAC) onto different boots can cause problems.

Example of a database script
The following example is from a membership site. You can display all membership records and then attempt to enter the site. If your entry is sucessful, the membership details stored in the database are displayed.

Try out the database scripts ...
Enter your membership ID :
Enter your post code :

View database script source.

Walk through of the database script
  1. A Microsoft ACCESS® database is located on the Dynamic-CD relative to the ROOT_DIR.
  2. The database path needs to be an absolute DOS path on the drive that is running Dynamic-CD.
  3. A check is made to verify that the database file exists.
  4. A connection is made to the ADODB COM object with a call to
     Server.CreateObject( "ADODB.Connection") 
  5. If the database cannot be opened, VBScript generates a run-time error.
  6. If the database has been locked with a Microsoft ACCESS® password, this password should be included in the open statement -
     Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=myPwd;"  
<%
PUBLIC Con
PUBLIC memNum
PUBLIC memEmail
PUBLIC memName
PUBLIC dbPath

' -----------------------------------
FUNCTION Connect2DB()

    Connect2DB = FALSE

    ' locate the database
    rootPath = Request.ServerVariables( "ROOT_DIR")
    dbPath = rootPath + "\dcddoc\dcd_dbex.mdb"

    ' create connection
    SET Con = Server.CreateObject( "ADODB.Connection")
    IF NOT isObject( Con) THEN
        EXIT FUNCTION
    END IF

    ' open the database
    Con.Open _
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath

    Connect2DB = TRUE

END FUNCTION
%>

  1. Once the database is opened successfully, you can perform standard database operations on it.
  2. The script first picks up data sent to the script by the user.
  3. It is important to clean up such data before using it to query the database - because the user's data will make up part of an SQL query string, it is important to ensure that all quotes are removed. 
<%
' -----------------------------------
FUNCTION delquotes( entry)
  entry = delchar( entry, "'")
  delquotes = delchar( entry, CHR(34))
END FUNCTION
    
' -----------------------------------
FUNCTION delchar( entry, cc)
  DO 
    qat = instr( entry, cc)
    IF qat > 0 THEN
      entry = left( entry, qat - 1) + mid( entry, qat + 1)
    ELSE
      exit do
    END IF
  LOOP
  delchar = entry
END FUNCTION

' -----------------------------------
SUB runMyScript

  ' -----------------------------------
  ' PICK UP VARIABLES
  memNum = TRIM( Request.Form( "memNum"))
  memNum = delquotes( memNum)
  memNum = ucase( memNum)
  memEmail  = TRIM( Request.Form( "memEmail"))
  memEmail = delquotes( memEmail)

  ' -----------------------------------
  ' etc

  1. The following script searches for a record that matches the user's input data. If a record if found, their name is extracted.
  2. Note that the database is located on a CD, so that it is impossible to update the database records. To write to the database, it would first have to be copied to the user's hard disk. 

' -----------------------------------
FUNCTION memNumExists( memNum, memEmail)

  memNumExists = -1

  IF NOT Connect2DB() THEN
    EXIT FUNCTION
  END IF

  memNumExists = 0

  IF memNum = "" THEN
    EXIT FUNCTION
  END IF

  ' see if user exists
  MySQL = "SELECT * FROM memData WHERE memNum='" &_
    memNum + "' AND memEmail='" + memEmail + "'"
  SET RS = Con.Execute( MySQL)

  ' is there a record?
  IF RS.EOF THEN
    Con.Close
    SET Con = NOTHING
    EXIT FUNCTION
  END IF

  ' extract member's name
  memName = TRIM( RS( "memName"))
  IF vartype( memName) <> vbString THEN
    memName = "unknown"
  END IF

  Con.Close
  SET Con = NOTHING

  ' return OK
  memNumExists = 1
  
END FUNCTION

  1. Finally, the browser is redirected to a second script with the the user's name and membership number passed in the URL.
  2. The call  Server.URLEncode  is used to ensure that the passed parameters are suitably encoded to be wrapped up in a URL. 
  memName2send = Server.URLEncode( memName)
  memNum2send = Server.URLEncode( memNum)
  Response.Redirect( "exdb.asp?memName=" + memName2send &_
    "&memNum=" + memNum)
  EXIT SUB

For further information about ADODB objects, see our resource list.

© Copyright 2000-2007 PHD Computer Consultants Ltd