How to connect to a database using ASP and a DSN-less connection

1. Upload your mdb database to the ‘_private’ folder in your ‘httpdocs’
2. Create a new file called connection.asp
3. Copy the following code into the file swapping ‘yourdomain.com’ with your own domain name and ‘yourdatabasefile.mdb’ with the name of your database.

<%'====DATABASE CONNECTION=========================================
database="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=""c:\Inetpub\vhosts\yourdomain.com\httpdocs\_private\yourdatabase.mdb"";
username="admin"
password=""
'================================================================
 %>

Create a new file called adovbs.inc and copy the following code into it.

<%
'--------------------------------------------------------------------
' Microsoft ADO
'
' (c) 1996-1998 Microsoft Corporation.  All Rights Reserved.
'
'
'
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorOptionEnum Values ----
Const adHoldRecords = &amp;H00000100
Const adMovePrevious = &amp;H00000200
Const adAddNew = &amp;H01000400
Const adDelete = &amp;H01000800
Const adUpdate = &amp;H01008000
Const adBookmark = &amp;H00002000
Const adApproxPosition = &amp;H00004000
Const adUpdateBatch = &amp;H00010000
Const adResync = &amp;H00020000
Const adNotify = &amp;H00040000
Const adFind = &amp;H00080000
Const adSeek = &amp;H00400000
Const adIndex = &amp;H00800000

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- ExecuteOptionEnum Values ----
Const adAsyncExecute = &amp;H00000010
Const adAsyncFetch = &amp;H00000020
Const adAsyncFetchNonBlocking = &amp;H00000040
Const adExecuteNoRecords = &amp;H00000080

'---- ConnectOptionEnum Values ----
Const adAsyncConnect = &amp;H00000010

'---- ObjectStateEnum Values ----
Const adStateClosed = &amp;H00000000
Const adStateOpen = &amp;H00000001
Const adStateConnecting = &amp;H00000002
Const adStateExecuting = &amp;H00000004
Const adStateFetching = &amp;H00000008

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3

'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
Const adChapter = 136
Const adFileTime = 64
Const adDBFileTime = 137
Const adPropVariant = 138
Const adVarNumeric = 139

'---- FieldAttributeEnum Values ----
Const adFldMayDefer = &amp;H00000002
Const adFldUpdatable = &amp;H00000004
Const adFldUnknownUpdatable = &amp;H00000008
Const adFldFixed = &amp;H00000010
Const adFldIsNullable = &amp;H00000020
Const adFldMayBeNull = &amp;H00000040
Const adFldLong = &amp;H00000080
Const adFldRowID = &amp;H00000100
Const adFldRowVersion = &amp;H00000200
Const adFldCacheDeferred = &amp;H00001000
Const adFldNegativeScale = &amp;H00004000
Const adFldKeyColumn = &amp;H00008000

'---- EditModeEnum Values ----
Const adEditNone = &amp;H0000
Const adEditInProgress = &amp;H0001
Const adEditAdd = &amp;H0002
Const adEditDelete = &amp;H0004

'---- RecordStatusEnum Values ----
Const adRecOK = &amp;H0000000
Const adRecNew = &amp;H0000001
Const adRecModified = &amp;H0000002
Const adRecDeleted = &amp;H0000004
Const adRecUnmodified = &amp;H0000008
Const adRecInvalid = &amp;H0000010
Const adRecMultipleChanges = &amp;H0000040
Const adRecPendingChanges = &amp;H0000080
Const adRecCanceled = &amp;H0000100
Const adRecCantRelease = &amp;H0000400
Const adRecConcurrencyViolation = &amp;H0000800
Const adRecIntegrityViolation = &amp;H0001000
Const adRecMaxChangesExceeded = &amp;H0002000
Const adRecObjectOpen = &amp;H0004000
Const adRecOutOfMemory = &amp;H0008000
Const adRecPermissionDenied = &amp;H0010000
Const adRecSchemaViolation = &amp;H0020000
Const adRecDBDeleted = &amp;H0040000

