ADO resultset returns an EOF when called as a procedure...

[shanegarcia]

Hi, I have an ASP program that queries a SQL database based on multiple user selections stored in an array. I then SPLIT() the array to get at individual items.

My problem is that after the initial pass (which gets data successfully), the second call of the sub with the corresponding parameters dies with an EOF. Consequently, the other items don't display. I know that there are other records which match the query coz I've checked it manually. It doesn't occur in VB though. Just in this ASP program....

I've been trying to figure out this weird behavior but can't seem to hack it... any help would be greatly appreciated.

Actually, I'm pretty desperate... Help!!! =)

If you need to take a look at the code, I can post it... Thanks for the assist...

 

[mwolf00]

Have you evaluated the second query that you are executing? response.write each query to see what it's doing.


for x = 0 to uBound(fieldArr)
strSQL = "SELCT * FROM myTable WHERE field = '" & fieldArr(x) & "'"
set objrs = objCN.execute(strSQL)
if objrs.eof then
response.write &quot;<br>&quot; & strsql
else
response.write &quot;<br>&quot; & objrs(0)
end if
next



If this doesn't help, please post your code. Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[shanegarcia]

Yes, I've actually done Response.Write debugging already that's why I saw that the subsequent calls were getting EOF...

Ok, here's the procedure code that I call....I've cut out some of the code, namely the other Response.writes...to make it shorter but none of the important stuff...

Sub ShowData(strSQL, ctr)

Dim AdoConn, adrsICD, ConnStr
Dim intRecordsAffected

