DNS-less Connections

slc · Feb 19, 2005

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

' 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

While Not rs.EOF
'Response.Write "ID : " & rs("id") & " "
Response.Write "First Name : " & rs("first_name") & " "
Response.Write "Last Name : " & rs("last_name") & " "
Response.Write " "
Response.Write " "
rs.MoveNext
Wend

rs.Close
Set rs = Nothing %>
</body>
</html>

Veign · Feb 19, 2005

Dim rs, dbPath

dbPath = "c:\safety\tr\odbc_exmp.mdb"
Set rs = Server.CreateObject("ADODB.Connection")

You have the variable named RS but have instantiated a Connection object.
Try changing it to an ADODB.Recordset

rs.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath

You need some sort of SQL statement of something to let it know what to pull
from the database.

'---------------------------------------Code Sample:
'Create the Connection string
Dim strConnStr, dbPath
dbPath = "c:\safety\tr\odbc_exmp.mdb"
strConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath

'Create the SQL Statement
Dim strSQL
strSQL = "SELECT id, first_name, last_name FROM MyTable

'Open the Recordset
Dim RS
Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open strSQL, strConnStr

'Loop through the records
With RS
If Not .EOF Then
Do Until .EOF
Response.Write "ID : " & RS("id").Value & " "
.MoveNext
Loop
End If
End With

'Cleanup
RS.Close
Set RS = Nothing
'-------------------------End Code sample

**- Make sure you change MyTable to the actual table name in your DB
** - On the line 'RS.Open strSQL, strConnStr' you should include the
constants for a Forward Only, Read only recordset (firehose recordset) as
this is the most efficient kind in this type of use

Bob Barrows [MVP] · Feb 19, 2005

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) & " "
Response.Write "First Name : " & arData(1,i) & " "
Response.Write "Last Name : " & arData(2,i) & " "
Response.Write " "
Response.Write " "
next
else
response.write "No Records were returned"
end if

HTH,
Bob Barrows

brownk10 · Feb 20, 2005

Chris,

Thank You for the sample code.
I fix the line that says strSQL = "SELECT id, first_name, last_name
FROM MyTable
to read
strSQL = "SELECT id, first_name, last_name FROM [MyTable]"
and it works grate.

(Boy, was my code mested up)

Again Thanks for the Help.

Kevin

slc · Feb 20, 2005

Bob,

Thanks for the Help, your code works grate.
I will try to do beater need time on describing my symptoms.
Again Thanks for the help and setting me strate.

Kevin

ASP Include is not working.	2	Aug 15, 2008
Paging	3	Jun 11, 2006
Pure ASP Upload - script unable to redirect for larger files	3	Aug 18, 2008
DNS Less Connection String with ASP	1	Feb 6, 2005
paging error handling	1	Jun 25, 2006
Recordset Errors / Friendly Message needed	8	Jul 29, 2004
Problem with Rnd Function	3	Jun 28, 2007
Connection string for a MySQL database needed-this is driving me nuts!	1	May 17, 2004

DNS-less Connections

slc

Veign

Bob Barrows [MVP]

brownk10

slc

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads