really baffled - a might OT about recordset EOF

I have some code running in the field on a test server. It is an asp app
executing a VB "dll". I have written stuff
to the event log to debug my code which is not working. (Or I would not be
bugging you all.)

A sql string is create, SELECT...

A recordset is opened from the string. Then

If Not rs.EOF then
FunctionReturn=false
end if

I logged the contents of the string and the value of rs.eof. When I pasted
the string into SQL Server, I get a row, as expected. Yet rs.eof is TRUE.
Have I lost my noodle? I knew that with certain cursor types you cannot rely
on RecordCount or whatever property that is that I have not used in a
millenium... But EOF?

Thanks.

Stephanie

Re: really baffled - a might OT about recordset EOF

Stephanie Stowe wrote:
> I have some code running in the field on a test server. It is an asp
> app executing a VB "dll". I have written stuff
> to the event log to debug my code which is not working. (Or I would
> not be bugging you all.)
>
> A sql string is create, SELECT...
>
> A recordset is opened from the string. Then
>
> If Not rs.EOF then
> FunctionReturn=false
> end if
>
> I logged the contents of the string and the value of rs.eof. When I
> pasted the string into SQL Server, I get a row, as expected. Yet
> rs.eof is TRUE. Have I lost my noodle? I knew that with certain
> cursor types you cannot rely on RecordCount or whatever property that
> is that I have not used in a millenium... But EOF?
>

Well, it says "Not rs.EOF" - should it return true for empty recorsets and
false for populated recordsets?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: really baffled - a might OT about recordset EOF

"Bob Barrows [MVP]" wrote in message
news:OFGE6U1yEHA.4028@TK2MSFTNGP15.phx.gbl...
> Stephanie Stowe wrote:
> > I have some code running in the field on a test server. It is an asp
> > app executing a VB "dll". I have written stuff
> > to the event log to debug my code which is not working. (Or I would
> > not be bugging you all.)
> >
> > A sql string is create, SELECT...
> >
> > A recordset is opened from the string. Then
> >
> > If Not rs.EOF then
> > FunctionReturn=false
> > end if
> >
> > I logged the contents of the string and the value of rs.eof. When I
> > pasted the string into SQL Server, I get a row, as expected. Yet
> > rs.eof is TRUE. Have I lost my noodle? I knew that with certain
> > cursor types you cannot rely on RecordCount or whatever property that
> > is that I have not used in a millenium... But EOF?
> >
>
> Well, it says "Not rs.EOF" - should it return true for empty recorsets and
> false for populated recordsets?
>


The recordset.eof is what I am testing. If I do

LogWarn "The value of recordset eof is: " & cstr(rs.eof)

where logwarn is a proc which writes a warning to the EventLog I am seeing

The value of the recordset eof is: true" in the event log despite the fact
that when I paste the exact same query into query analyzer, I get a row.

S

> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: really baffled - a might OT about recordset EOF

Is it possible that there are messages in Query Analyzer as well? E.g. have
you tried adding SET NOCOUNT ON to the beginning of the query?

--
http://www.aspfaq.com/
(Reverse address to reply.)




"Stephanie Stowe" wrote in message
news:#xbsMY1yEHA.3844@TK2MSFTNGP12.phx.gbl...
>
> "Bob Barrows [MVP]" wrote in message
> news:OFGE6U1yEHA.4028@TK2MSFTNGP15.phx.gbl...
> > Stephanie Stowe wrote:
> > > I have some code running in the field on a test server. It is an asp
> > > app executing a VB "dll". I have written stuff
> > > to the event log to debug my code which is not working. (Or I would
> > > not be bugging you all.)
> > >
> > > A sql string is create, SELECT...
> > >
> > > A recordset is opened from the string. Then
> > >
> > > If Not rs.EOF then
> > > FunctionReturn=false
> > > end if
> > >
> > > I logged the contents of the string and the value of rs.eof. When I
> > > pasted the string into SQL Server, I get a row, as expected. Yet
> > > rs.eof is TRUE. Have I lost my noodle? I knew that with certain
> > > cursor types you cannot rely on RecordCount or whatever property that
> > > is that I have not used in a millenium... But EOF?
> > >
> >
> > Well, it says "Not rs.EOF" - should it return true for empty recorsets
and
> > false for populated recordsets?
> >
>
>
> The recordset.eof is what I am testing. If I do
>
> LogWarn "The value of recordset eof is: " & cstr(rs.eof)
>
> where logwarn is a proc which writes a warning to the EventLog I am seeing
>
> The value of the recordset eof is: true" in the event log despite the fact
> that when I paste the exact same query into query analyzer, I get a row.
>
> S
>
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >
>
>

