0x800A0E7D error--don't get it

[middletree]

I had some text links at the top of all my pages (in one include file),
which worked just fine. But I was asked to make it so that people in a
certain department, (this is an Intranet app) would see a particular link
that nobody else would. You'd find their name by getting their network
logon. This worked fine on all my pages, but for some reason, on one page,
it gives me an error. First, the error:

Error Type:
ADODB.Recordset (0x800A0E7D)
The connection cannot be used to perform this operation. It is either closed
or invalid in this context.
/includes/header.inc, line 32

Before we go any farther, please note that I went to ASPFAQ.com, and
couldn't find an answer there which fit my situation. The article that
seemed to have the best shot for me was
http://www.aspfaq.com/show.asp?id=2191, but no dice.

Anyway, here's my code:

'this first line is there because when you get the network logon name, it is
prefixed by the domain name and a backslash. Since the db I am pulling data
from has only the logon name, I had to get rid of the first part:

strLogon= Replace(Request.ServerVariables("LOGON_USER"),"domainname\","")
Set RSLogon = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeID, DepartmentID "
strSQL = strSQL & "FROM Employee "
strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
'this next line is the infamous line 32
RSLogon.Open strSQL, objConnection
objConnection.execute strSQL,,&h00000080

strDepartmentID = RSLogon("DepartmentID")
strEmployeeID = RSLogon("EmployeeID")
RSLogon.Close
Set RSLogon = Nothing

If strDepartmentID = "9" then%>
do some stuff
End if



Keep in mind that this code works fine on other pages, just not this one. In
fact, it works when you first bring this page up, but it does submit to
itself, and when it does, you should see a "thank-you"-type message, but get
the error instead. But in the code at the top of the page, before it decides
if it's the original page or the repost, I have the same includes which
establish the same db connection. I mention this because the first thing
you look for is the difference between the pages that work and the ones that
don't. In fact, this code is in an include file which is used on all pages,
and that rules out any differences in code, you'd think.

 

[Mark Schupp]

First, you do not seem to be opening a database connection.
Second, you seem to be executing the query twice.
Third, you are not checking to see if the query returns anything before
attempting to access column data.
Fourth, you should not explicitly create a recordset for that type of query.
Try:

strSQL = "SELECT EmployeeID, DepartmentID "
strSQL = strSQL & "FROM Employee "
strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
Set RSLogon = objConnection.execute(strSQL,,1)
If Not RSLogon.EOF Then
strDepartmentID = RSLogon("DepartmentID")
strEmployeeID = RSLogon("EmployeeID")
End If
RSLogon.Close
Set RSLogon = Nothing


--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com

 

[middletree]

OK, I'll try it out. Thanks.

I am not sure why I shouldn't use a recordset, though.

I'm also not sure why this works on every other page which uses this same
exact code.

 

[Bob Barrows]

First, you do not seem to be opening a database connection.

Right, I'm not sure of this as well. Middletree, have you simply not shown
us the code where you opened the objConnection object?

Second, you seem to be executing the query twice.

That is true. Middletree, was one of these staements supposed to be
commented out?
RSLogon.Open strSQL, objConnection
objConnection.execute strSQL,,&h00000080

The second statement is simply not correct. It should not be there. That is
the statement that should be used to execute a non-records-returning query.
Actually, I prefer to explicitly set the Options argument in the Open
statement (it's got nothing to do with your problem)

RSLogon.Open strSQL, objConnection,,,1 '1 = adCmdText

Third, you are not checking to see if the query returns anything
before attempting to access column data.

Right. Always check the recordset's EOF property before attempting to access
any of its data.

Fourth, you should not explicitly create a recordset for that type of
query. Try:

Why not? What possible difference can that make? Whether you explicitly
create a recordset and then call its Open method, or use Execute to tell ADO
to create a recordset and open it should make absolutely no difference. I
hate when this advice is given because:
1. It's a waste of time - it's never the root of the problem
2. It confuses newcomers who think you are telling them not to use a
recordset - we're here to help people, not to further confuse them.

Bob Barrows

 

[Bob Barrows]

OK, I'll try it out. Thanks.

I am not sure why I shouldn't use a recordset, though.

To Mark: See? He thought you were telling him not to use a recordset!

middletree, just to clarify: you are always using a recordset, even when you
use Execute (unless you use that &h00000080 constant (adExecuteNoRecords) to
tell ADO not to create a recordset).

FWIW, I have never seen a problem solved by switching from

Set rs=server.createobject("adodb.recordset")
rs.open ...

to

set rs = conn.execute(...)

I have seen cases where problems were masked by the switch, but the root
problem was never addressed because the switch made everything "work".

Bob Barrows

 

[middletree]

Bob:

As always, thanks for you help. I have comments below:

Right, I'm not sure of this as well. Middletree, have you simply not shown
us the code where you opened the objConnection object?

Well, I have an include file for every page which establishes the
connection. But that's on the pages where this works, and the one where it
doesn't.
Anyway, here's that code:
Dim strDBConnection
strDBConnection = _
"Provider=SQLOLEDB;" & _
"Persist Security Info=False;" & _
"Data Source=xxxxxx;" & _
"User ID=xxxxx;" & _
"Password=xxxxx;" & _
"Database=xxxxxx;"

Dim objConnection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open strDBConnection

That is true. Middletree, was one of these statements supposed to be
commented out?
RSLogon.Open strSQL, objConnection
objConnection.execute strSQL,,&h00000080

What happened there was I tried a million things before being so exhausted
by the whole thing that I posted a question.
Here's the code which is currently in use, and which works on all pages
except the one. Note that the Recordset is simply called RS, not RSLogon,
as the use of a rs name that was used elsewhere was one of many things I
thought might be a conflict.
(I replaced our network domain name with x's)

strLogon= Replace(Request.ServerVariables("LOGON_USER"),"xxxxx\","")

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT EmployeeID, DepartmentID "
strSQL = strSQL & "FROM Employee "
strSQL = strSQL & "WHERE NetworkID = '"&strLogon&"'"
RS.Open strSQL, objConnection

strDepartmentID = rs("DepartmentID")
strEmployeeID = rs("EmployeeID")
rs.Close
Set rs = Nothing

If strDepartmentID = "9" then%>
<img src="images/spacer.gif" width="22" border="0">
<a href="DisplaySortableTickets.asp?selectTSE=<%=strEmployeeID%>">
<span class="cellsmall">Your open Tickets</span></a>
<%Else
end if

The second statement is simply not correct. It should not be there. That is
the statement that should be used to execute a non-records-returning query.
Actually, I prefer to explicitly set the Options argument in the Open
statement (it's got nothing to do with your problem)

RSLogon.Open strSQL, objConnection,,,1 '1 = adCmdText


Right. Always check the recordset's EOF property before attempting to access
any of its data.

Noted. thanks.

 

[Mark Schupp]

Fourth, you should not explicitly create a recordset for that type of

Why not? What possible difference can that make? Whether you explicitly
create a recordset and then call its Open method, or use Execute to tell ADO
to create a recordset and open it should make absolutely no difference. I
hate when this advice is given because:
1. It's a waste of time - it's never the root of the problem
2. It confuses newcomers who think you are telling them not to use a
recordset - we're here to help people, not to further confuse them.

2 lines of code = 2 chances to mess up
1 line of code = 1 chance.

I probably would not have bothered with that bit of advice except that he
use both rs.open and conn.execute on the same SQL statement which indicates
a bit of confusion about recordset usage anyway. My guess as to his actual
problem is that he is not getting any records back. Since he was not
checking for EOF this would cause an error.

 

[middletree]

I probably would not have bothered with that bit of advice except that he
use both rs.open and conn.execute on the same SQL statement which indicates
a bit of confusion about recordset usage anyway. My guess as to his actual
problem is that he is not getting any records back. Since he was not
checking for EOF this would cause an error.

You are correct when you say that I am confused about recordsets. For some
reason, in 4 years of doing ASP (although not 40 hours per week), I have not
gotten connection strings, recordsets, anything related to that sort of
thing. Not sure why. I'm not dumb. 2 college degrees, won the spelling bee
in 5th grade, etc.

But it (lack of understanding about rs's and connections) usually doesn't
pose a problem, as I have code I can re-use. But I cannot emphasize enough
that what you saw of my code was the last in a chain of trying a million
different things. I can assure you that most of the day, I wasn't trying to
connect twice.

 

[middletree]

Well, I put in Mark's code below, and still got an "Object Required" error.
Only on that one page. Not sure why. But since the line it pointed to was
the objConnection.execute, I simply pasted in this code from the db
connection include file that I have at the top of all pages:

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open strDBConnection


I have no idea why this works, but it does. Doesn't seem to affect any
other pages negatively, so I'm thankful for that. But it shouldn't have been
a problem on this one page, as the include file is here on this page, as
well, so objConnection should be there. I have no idea why this fixed it,
but am not up for spending any more time on this, as I have other fish to
fry.

thanks for your help, guys!

James W
www.middletree.net