To assist in interfacing with databases. This script can format variables and return SQL formats.
Such as double quoting apposterphies and surrounding strings with quotes, Returning NULL for invalid data
types, trimming strings so they do not exceed maximum lengths. This also has some functions so that you
can open and close databases more conveiently with just one line of code. You can query a database and get
an Array as well with some code.
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
'**************************************
' for :Common Database Routines
'**************************************
Copyright (c) 1999 by Lewis Moten, All rights reserved.
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
'**************************************
' Name: Common Database Routines
' Description:To assist in interfacing w
' ith databases. This script can format va
' riables and return SQL formats. Such as
' double quoting apposterphies and surroun
' ding strings with quotes, Returning NULL
' for invalid data types, trimming strings
' so they do not exceed maximum lengths. T
' his also has some functions so that you
' can open and close databases more convei
' ently with just one line of code. You ca
' n query a database and get an Array as w
' ell with some code.
' By: Lewis Moten
'
'
' Inputs:None
'
' Returns:None
'
'Assumes:This script assumes that you at
' least have Microsoft ActiveX Data Object
' s 2.0 or Higher (ADODB). This script may
' get some getting used to at first until
' you go through and study what each routi
' ne can do.
'
'Side Effects:None
'
'Warranty:
'code provided by Planet Source Code(tm)
' (www.Planet-Source-Code.com) 'as is', wi
' thout warranties as to performance, fitn
' ess, merchantability,and any other warra
' nty (whether expressed or implied).
'Terms of Agreement:
'By using this source code, you agree to
' the following terms...
' 1) You may use this source code in per
' sonal projects and may compile it into a
' n .exe/.dll/.ocx and distribute it in bi
' nary format freely and with no charge.
' 2) You MAY NOT redistribute this sourc
' e code (for example to a web site) witho
' ut written permission from the original
' author.Failure to do so is a violation o
' f copyright laws.
' 3) You may link to this code from anot
' her website, provided it is not wrapped
' in a frame.
' 4) The author of this code may have re
' tained certain additional copyright righ
' ts.If so, this is indicated in the autho
' r's description.
'**************************************
<!--METADATA Type="TypeLib" NAME="Microsoft ActiveX Data Objects 2.0 Library" UUID="{00000200-0000-
0010-8000-00AA006D2EA4}" VERSION="2.0"-->
<%
' Setup the ConnectionString
Dim sCONNECTION_STRING
sCONNECTION_STRING = "DRIVER=Microsoft Access Driver
(*.mdb);DBQ=D:\inetpub\wwwroot\inc\data\database.mdb;"
Dim oConn
'---------------------------------------
' ----------------------------------------
'
Function DBConnOpen(ByRef aoConnObj)
' This routine connects To a database and returns
' weather or Not it was successful
' Prepare For any errors that may occur While connecting To the database
On Error Resume Next
' Create a connection object
Set aoConnObj = Server.CreateObject("ADODB.Connection")
' Open a connection To the database
Call aoConnObj.Open(sCONNECTION_STRING)
' If any errors have occured
If Err Then
' Clear errors
Err.Clear
' Release connection object
Set aoConnObj = Nothing
' Return unsuccessful results
DBConnOpen = False
' Else errors did Not occur
Else
' Return successful results
DBConnOpen = True
End If ' Err
End Function ' DBConnOpen
'---------------------------------------
' ----------------------------------------
'
Public Function DBConnClose(ByRef aoConnObj)
' This routine closes the database connection and releases objects
' from memory
' If the connection variable has been defined as an object
If IsObject(aoConnObj) Then
' If the connection is open
If aoConnObj.State = adStateOpen Then
' Close the connection
aoConnObj.Close
' Return positive Results
DBConnClose = True
End If ' aoConnObj.State = adStateOpen
' Release connection object
Set aoConnObj = Nothing
End If ' IsObject(aoConnObj)
End Function ' DBConnClose
'---------------------------------------
' ----------------------------------------
'
Public Function SetData(ByRef asSQL, ByRef avDataAry)
' This routine acquires data from the database
Dim loRS ' ADODB.Recordset Object
' Create Recordset Object
Set loRS = Server.CreateObject("ADODB.Recordset")
' Prepare For errors when opening database connection
On Error Resume Next
' If a connection object has been defined
If IsObject(oConn) Then
' If the connection is open
If oConn.State = adStateOpen Then
' Acquire data With connection object
Call loRS.Open(asSQL, oConn, adOpenForwardOnly, adLockReadOnly)
' Else the connection is closed
Else
' Set the ConnectionString
Call SetConnectionString(csConnectionString)
' If atempt To open connection succeeded
If DBConnOpen() Then
' Acquire data With connection object
Call loRS.Open(asSQL, oConn, adOpenForwardOnly, adLockReadOnly)
' Return connection object To closed state
Call DBConnClose()
End If ' DBConnOpen()
End If ' aoConn.State = adStateOpen
' Else active connection is the ConnectionString
Else
' Acquire data With ConnectionString
Call loRS.Open(asSQL, sCONNECTION_STRING, adOpenForwardOnly, adLockReadOnly)
End If ' IsObject(oConn)
' If errors occured
If Err Then
response.write "<HR color=red>" & err.description & "<HR color=red>" & asSQL & "<HR
color=red>"
' Clear the Error
Err.Clear
' If the recorset is open
If loRS.State = adStateOpen Then
' Close the recorset
loRS.Close
End If ' loRS.State = adStateOpen
' Release Recordset from memory
Set loRS = Nothing
' Return negative results
SetData = False
' Exit Routine
Exit Function
End If ' Err
' Return positve results
SetData = True
' If data was found
If Not loRS.EOF Then
' Pull data into an array
avDataAry = loRS.GetRows
End If ' Not loRS.EOF
' Close Recordset
loRS.Close
' Release object from memory
Set loRS = Nothing
End Function ' SetData
'---------------------------------------
' ----------------------------------------
'
' SQL Preperations are used to prepare v
' ariables for SQL Queries. If
' invalid data is passed to these routin
' es, NULL values or Default Data
' is returned to keep your SQL Queries f
' rom breaking from users breaking
' datatype rules.
'---------------------------------------
' ----------------------------------------
'
Public Function SQLPrep_s(ByVal asExpression, ByRef anMaxLength)
' If maximum length is defined
If anMaxLength > 0 Then
' Trim expression To maximum length
asExpre
评论0