Re: really baffled - a might OT about recordset EOF

Stephanie Stowe wrote:
>>>
>>> I logged the contents of the string and the value of rs.eof. When I
>>> pasted the string into SQL Server, I get a row, as expected. Yet
>>> rs.eof is TRUE. Have I lost my noodle? I knew that with certain
>>> cursor types you cannot rely on RecordCount or whatever property
>>> that is that I have not used in a millenium... But EOF?
>>>
>>
>> Well, it says "Not rs.EOF" - should it return true for empty
>> recorsets and false for populated recordsets?
>>
>
>
> The recordset.eof is what I am testing. If I do
>
> LogWarn "The value of recordset eof is: " & cstr(rs.eof)
>
> where logwarn is a proc which writes a warning to the EventLog I am
> seeing
>
> The value of the recordset eof is: true" in the event log despite the
> fact that when I paste the exact same query into query analyzer, I
> get a row.
>

Have you done a Move in the recordset before testing EOF? If so, it may need
to be requeried. I've never seen a populated recordset return a true EOF
without some sort of navigation in the recordset having taken place. If you
check EOF immediately after opening the recordset, it should be pointed at
the first record if the recordset contains any recorcords at all, so EOF
will be false.

Please post a true repro for this issue if the above does not answer your
question.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: really baffled - a might OT about recordset EOF

Color me dense, but I don't understand this reply.

The entire function is (with little notes from me):


Public Function MakeInterfaceSQLCall(sSQL As String, Optional bLog As
Boolean) As ADODB.Recordset

Dim sCount As String

EnterContext "MakeInterfaceSQLCall"

If bLog Then LogWarn "SAS: Enterint MakeInterfaceSQLCall "

On Error GoTo Error_MakeInterfaceSQLCall

Set MakeInterfaceSQLCall = New ADODB.Recordset

MakeInterfaceSQLCall.ActiveConnection = dbConn
MakeInterfaceSQLCall.CursorLocation = adUseClient

If bLog Then LogWarn "SAS: ConnectionString - " & dbConn.ConnectionString
<---- I looked at the connection string to ensure that the correct database
was being queried (the same one that I am querying. It is.)
If bLog Then LogWarn "SAS: dbConn State - " & dbConn.State & vbCrLf & "State
open constant = " & adStateOpen <-- What the hell, my Dad always said "When
in doubt, do something." The connection is open.

If giDatabaseType = dbTypeORACLE Then <-- This is NOT an Oracle db.
This properly is not executed.
' Check for "TOP xx"
If InStr(UCase(sSQL), "SELECT TOP ") = 1 Then
sCount = parseString(sSQL, " ", 3, 3)
'Replace Top xx with ""
sSQL = Replace(sSQL, parseString(sSQL, " ", 2, 3) & " ", "")
MakeInterfaceSQLCall.MaxRecords = sCount
End If
MakeInterfaceSQLCall.Open sSQL, dbConn, adOpenForwardOnly,
adLockOptimistic
Else
MakeInterfaceSQLCall.Open sSQL, dbConn, adOpenForwardOnly,
adLockReadOnly <--- This is executed.

