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 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
%>