'---- GetRowsOptionEnum Values ----
Const adGetRowsRest = -1

'---- PositionEnum Values ----
Const adPosUnknown = -1
Const adPosBOF = -2
Const adPosEOF = -3

'---- enum Values ----
Const adBookmarkCurrent = 0
Const adBookmarkFirst = 1
Const adBookmarkLast = 2

'---- MarshalOptionsEnum Values ----
Const adMarshalAll = 0
Const adMarshalModifiedOnly = 1

'---- AffectEnum Values ----
Const adAffectCurrent = 1
Const adAffectGroup = 2
Const adAffectAllChapters = 4

'---- ResyncEnum Values ----
Const adResyncUnderlyingValues = 1
Const adResyncAllValues = 2

'---- CompareEnum Values ----
Const adCompareLessThan = 0
Const adCompareEqual = 1
Const adCompareGreaterThan = 2
Const adCompareNotEqual = 3
Const adCompareNotComparable = 4

'---- FilterGroupEnum Values ----
Const adFilterNone = 0
Const adFilterPendingRecords = 1
Const adFilterAffectedRecords = 2
Const adFilterFetchedRecords = 3
Const adFilterConflictingRecords = 5

'---- SearchDirectionEnum Values ----
Const adSearchForward = 1
Const adSearchBackward = -1

'---- PersistFormatEnum Values ----
Const adPersistADTG = 0
Const adPersistXML = 1

'---- StringFormatEnum Values ----
Const adClipString = 2

'---- ConnectPromptEnum Values ----
Const adPromptAlways = 1
Const adPromptComplete = 2
Const adPromptCompleteRequired = 3
Const adPromptNever = 4

'---- ConnectModeEnum Values ----
Const adModeUnknown = 0
Const adModeRead = 1
Const adModeWrite = 2
Const adModeReadWrite = 3
Const adModeShareDenyRead = 4
Const adModeShareDenyWrite = 8
Const adModeShareExclusive = &amp;Hc
Const adModeShareDenyNone = &amp;H10
Const adModeRecursive = &amp;H20

'---- RecordCreateOptionsEnum Values ----
Const adCreateCollection = &amp;H00002000
Const adCreateStructDoc = &amp;H80000000
Const adCreateNonCollection = &amp;H00000000
Const adOpenIfExists = &amp;H02000000
Const adCreateOverwrite = &amp;H04000000
Const adFailIfNotExists = -1

'---- RecordOpenOptionsEnum Values ----
Const adOpenSource = &amp;H00000080
Const adOpenAsync = &amp;H00001000
Const adDelayFetchStream = &amp;H00004000
Const adDelayFetchFields = &amp;H00008000
Const adOpenURLBind = &amp;H0400

'---- IsolationLevelEnum Values ----
Const adXactUnspecified = &amp;Hffffffff
Const adXactChaos = &amp;H00000010
Const adXactReadUncommitted = &amp;H00000100
Const adXact

Browse = &amp;H00000100
Const adXactCursorStability = &amp;H00001000
Const adXactReadCommitted = &amp;H00001000
Const adXactRepeatableRead = &amp;H00010000
Const adXactSerializable = &amp;H00100000
Const adXactIsolated = &amp;H00100000

'---- XactAttributeEnum Values ----
Const adXactCommitRetaining = &amp;H00020000
Const adXactAbortRetaining = &amp;H00040000

'---- PropertyAttributesEnum Values ----
Const adPropNotSupported = &amp;H0000
Const adPropRequired = &amp;H0001
Const adPropOptional = &amp;H0002
Const adPropRead = &amp;H0200
Const adPropWrite = &amp;H0400

