Dynamic-CD
Search : 
  Powered by FindinSite

ASP Database support
• Introduction
• Checking objects
• Copying databases
• Creating databases
• Database connections
• Database browser 		    
Putting a database-driven website on CD

Creating an updatable database from scratch.
The ADOX component can be used to create a database from scratch unless the target machine is running a version of ADODB earlier than 2.5 (typically Windows 98SE). In that case, you will have to run an SQL command using an ODBC connection string. To create a database from scratch,

  1. check the database doesn't exist already
  2. if it exists, either delete or exit
  3. create an ADOX Catalog
  4. open the Catalog
  5. create an ADOX Table
  6. specify the properties of the table
  7. append the table
  8. create and append other database elements 


<!-- include support functions -->
<!-- #INCLUDE FILE="adodbfns.asp" -->
<!-- #INCLUDE FILE="adovbs.inc" -->
<%

CONST connString = _
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbPath

FUNCTION createDB( destFile, oWriteIfDestExists)
  on error goto 0
  
  createDB = FALSE

  ' if this fails, the function exits immediately
  Set fso = Server.CreateObject("Scripting.FileSystemObject")

  ' does the destination file exist already?
  debugMsg( "Checking existence of " + destFile)
  IF fso.FileExists( destFile) THEN
    debugMsg( "File " + destFile + " exists already")

    ' if exists - decide what to do
    IF oWriteIfDestExists THEN
      fso.DeleteFile( destFile) 
      debugMsg( "Deleted file " + destFile)
      ELSE
        EXIT FUNCTION
    END IF
  END IF
  SET fso = nothing

  ' if this fails, the function exits immediately
  Set catNew = Server.CreateObject("ADOX.Catalog")
  catNew.Create connString + destFile
  debugMsg( "Created new database")

  catNew.ActiveConnection = connString + destFile
  debugMsg( "Opened the database")

  ' add a table
  set tbl = Server.CreateObject( "ADOX.Table")
  debugMsg( "Created new table")
  
  tbl.Name = "MyTable"
  tbl.Columns.Append "CustomerID", adVarWChar, 50
  tbl.Columns.Append "ItemID", adInteger
  tbl.Columns.Append "Quantity", adInteger
  catNew.Tables.Append tbl

  ' is appending tables supported?
  Call ErrCheck( "Couldn't append table", TRUE)
  debugMsg( "Appended the table")

  ' clean up
  Set tbl = nothing
  Set catNew = nothing

  createDB = TRUE

END FUNCTION

' -------------------------
  ON ERROR RESUME NEXT
  dontDebug = FALSE
  
  destFile = Request.ServerVariables( "WINDOWS_TEMP_DIR") &_
    "\test.mdb"
  rval = createDB( destFile, TRUE)
  Response.Write "<BR>CreateDB " &_
    IIF( rval, "succeeded", "failed")

%>
If this process fails, you can also create a database using the ODBC provider connection string : 

<%
  ' older versions of ADOX fail to append tables 
  ' - try a different approach
  debugMsg( "append failed - trying SQL")

  SET con = Server.CreateObject( "ADODB.Connection")
  debugMsg( "adodb version = " + cstr( con.version))
  connString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="
  con.Open connString + dbPath
  errChk
		
  mySQL = "CREATE TABLE MyTable (" &_
  "CustomerID TEXT (50)," &_
  "ItemID INTEGER," &_
  "Quantity INTEGER);"

  con.Execute( mySQL)
  errChk
  debugMsg( "appended the table")

  con.close
  SET con=nothing
%>
© Copyright 2000-2007 PHD Computer Consultants Ltd