ADO OLE DB Connection Strings (Classic ASP)
The following are a collection of OLE DB Connection
Strings encapsulated as complete Classic ASP scripts. These are
a quick and easy way of getting data out of a range
of database types.
The connection strings are highlighted in blue and the parts of the script you should change to suit your database location, name, table and field are in bold.
[Access] [Excel]
[CSV] [Oracle]
[SQL Server] [Index
Server] [ODBC Strings]
Access
97/2000/XP/2003 Database Connection String using
Microsoft Jet Driver (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & _
Server.MapPath(".\db\db.mdb")
& ";User Id=admin;Password=;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myTable"
rs.open strSQL, conn, 3,3
rs.MoveFirst
WHILE NOT rs.EOF
Response.Write(rs("myField")
& "<br/>")
rs.MoveNext
WEND
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
Excel
97/2000/XP/2003 Database Connection String using
Microsoft Jet Driver 4.0 (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & _
Server.MapPath(".\excel\myExcelFile.xls")
& ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myRange"
rs.open strSQL, conn, 3,3
rs.MoveFirst
WHILE NOT rs.EOF
Response.Write(rs("myColumn") & "<br/>")
rs.MoveNext
WEND
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
Comma Separated Values (.csv) Connection String using Microsoft
Jet Driver 4.0 (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath(".\csv\")
& ";Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myFile.csv"
rs.open strSQL, conn, 3,3
rs.MoveFirst
WHILE NOT rs.EOF
Response.Write(rs("myColumn") & "<br/>")
rs.MoveNext
WEND
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
Oracle Database Connection String using Oracle OLEDB
Driver (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Provider=OraOLEDB.Oracle;"Data Source=OracleDBase;" & _
"User Id=user;"Password=password;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myTable"
rs.open strSQL, conn, 3,3
rs.MoveFirst
WHILE NOT rs.EOF
Response.Write(rs("myField") & "<br/>")
rs.MoveNext
WEND
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
SQL Server Database Connection String using Microsoft
SQL Server OLEDB Driver (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Provider=sqloledb;Data
Source=SQLServerName;" &
_
"Initial Catalog=DBaseName;User
Id=sa;Password=password;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myTable"
rs.open strSQL, conn, 3,3
rs.MoveFirst
WHILE NOT rs.EOF
Response.Write(rs("myField") & "<br/>")
rs.MoveNext
WEND
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
Microsoft Index Server Connection String using MS
Index Server Driver (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Provider=msidxs;Data source=myCatalog;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT DocTitle
FROM Scope() WHERE CONTAINS('mySearchQuery')
ORDER BY rank"
rs.open strSQL, conn, 3,3
rs.MoveFirst
WHILE NOT rs.EOF
Response.Write(rs("DocTitle") & "<br/>")
rs.MoveNext
WEND
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>