<%

' 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)};DriverId=25"

'Const DSN_NAME = "DBQ=C:\InetPub\database\donations.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=25"

Dim DSN_NAME

DSN_NAME = "DBQ=" & Server.MapPath("db_dsn.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;"

Const DSN_USER = "username"

Const DSN_PASS = "password"

' Ok, I know these are poorly named constants, so sue me!

' This script can be used without actually setting up a DSN, so

' DSN_NAME as well as the other two constants should really be named

' 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, you 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 the 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 & 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 the 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 = Nothing

End Sub

Sub WriteTitle(sTitle)

Response

ASP访问SQL Server内置对象 ASP数据库简单操作教程