Set AdoConn = Server.CreateObject(&quot;ADODB.Connection&quot
Set adrsICD = Server.CreateObject(&quot;ADODB.Recordset&quot

ConnStr = &quot;PROVIDER=MSDASQL;DRIVER={SQL SERVER};SERVER=TEST_SERVER;DATABASE=ICDSYS;UID=sa;PWD=&quot;

AdoConn.Open ConnStr

set adrsICD = AdoConn.Execute(strSQL,intRecordsAffected)
If NOT adrsICD.EOF Then
Response.Write(&quot;<TR align=center>&quot
Response.Write(&quot;<TD ALIGN=center BGCOLOR=&quot; & rColor & &quot; NOWRAP><font face=Tahoma size=1>&quot; & cstr(ctr) & &quot;</TD>&quot
Response.Write(&quot;</TR>&quot
End If

adrsICD.Close
AdoConn.Close
Set adrsICD = Nothing
Set AdoConn = Nothing

End Sub%>


Here's the main routine that calls this sub:

<%
Dim adrsICD, adrsPorts, AdoConn, ConnStr, sSQL, ctr, rColor
Dim intRecordsAffected, n
Dim cntArr
Set AdoConn = Server.CreateObject(&quot;ADODB.Connection&quot
Set adrsICD = Server.CreateObject(&quot;ADODB.Recordset&quot

ConnStr = &quot;PROVIDER=MSDASQL;DRIVER={SQL SERVER};SERVER=TEST_SERVER;DATABASE=ICDSYS;UID=sa;PWD=&quot;

AdoConn.Open ConnStr

If Request.Form(&quot;SelType&quot = &quot;0&quot; Then
If Request.Form(&quot;chkCnt&quot = &quot;&quot; Then
Response.Write(&quot;<hr>&quot
Response.Write(&quot;<CENTER><H1>No containers were selected!</H1>&quot
Response.Write(&quot;<CENTER><H2>Please select containers for JIT!</H2>&quot
Response.End
Else
cntArr = Split(Request.Form(&quot;chkCnt&quot,&quot;,&quot
Call ShowHeader
For n = LBound(cntArr) to UBound(cntArr)
sSQL = &quot;SELECT * FROM EIR_Table WHERE ContainerNo = '&quot; & cntArr & &quot;' AND Out_ContainerStatus = ''&quot;
Call ShowData(sSQL,n)
Next
End If
Else
sSQL = &quot;SELECT * FROM EIR_Table WHERE ContainerNo = '&quot; & Request.QueryString & &quot;' AND Out_ContainerStatus = ''&quot;
ShowHeader
ShowData sSQL, 1
End If
Response.Write(&quot;</Table>&quot%>

Thanks in advance, guys....

 

[rmcmorris]

I am running into a very similar problem. My query is only returning the first record, even though there are 30 records. I have found that in my query, if I use a numeric field, all of the records return, but if I try to match a string field, only the first record returns. I have found that if I change the = sign to the LIKE operator, all of the records return fine. For example:

thenum=123
&quot;Select * from mytable where mynum=&quot; & thenum
works fine, but

theword=&quot;abc&quot;
&quot;Select * from mytable where myword='&quot; & theword & &quot;'&quot;
will only return the first matching record, but

theword=&quot;abc&quot;
&quot;Select * from mytable where myword LIKE '&quot; & theword & &quot;'&quot;
will return all records.

I'll post this as a seperate post to see if I can find anything else about this problem...

Regan

 

[kxfan]

Have a very similar situation. MoveNext always seems to go to EOF. I've been able to reproduce the problem on winxp and win2k. Windows Script Host version 5.6
This is an LDAP query returning a recordset. Code excerpt from

http://www.rallenhome.com/books/ad2e/source/ch_20_searchad_function.vbs.txt

CODE EXCERPT:
'**********************************************************************
'Populate the command object in order to execute a query through the
'linked connection. Set the text of the query command (i.e., the search),
'the max number of results to return, the timeout in seconds to wait
'for the query, and whether the results are to be cached.
'**********************************************************************
objComm.CommandText = "<" & strLDAPBase & ">;" & strCriteria & ";" _
& strAttributeList & ";" & strDepth
objComm.Properties("Page Size") = 10000
objComm.Properties("Timeout") = 60
objComm.Properties("searchscope") = ADS_SCOPE_SUBTREE
objComm.Properties("Cache Results") = False

'**********************************************************************
'Execute the command through the linked connection
'**********************************************************************
Err.Clear
Set objRS = nothing
Set objRS = objComm.Execute
'**********************************************************************
'If there was an error, then return FALSE
'**********************************************************************
If Err Then
objConn.Close
Set objRS = Nothing
SearchAD = False
Else
intArrayIndex = 0
stdout.write "PRE-COUNT: "
stdout.write objRS.recordcount
stdout.write " EOF: "
stdout.write objRS.EOF
stdout.write " BOF: "
stdout.writeline objRS.BOF
while not objRS.EOF
stdout.writeline "INDEX: "
stdout.writeline intArrayIndex
intArrayIndex = intArrayIndex + 1
objRS.MoveNext
stdout.write "COUNT: "
stdout.write objRS.recordcount
stdout.write " EOF: "
stdout.write objRS.EOF
stdout.write " BOF: "
stdout.writeline objRS.BOF
wend
end if


and the output:
PRE-COUNT: 50 EOF: False BOF: False
INDEX: 0
COUNT: 50 EOF: True BOF: False

 

[mwolf00]

WHen you use "stdout.write objRS.recordcount" you go to EOF (that's how it gets the count) - you should then use "objRS.moveFirst"

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[kxfan]

Almost. objRS.RecordCount leaves the pointer at the first record (not the last record or EOF), objRS.movenext then moves it from the first record to EOF. If you have a look at the output above, EOF is false immediatly after the RecordCount test and TRUE after movenext. By printing my data out I know that the recordset pointer is at the first record. HOWEVER, You've pointed me in a different direction and I've avoided using "objRS.RecordCount" altogether to get around the problem. If I don't use objRS.Recordcount, objRS.MoveNext works fine.

Tested by testing objRS.RecordCount BEFORE my while loop and NOT using objRS.RecordCount inside the loop. The loop executes the first iteration, then moveNext within the loop moves the pointer to eof every time. When I don't test RecordCount before entering the loop, .MoveNext operates as expected. Still think it's weird but I'll get over it.

Thanks for the nudge in the right direction though!