'---- ErrorValueEnum Values ----
Const adErrInvalidArgument = &amp;Hbb9
Const adErrOpeningFile = &amp;Hbba
Const adErrReadFile = &amp;Hbbb
Const adErrWriteFile = &amp;Hbbc
Const adErrNoCurrentRecord = &amp;Hbcd
Const adErrIllegalOperation = &amp;Hc93
Const adErrInTransaction = &amp;Hcae
Const adErrFeatureNotAvailable = &amp;Hcb3
Const adErrItemNotFound = &amp;Hcc1
Const adErrObjectInCollection = &amp;Hd27
Const adErrObjectNotSet = &amp;Hd5c
Const adErrDataConversion = &amp;Hd5d
Const adErrObjectClosed = &amp;He78
Const adErrObjectOpen = &amp;He79
Const adErrProviderNotFound = &amp;He7a
Const adErrBoundToCommand = &amp;He7b
Const adErrInvalidParamInfo = &amp;He7c
Const adErrInvalidConnection = &amp;He7d
Const adErrNotReentrant = &amp;He7e
Const adErrStillExecuting = &amp;He7f
Const adErrOperationCancelled = &amp;He80
Const adErrStillConnecting = &amp;He81
Const adErrUnsafeOperation = &amp;He84

'---- ParameterAttributesEnum Values ----
Const adParamSigned = &amp;H0010
Const adParamNullable = &amp;H0040
Const adParamLong = &amp;H0080

'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &amp;H0000
Const adParamInput = &amp;H0001
Const adParamOutput = &amp;H0002
Const adParamInputOutput = &amp;H0003
Const adParamReturnValue = &amp;H0004

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &amp;H0008
Const adCmdText = &amp;H0001
Const adCmdTable = &amp;H0002
Const adCmdStoredProc = &amp;H0004
Const adCmdFile = &amp;H0100
Const adCmdTableDirect = &amp;H0200
Const adCmdURLBind = &amp;H0400

'---- EventStatusEnum Values ----
Const adStatusOK = &amp;H0000001
Const adStatusErrorsOccurred = &amp;H0000002
Const adStatusCantDeny = &amp;H0000003
Const adStatusCancel = &amp;H0000004
Const adStatusUnwantedEvent = &amp;H0000005

'---- EventReasonEnum Values ----
Const adRsnAddNew = 1
Const adRsnDelete = 2
Const adRsnUpdate = 3
Const adRsnUndoUpdate = 4
Const adRsnUndoAddNew = 5
Const adRsnUndoDelete = 6
Const adRsnRequery = 7
Const adRsnResynch = 8
Const adRsnClose = 9
Const adRsnMove = 10
Const adRsnFirstChange = 11
Const adRsnMoveFirst = 12
Const adRsnMoveNext = 13
Const adRsnMovePrevious = 14
Const adRsnMoveLast = 15

'---- SchemaEnum Values ----
Const adSchemaProviderSpecific = -1
Const adSchemaAsserts = 0
Const adSchemaCatalogs = 1
Const adSchemaCharacterSets = 2
Const adSchemaCollations = 3
Const adSchemaColumns = 4
Const adSchemaCheckConstraints = 5
Const adSchemaConstraintColumnUsage = 6
Const adSchemaConstraintTableUsage = 7
Const adSchemaKeyColumnUsage = 8
Const adSchemaReferentialConstraints = 9
Const adSchemaTableConstraints = 10
Const adSchemaColumnsDomainUsage = 11
Const adSchemaIndexes = 12
Const adSchemaColumnPrivileges = 13
Const adSchemaTablePrivileges = 14
Const adSchemaUsagePrivileges = 15
Const adSchemaProcedures = 16
Const adSchemaSchemata = 17
Const adSchemaSQLLanguages = 18
Const adSchemaStatistics = 19
Const adSchemaTables = 20
Const adSchemaTranslations = 21
Const adSchemaProviderTypes = 22
Const adSchemaViews = 23
Const adSchemaViewColumnUsage = 24
Const adSchemaViewTableUsage = 25
Const adSchemaProcedureParameters = 26
Const adSchemaForeignKeys = 27
Const adSchemaPrimaryKeys = 28
Const adSchemaProcedureColumns = 29
Const adSchemaDBInfoKeywords = 30
Const adSchemaDBInfoLiterals = 31
Const adSchemaCubes = 32
Const adSchemaDimensions = 33
Const adSchemaHierarchies = 34
Const adSchemaLevels = 35
Const adSchemaMeasures = 36
Const adSchemaProperties = 37
Const adSchemaMembers = 38
Const adSchemaTrustees = 39

