ADO Connections in ASP

[CrimsonDiva]

Hello,

I've had SEVERAL problems creating successful ADO connections with ASP and SQL Server 2000. I have an ASP connection file that has the following information (connection string altered for security):

<%
'Connection.asp

Dim objconn, ConnTemp
'opens the connection to the db
Function openDB()
'Dim objconn, ConnTemp
on error resume next
set objconn=Server.Createobject(&quot;ADODB.Connection&quot
set connTemp=Server.Createobject(&quot;ADODB.Connection&quot

'connection string
objconn.open &quot;PROVIDER=SQLOLEDB;DATA SOURCE=111.111.11.11;UID=nratler;PWD=123456;DATABASE=HTC&quot;
connTemp.open &quot;PROVIDER=SQLOLEDB;DATA SOURCE=111.111.11.11;UID=nratler;PWD=123456;DATABASE=HTC&quot;

if err.number <> 0 then
response.write Err.Description
end if

End Function

There is also a similar CloseDB() function that closes out the connections.

Question 1: The first problems arise when I try to move the DIM into the function instead of outside the function(commented out). When I move it into the function, it causes any page that calls this file to give a 500 internal error. Therefore, I MUST declare the connections outside of the function. Why is that?

Question 2: When I leave the DIM outside of the function, I STILL notice problems with connection strings in other ASP files that include this file:

<!--#include file=&quot;../connection.asp&quot;-->
Call OpenDB()
Set rstemp=Server.CreateObject(&quot;Adodb.RecordSet&quot
rstemp.cursorlocation = aduseclient
rstemp.Open mySQL, objconn
intrecords=rstemp.recordcount
.
.
.

When I try to run the code above, I get this error message:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Why? What's the problem? I've been struggling with this for weeks. Please help!

 

[FesterSXS]

in answer to your 2nd question, you need the adOpenStatic cursorType in order to get the recordcount of the recordset.

rstemp.Open mySQL, objconn, adOpenStatic

If you dont have the msado15.dll included then you can replace adOpenStatic with 3 Tony

http://www.SpiraxSarco.com/cad

 

[JamesLean]

Q1) This is a question of scope. If you Dim the variables inside the function they only have scope inside the function. You need to Dim them outside the function to enable other code to reference them.

Q2) Get rid of the brackets when calling your function:

...
Call OpenDB
Set rstemp...

you only need to use brackets when passing parameters. --James

 

[JamesLean]

Sorry, ignore my answer to Q2 - it's rubbish! Your Call should work fine.

If Tony's answer doesn't help, which line is throwing the error? --James

 

[CrimsonDiva]

Thanks to both of you. I've got the first question answered. I understand what's going on now.

But I'm still having problems with Question 2. I don't think the compiler has even gotten to the issue with the cursorType property, but I added it anyway. But, I STILL get the 0x800A0BB9 Error:


<!--#include file=&quot;../connection.asp&quot;-->
.
.
.
Set rstemp=Server.CreateObject(&quot;Adodb.RecordSet&quot
rstemp.cursorlocation = aduseclient
rstemp.CursorType = 3
rstemp.Open mySQL, objconn

.
.
.
Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

 

[JamesLean]

Can you tell us which line the error actually occurs on? What is the value of mySQL? --James

 

[baddos]

Are you doing this?

<!--#include file=&quot;../connection.asp&quot;-->

Call OpenDB()
Set rstemp=Server.CreateObject(&quot;ADODB.Recordset&quot
rstemp.CursorLocation = 3
rstemp.LockType = 1
rstemp.CursorType = 3
rstemp.Open mySQL, objconn
'Do some work w/ the DB
rstemp.Close
Call CloseDB()

 

[CrimsonDiva]

The error occurs on line 247 in the code, which is indicated below:

<!--#include file=&quot;../connection.asp&quot;-->
.
.
.
Set rstemp=Server.CreateObject(&quot;Adodb.RecordSet&quot
rstemp.cursorlocation = aduseclient
rstemp.CursorType = 3
rstemp.Open mySQL, objconn<--- Error

JamesLean - I tried the SQL statement in the Query Analyzer and it works fine. Here's the value of mySQL:
mySQL=&quot;Select user_info.user_id, user_info.first_name, user_info.last_name, user_info.organization_name, &quot; & _
&quot;login_info.user_name, login_info.expiration_date FROM user_info INNER JOIN login_info ON user_info.user_id=&quot; & _
&quot;login_info.user_id WHERE DATEDIFF(day, getdate(), login_info.expiration_date) < 0 AND login_info.expiration_date &quot; & _
&quot;<> '1/01/1980' ORDER BY user_info.last_name ASC&quot;


Baddos - Yes, Your code execution statments are correct. I do close out the connection with the CloseDB() function.

Here is ANOTHER area that I'm getting the same error message:

Call OpenDB()
Set rstemp=Server.CreateObject(&quot;Adodb.RecordSet&quot
rstemp.cursorlocation = aduseclient
rstemp.Open mySQL, objconn
intRecordsPerPage = 100
rstemp.PageSize = intRecordsPerPage
intNumberOfPages = rstemp.PageCount <--- Error
intrecords=rstemp.recordcount


As you can see, these pages are unrelated. Yet, I get the same error. ugh....

 

[CrimsonDiva]

I've figured out the answer to Question 2.
After I declared the global variables representing the connections. All subsequent re-dims of the variables needed to be moved from the code. I went through and did this and now, the program is running fine.