Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll
my Blog
[prev. Lesson]  Database to ListBox Online Resources
     [next Lesson]  DSN Setup #1 by Rob Martinson

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

<Test Script Below>


<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

<Test Script Below>


<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

<Test Script Below>


<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

<Test Script Below>


<% 
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="&nbsp;"
            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%>

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.