'---- SeekEnum Values ----
Const adSeekFirstEQ = &amp;H1
Const adSeekLastEQ = &amp;H2
Const adSeekAfterEQ = &amp;H4
Const adSeekAfter = &amp;H8
Const adSeekBeforeEQ = &amp;H10
Const adSeekBefore = &amp;H20

'---- ADCPROP_UPDATECRITERIA_ENUM Values ----
Const adCriteriaKey = 0
Const adCriteriaAllCols = 1
Const adCriteriaUpdCols = 2
Const adCriteriaTimeStamp = 3

'---- ADCPROP_ASYNCTHREADPRIORITY_ENUM Values ----
Const adPriorityLowest = 1
Const adPriorityBelowNormal = 2
Const adPriorityNormal = 3
Const adPriorityAboveNormal = 4
Const adPriorityHighest = 5

'---- CEResyncEnum Values ----
Const adResyncNone = 0
Const adResyncAutoIncrement = 1
Const adResyncConflicts = 2
Const adResyncUpdates = 4
Const adResyncInserts = 8
Const adResyncAll = 15

'---- ADCPROP_AUTORECALC_ENUM Values ----
Const adRecalcUpFront = 0
Const adRecalcAlways = 1

'---- MoveRecordOptionsEnum Values ----
Const adMoveOverWrite = 1
Const adMoveDontUpdateLinks = 2
Const adMoveAllowEmulation = 4

'---- CopyRecordOptionsEnum Values ----
Const adCopyOverWrite = 1
Const adCopyAllowEmulation = 4
Const adCopyNonRecursive = 2

'---- StreamTypeEnum Values ----
Const adTypeBinary = 1
Const adTypeText = 2

'---- LineSeparatorEnum Values ----
Const adLF = 10
Const adCR = 13
Const adCRLF = -1

'---- StreamOpenOptionsEnum Values ----
Const adOpenStreamAsync = 1
Const adOpenStreamFromRecord = 4
Const adOpenStreamFromURL = 8

'---- StreamWriteEnum Values ----
Const adWriteChar = 0
Const adWriteLine = 1

'---- SaveOptionsEnum Values ----
Const adSaveCreateNotExist = 1
Const adSaveCreateOverWrite = 2

'---- enum Values ----
Const adDefaultStream = -1
Const adRecordURL = -2

'---- enum Values ----
Const adReadAll = -1
Const adReadLine = -2

'---- RecordTypeEnum Values ----
Const adSimpleRecord = 0
Const adCollectionRecord = 1
Const adStructDoc = 2
%>

Upload both files to your 'httpdocs' folder.

Now, to refer to the database in your .asp page. Just include the following code on the first line to each asp page;

<!--#include virtual="/adovbs.inc" -->
<!--#include virtual="/connection.asp" -->

Now each time you wish to connect to your database to run a query use the following code;

' Connection Object
set connection=Server.CreateObject("ADODB.Connection")
connection.CommandTimeout=30
connection.ConnectionTimeout=20
connection.Open database, username, password

' Recordset Object
set objrs=Server.CreateObject("ADODB.Recordset")
objrs.CursorLocation=adUseClient

'Example query
strsql="Select * FROM table_name WHERE id=1;"
objrs.open strsql,connection,adopenkeyset,adlockreadonly
numrec=objrs.recordcount
if numrec>0 then
    id=objrs("id")
end if
objrs.close

' Close Objects
connection.close
set objrs=nothing
set connection=nothing

Further Reading:

http://www.connectionstrings.com/

Sponsors

Related posts:

• How do I protect my Access database (MDB file)?
• How to connect to a MySQL database using PHP
• How do I backup my MySQL database?
• How-to Backup a MySQL database
• Web.config

Tags: access, asp, connection, database, dsn-less

This entry was posted on Sunday, May 10th, 2009 at 5:31 pm and is filed under Connecting to a database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.