ADO ODBC Connection Strings
The following are a collection of ODBC Connection Strings
encapsulated as complete ASP files. 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] [OLE
DB Strings] |
|
|
|
 |
| |
Access
97/2000/XP/2003 Database Connection String using
Microsoft Access Driver (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Driver={Microsoft
Access Driver (*.mdb)};" & _
"Dbq=" & Server.MapPath(".\db\db.mdb")
& ";Uid=admin;Pwd=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 Excel Driver (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Driver={Microsoft
Excel Driver (*.xls)};DriverId=790;" &
_
"Dbq=" & Server.MapPath(".\excel\myExcelFile.xls")
& ";"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM [sheet1$]"
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 Text Driver (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Driver={Microsoft
Text Driver (*.txt; *.csv)};" & _
"Dbq=" & Server.MapPath(".\csv\")
& ";"Extensions=asc,csv,tab,txt;Persist
Security Info=False"
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 Microsoft ODBC
for Oracle (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Driver={Microsoft
ODBC for Oracle};" & _
"Server=OracleServerName;uid=username;pwd=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 SQL Server
Driver (DSN less)
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL
strConnection = "Driver={SQL
Server};Server=SQLServerName;"
& _
"Database=DBaseName;Uid=sa;Pwd=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
%>
|
|
|
|
|
|
