|
DSN, DSNless connections by Charles Carroll
OLEDB Good Connection String (recommended)
strconn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
strconn=strconn & server.mappath(accessDB)
& ";"
'strconn=strconn & "Password=whatever;" ODBC Connection String
(not recommended)
strconn="DRIVER={Microsoft
Access Driver (*.mdb)};"
strconn=sourceDSN & "DBQ=" & server.mappath(accessdb)
strconn=sourceDSN & "Password=whatever;" NOTE:
An ODBC connection string to Access often results in the dreaded:
Unable to open registry key
or 'Unspecified Error' usually occurs with an ODBC connection string to Access, i.e.
Any ASP script that needs to connect to a database must
open it on the server first. There are several ways:
SYSTEM DSN
which must be setup on the server, see:
/freebook/asp/dsn1.aspx for
detailed instructions or
http://www.aspalliance.com/components/database.asp
for components that automate this task.
This is NOT the fastest way* since all the information resides
on the server and need only be validated when the DSN is setup.
FILE DSN
which is not recommended for high concurrency situations since all
users would be bottlenecked by how fast the ASII file that holds the DSN
could be accessed. The trouble with a File DSN is that every
connection.open must open, read, close an ASCII file
and present the data anew to the provider/driver
since the ASCII file may have changed since last connection.File DSNs are
bottlenecks on busy sites.
DSNLESS
which requires no server setup, just a carefully constructed connection
string as demonstrated below.
DSNless connections demand that that you know the name of the
file (i.e. file based databases like Access, Paradox, FoxPro, etc.) or the
address of the data server (SQLserver for example). Armed with appropriate
information you could open a data source without a
DSN!
This is faster than a system DSN* since it saves a trip to
read the registry each attempt.
Most Connection strings are detailed @
http://www.able-consulting.com/ado_conn.htm?f=ado_conn.htm
& http://support.microsoft.com/support/kb/articles/q193/3/32.asp
http://support.microsoft.com/support/kb/articles/Q191/7/54.ASP
4GuyfromRolla (a superb ASP site of unparalleled
depth) has a great set of DSN articles:
* System DSN or DSNless connection @
http://www.4guysfromrolla.com/webtech/070399-1.shtml
Nifty way to make DSNless connections @
http://www.4guysfromrolla.com/webtech/070699-1.shtml
Listing System DSNs with a FREE component:
http://www.4guysfromrolla.com/webtech/011900-1.shtml
Here is a sample nwind.asp connecting to an access database named
"nwind.mdb" in the root of the web site with a DSNless connection. Note that you must know the actual filepath on the server, i.e. nwind.mdb is not good
enough it needs to be "C:\thatserver\account17\nwind.mdb". Fortunately the
server.mappath function can turn a filename into the proper fully qualified filename with
path on the server.
filename=/learn/test/showdb.asp
<HTML><HEAD>
<TITLE>ShowDatabase.asp</TITLE>
<body bgcolor="#FFFFFF"></HEAD>
<%
accessdb=server.mappath("nwind.mdb")
mySQL="select * from customers"
strconn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
strconn=strconn & accessDB & ";"
'strconn=strconn & "USER ID=;PASSWORD=;"
call query2table(mySQL,strconn)
%>
<!--#include file="lib_dbtable.asp"-->
</BODY>
</HTML>
Here is a sample sqldsn.asp connecting to an access database named
"nwind.mdb" in the root of the web site with a DSNless connection.
filename=/learn/test/sqldsn.asp
<HTML><HEAD>
<TITLE>sqlserverdsn.asp</TITLE>
<body bgcolor="#FFFFFF"></HEAD>
<%
strconn="DSN=student;uid=student;pwd=magic"
mySQL="select * from publishers where state='NY'"
call query2table(mySQL,strconn)
%>
<!--#include file="lib_dbtable.asp"-->
</BODY>
</HTML>
Here is a sample sqldsnless.asp connecting to an access database named
"nwind.mdb" in the root of the web site with a DSNless connection.
filename=/learn/test/sqldsnless.asp
<HTML><HEAD>
<TITLE>sqlDSNless.asp</TITLE>
<body bgcolor="#FFFFFF"></HEAD>
<%
strconn="PROVIDER=MSDASQL;DRIVER={SQL Server};"
strconn=strconn & "SERVER=sql7.orcsweb.net;DATABASE=;"
strconn=strconn & "UID=student;PWD=magic;"
mySQL="select * from publishers where state='NY'"
call query2table(mySQL,strconn)
%>
<!--#include file="lib_dbtable.asp"-->
</BODY>
</HTML>
The Include file lib_dbtable.asp looks like
this:
filename=/learn/test/lib_dbtable.asp
<%
sub query2table(inputquery, inputDSN)
dim conntemp, rstemp
set conntemp=server.createobject("adodb.connection")
conntemp.open inputDSN
set rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>
<table border=1><tr>
<% 'Put Headings On The Table of Field Names
for i=0 to howmanyfields %>
<td><b><%=rstemp(i).name%></B></TD>
<% next %>
</tr>
<% ' Now lets grab all the records
do while not rstemp.eof %>
<tr>
<% for i = 0 to howmanyfields
thisvalue=rstemp(i)
If isnull(thisvalue) then
thisvalue=" "
end if%>
<td valign=top><%=thisvalue%></td>
<% next %>
</tr>
<%rstemp.movenext
loop%>
</table>
<%
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
end sub%>
|