Control | data | database | data source <%
' BEGIN USER CONSTANTS
' To just use a DSN, the "format is shown on the next line:
' Const dsn_name = ' Dsn=asp101email '
' Two other samples I used it with. Left in as syntax examples for dsn-less connections
' Const dsn_name = ' Dbq=c:\inetpub\wwwroot\asp101\samples\database.mdb;driver={microsoft Access Driver (*.mdb)};D Riverid=25 "
' Const dsn_name = ' Dbq=c:\inetpub\database\donations.mdb;driver={microsoft Access Driver (*.mdb)};D riverid=25 "
Dim Dsn_name
Dsn_name = "dbq=" & Server.MapPath ("Db_dsn.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;"
Const Dsn_user = "username"
Const dsn_pass = "Password"
' Ok, I know these are poorly named constants and so Sue me!
' This script can is used without actually setting up a DSN
' Dsn_name as as the other two constants should really
' Something more generic like connection_string, Connection_user, and
' Connection_pass, but I did it this way without really thinking about
' It and I ' m too lazy to change it now. If it bothers you to do it!
' End USER CONSTANTS
' BEGIN Subs & Functions section
Sub OpenConnection
Set OBJDC = Server.CreateObject ("ADODB. Connection ")
Objdc.connectiontimeout = 15
Objdc.commandtimeout = 30
Objdc.open Dsn_name, Dsn_user, Dsn_pass
End Sub
Sub OpenRecordset (stype)
Dim ssqlstring ' as string-building area for SQL query
Dim scritoperator ' as string-basically ' = ' or ' like '
Dim Scritdelimiter ' as String-parameter delimiter ', ' ' ', or ' # '
Set objRS = Server.CreateObject ("ADODB. Recordset ")
Select Case Stype
Case "Listtables" ' Open RS of the ' Tables in ' DB
Set objRS = Objdc.openschema (adSchemaTables)
Case "Viewtable" ' Open the Selected Table
Set objRS = Server.CreateObject ("ADODB. Recordset ")
Objrs.open "[" & sTableName & "]", OBJDC, adOpenForwardOnly, adLockReadOnly
Case "DrillDown" ' Open the Recordset built by the selected options
Set objRS = Server.CreateObject ("ADODB. Recordset ")
' Build our SQL Statement
ssqlstring = "SELECT * FROM [" & sTableName & "]"
' If we ' re limiting records returned-insert the WHERE Clause into the SQL
If Scritfield <> "" Then
' Figure out if we ' re dealinh with Numeric, Date, or String Values
Select Case Icritdatatype
Case adSmallInt, adinteger, Adsingle, addouble, Addecimal, Adtinyint, Adunsignedtinyint, Adunsignedsmallint, Adunsignedint, adBigInt, Adunsignedbigint, Adbinary, Adnumeric, Advarbinary, Adlongvarbinary, AdCurrency, AdBoolean
Scritoperator = "="
Scritdelimiter = ""
Case Addate, adDBDate, adDBTime, adDBTimeStamp
Scritoperator = "="
Scritdelimiter = "#"
Case Adbstr, Adchar, Adwchar, adVarChar, adLongVarChar, adVarWChar, adLongVarWChar
Scritoperator = "like"
Scritdelimiter = "'"
End Select
ssqlstring = ssqlstring & "WHERE [" & Scritfield & "]" & Scritoperator & "" & Scritdelimiter &A mp Scritvalue & Scritdelimiter
End If
' If we ' re sorting-insert the ORDER BY clause
If ssortorder <> "None" Then
ssqlstring = ssqlstring & "ORDER BY [" & Ssortfield & "]" & Ssortorder
End If
ssqlstring = ssqlstring & ";"
' Open ' actual Recordset using a Forward only Cursor in Read only Mode
Objrs.open ssqlstring, OBJDC, adOpenForwardOnly, adLockReadOnly
End Select
End Sub
Sub CloseRecordset
Objrs.close
Set objRS = Nothing
End Sub
Sub CloseConnection
Objdc.close
Set OBJDC = Noth