If bLog Then LogWarn "SAS: Just opened recordset with SQL String: " & vbCrLf
& sSQL <--- I go to event viewer and get this query which I will show below.
If bLog Then LogWarn "SAS recordset EOF = " & CStr(MakeInterfaceSQLCall.EOF)
<--- EOF is true. When I used query analyzer, and paste the SQL from above,
I get a row.
If bLog Then LogWarn "SAS: Record count = " &
CStr(MakeInterfaceSQLCall.RecordCount) <-- Just for giggled, I set
adOpenDynamic and adLockOptimistic just to see... I don't know what. This
recordcount is returning 0.



End If

' Disconnect the Recordset
Set MakeInterfaceSQLCall.ActiveConnection = Nothing

If giDatabaseType = dbTypeORACLE Then
UpdateOracleNulls MakeInterfaceSQLCall
End If

ExitContext
Exit Function

Error_MakeInterfaceSQLCall:
LogErr "Error Executing SQL: " & sSQL
LogErrRaiseErr

End Function


This function returns a disconnected recordset to the caller (obviously).

The query in question is:

SELECT ActivityHeaderID,ActivityStatusCode FROM vActivityOrderInfo WHERE
PatientID = 449583 AND VisitNumberEXT = '100080808236' AND OrderNumberEXT =
'8045943NEC'

Where vActivityOrderInfo is a view.

If I run the query as is in the query analyzer, I get a row and a message
"(1 row(s) affected)" When I run the query preceeded by SET NOCOUNT ON, I
get a row and no message.

Are you suggesting that the message information is part of the recordset I
am getting? I don't understand.


The thing that is weird is this exact same code is executed in another
context and returns the row as it should.

Thanks.


"Aaron [SQL Server MVP]" wrote in message
news:OY$lLh1yEHA.3708@TK2MSFTNGP14.phx.gbl...
> Is it possible that there are messages in Query Analyzer as well? E.g.
have
> you tried adding SET NOCOUNT ON to the beginning of the query?
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>
> "Stephanie Stowe" wrote in message
> news:#xbsMY1yEHA.3844@TK2MSFTNGP12.phx.gbl...
> >
> > "Bob Barrows [MVP]" wrote in message
> > news:OFGE6U1yEHA.4028@TK2MSFTNGP15.phx.gbl...
> > > Stephanie Stowe wrote:
> > > > I have some code running in the field on a test server. It is an asp
> > > > app executing a VB "dll". I have written stuff
> > > > to the event log to debug my code which is not working. (Or I would
> > > > not be bugging you all.)
> > > >
> > > > A sql string is create, SELECT...
> > > >
> > > > A recordset is opened from the string. Then
> > > >
> > > > If Not rs.EOF then
> > > > FunctionReturn=false
> > > > end if
> > > >
> > > > I logged the contents of the string and the value of rs.eof. When I
> > > > pasted the string into SQL Server, I get a row, as expected. Yet
> > > > rs.eof is TRUE. Have I lost my noodle? I knew that with certain
> > > > cursor types you cannot rely on RecordCount or whatever property
that
> > > > is that I have not used in a millenium... But EOF?
> > > >
> > >
> > > Well, it says "Not rs.EOF" - should it return true for empty recorsets
> and
> > > false for populated recordsets?
> > >
> >
> >
> > The recordset.eof is what I am testing. If I do
> >
> > LogWarn "The value of recordset eof is: " & cstr(rs.eof)
> >
> > where logwarn is a proc which writes a warning to the EventLog I am
seeing
> >
> > The value of the recordset eof is: true" in the event log despite the
fact
> > that when I paste the exact same query into query analyzer, I get a row.
> >
> > S
> >
> > > --
> > > Microsoft MVP -- ASP/ASP.NET
> > > Please reply to the newsgroup. The email account listed in my From
> > > header is my spam trap, so I don't check it very often. You will get a
> > > quicker response by posting to the newsgroup.
> > >
> > >
> >
> >
>
>

