sl*@cfl.rr.com wrote:
Help,
I'm trying to make the following asp code work using a DNS-Less
connection on a windows 2000 server running IIS and ODBC 4.0 driver.
The Access database (odbc_exmp.mdb) was made using Access 2002. The
code works if I set up a DNS on the server under the ODBC drivers.
What I'm I doing wrong and what do I change the code to, to make it
work using DNS-Less connection?
Thanks for any help
Kevin.
<% Response.buffer = true %>
<html>
<head>
<title>Testing of odbc_exmp asp</title>
</head>
<body>
<%
'Next 4 lines do not work
What does "doesn't work" mean? Error messages? Incorrect behavior? Please
try to describe your symptoms without using the words "doesn't work", or "no
luck", or some other phrase that assumes your readers are able to read your
mind. :-)
Dim rs, dbPath
dbPath = "c:\safety\tr\odbc_exmp.mdb"
Set rs = Server.CreateObject("ADODB.Connection")
rs.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
"rs" for a connection object??? Do you want it make it impossible for
someone to debug your code?? Join the rest of the world and use "cn" or
"conn" for the name of your connection object variable.
' The next 3 lines work with DNS set up on the windows 2000
server-IIS-ODBC 4.0 drivers
'Dim rs
'Set rs = Server.CreateObject ("ADODB.Recordset")
'rs.Open "names", "DSN=odbc_exmp",,, &H0200
You've just redefined the rs variable. It's now a recordset object. Was that
intentional? Anyways, it's a bad idea to use a connection string in your
recordset's Open statement. You may be disabling connection pooling by doing
so. Always use an explicit connection object.
It's also not a good idea to open your entire names table when your intent
is to display two of the fields in it. Do this instead:
Dim rs, dbPath, cn, sSQL
dbPath = "c:\safety\tr\odbc_exmp.mdb"
sSQL= "Select [id],first_name,last_name From [names]"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
Set rs = cn.Execute(sSQL,,1)
For a potentially major performance improvement, use a GetRows array:
dim arData
If Not rs.EOF then arData = rs.GetRows
rs.close: set rs=nothing
cn.close: set cn=nothing
if isArray(arData) then
dim i
for i = 0 to ubound(arData,2)
Response.Write "ID : " & arData(0,i) & "<br>"
Response.Write "First Name : " & arData(1,i) & "<br>"
Response.Write "Last Name : " & arData(2,i) & "<br>"
Response.Write "<br>"
Response.Write "<br>"
next
else
response.write "No Records were returned"
end if
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"