Re: really baffled - a might OT about recordset EOF

"Bob Barrows [MVP]" wrote in message
news:#T#4bg1yEHA.3808@TK2MSFTNGP15.phx.gbl...
> Stephanie Stowe wrote:
> >>>
> >>> I logged the contents of the string and the value of rs.eof. When I
> >>> pasted the string into SQL Server, I get a row, as expected. Yet
> >>> rs.eof is TRUE. Have I lost my noodle? I knew that with certain
> >>> cursor types you cannot rely on RecordCount or whatever property
> >>> that is that I have not used in a millenium... But EOF?
> >>>
> >>
> >> Well, it says "Not rs.EOF" - should it return true for empty
> >> recorsets and false for populated recordsets?
> >>
> >
> >
> > The recordset.eof is what I am testing. If I do
> >
> > LogWarn "The value of recordset eof is: " & cstr(rs.eof)
> >
> > where logwarn is a proc which writes a warning to the EventLog I am
> > seeing
> >
> > The value of the recordset eof is: true" in the event log despite the
> > fact that when I paste the exact same query into query analyzer, I
> > get a row.
> >
>
> Have you done a Move in the recordset before testing EOF?

Nope.

> If so, it may need
> to be requeried. I've never seen a populated recordset return a true EOF
> without some sort of navigation in the recordset having taken place.

Me neither. Thus the bafflement.

> If you
> check EOF immediately after opening the recordset, it should be pointed at
> the first record if the recordset contains any recorcords at all, so EOF
> will be false.
>

So I thought also.

> Please post a true repro for this issue if the above does not answer your
> question.
>

The entire function with my crazy event log entries are in my reply to
Aaron. I am at a complete loss on this one. I almost think I am not seeing
what I am seeing, and that there is an error in my troubleshooting
technique. It would not be the first time.


> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: really baffled - a might OT about recordset EOF

I am trying to tack SET NOCOUNT ON to the beginning of the query. (I get it
now.) I will post results.

S
"Aaron [SQL Server MVP]" wrote in message
news:OY$lLh1yEHA.3708@TK2MSFTNGP14.phx.gbl...
> Is it possible that there are messages in Query Analyzer as well? E.g.
have
> you tried adding SET NOCOUNT ON to the beginning of the query?
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>
> "Stephanie Stowe" wrote in message
> news:#xbsMY1yEHA.3844@TK2MSFTNGP12.phx.gbl...
> >
> > "Bob Barrows [MVP]" wrote in message
> > news:OFGE6U1yEHA.4028@TK2MSFTNGP15.phx.gbl...
> > > Stephanie Stowe wrote:
> > > > I have some code running in the field on a test server. It is an asp
> > > > app executing a VB "dll". I have written stuff
> > > > to the event log to debug my code which is not working. (Or I would
> > > > not be bugging you all.)
> > > >
> > > > A sql string is create, SELECT...
> > > >
> > > > A recordset is opened from the string. Then
> > > >
> > > > If Not rs.EOF then
> > > > FunctionReturn=false
> > > > end if
> > > >
> > > > I logged the contents of the string and the value of rs.eof. When I
> > > > pasted the string into SQL Server, I get a row, as expected. Yet
> > > > rs.eof is TRUE. Have I lost my noodle? I knew that with certain
> > > > cursor types you cannot rely on RecordCount or whatever property
that
> > > > is that I have not used in a millenium... But EOF?
> > > >
> > >
> > > Well, it says "Not rs.EOF" - should it return true for empty recorsets
> and
> > > false for populated recordsets?
> > >
> >
> >
> > The recordset.eof is what I am testing. If I do
> >
> > LogWarn "The value of recordset eof is: " & cstr(rs.eof)
> >
> > where logwarn is a proc which writes a warning to the EventLog I am
seeing
> >
> > The value of the recordset eof is: true" in the event log despite the
fact
> > that when I paste the exact same query into query analyzer, I get a row.
> >
> > S
> >
> > > --
> > > Microsoft MVP -- ASP/ASP.NET
> > > Please reply to the newsgroup. The email account listed in my From
> > > header is my spam trap, so I don't check it very often. You will get a
> > > quicker response by posting to the newsgroup.
> > >
> > >
> >
> >
>
>

Re: really baffled - a might OT about recordset EOF

This is making exactly zero sense to me. I see the row in the db. I am about
85% (closer to 99% but I am doubting myself) certain that at the point when
this code is executed, there are no pending transactions which have not been
committed. I am troubleshooting this in a live-ish environment. I have no
debugger. All I can do is send messages to the event log. I simply cannot
grasp why this would be happening.

Weird beyond my ability to comprehend. If anyone has any further
suggestions, I would love them.

Thanks for all the help. I appreciate it.

"Stephanie Stowe" wrote in message
news:#G$9JC#yEHA.3656@TK2MSFTNGP09.phx.gbl...
> I am trying to tack SET NOCOUNT ON to the beginning of the query. (I get
it
> now.) I will post results.
>
> S
> "Aaron [SQL Server MVP]" wrote in message
> news:OY$lLh1yEHA.3708@TK2MSFTNGP14.phx.gbl...
> > Is it possible that there are messages in Query Analyzer as well? E.g.
> have
> > you tried adding SET NOCOUNT ON to the beginning of the query?
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Stephanie Stowe" wrote in message
> > news:#xbsMY1yEHA.3844@TK2MSFTNGP12.phx.gbl...
> > >
> > > "Bob Barrows [MVP]" wrote in message
> > > news:OFGE6U1yEHA.4028@TK2MSFTNGP15.phx.gbl...
> > > > Stephanie Stowe wrote:
> > > > > I have some code running in the field on a test server. It is an
asp
> > > > > app executing a VB "dll". I have written stuff
> > > > > to the event log to debug my code which is not working. (Or I
would
> > > > > not be bugging you all.)
> > > > >
> > > > > A sql string is create, SELECT...
> > > > >
> > > > > A recordset is opened from the string. Then
> > > > >
> > > > > If Not rs.EOF then
> > > > > FunctionReturn=false
> > > > > end if
> > > > >
> > > > > I logged the contents of the string and the value of rs.eof. When
I
> > > > > pasted the string into SQL Server, I get a row, as expected. Yet
> > > > > rs.eof is TRUE. Have I lost my noodle? I knew that with certain
> > > > > cursor types you cannot rely on RecordCount or whatever property
> that
> > > > > is that I have not used in a millenium... But EOF?
> > > > >
> > > >
> > > > Well, it says "Not rs.EOF" - should it return true for empty
recorsets
> > and
> > > > false for populated recordsets?
> > > >
> > >
> > >
> > > The recordset.eof is what I am testing. If I do
> > >
> > > LogWarn "The value of recordset eof is: " & cstr(rs.eof)
> > >
> > > where logwarn is a proc which writes a warning to the EventLog I am
> seeing
> > >
> > > The value of the recordset eof is: true" in the event log despite the
> fact
> > > that when I paste the exact same query into query analyzer, I get a
row.
> > >
> > > S
> > >
> > > > --
> > > > Microsoft MVP -- ASP/ASP.NET
> > > > Please reply to the newsgroup. The email account listed in my From
> > > > header is my spam trap, so I don't check it very often. You will get
a
> > > > quicker response by posting to the newsgroup.
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Re: really baffled - a might OT about recordset EOF - UPDATE

It WAS an error in my troubleshooting. The upshot is that the things that
transpired between when my recordset was being opened and returning no rows,
more work was being done that added a new row. So after the code completed,
there was a row, just not the one I wanted.

All is well. Well not really since I still cannot help the customer with the
underlying problem. But at least I can rely on EOF! I really did think I was
going nuts. Sorry to waste your time. And thanks for the willingness to
help!

Stephanie