ADO resultset returns an EOF when called from a proc in ASP 1

[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, let me know... Thanks for the assist...

 

[FesterSXS]

yes - please post the relevant code Tony

http://www.SpiraxSarco.com/cad

 

[Palooka]

If you use the same recordset, you have to reset the position to the beginning; recordset.MoveFirst()

Palooka

 

[shanegarcia]

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("ADODB.Connection&quot
Set adrsICD = Server.CreateObject("ADODB.Recordset&quot

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

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

 

[Palooka]

Well, I notice that you use identical names for you variables, both global ones and the ones in your sub. (AdoConn, adrsICD, etc.) It might be some kind of problems regarding the scope.


Also, remember that if you convert your container array into a delimitered string, you could make your code much smoother. It would speed up your code, as it only accesses the database once.

Something like this:

strContainer = "'Cont1', 'Cont2', 'Cont3'"

Then you can avoid the FOR-loop, and just go:

sSQL = "SELECT * FROM EIR_Table WHERE ContainerNo IN (" & strContainer & ") AND Out_ContainerStatus = ''"

Then, in your sub, you would go:

do while not adrsIDC.EOF
   response.write &quot;<tr>&quot;
   response.write &quot;...some stuff...&quot;
   response.write rs(&quot;ContainerNo&quot;)
   response.write &quot;...more stuff...&quot;
   response.write &quot;</tr>&quot;
   rs.MoveNext
loop

Palooka

 

[shanegarcia]

Palooka,

Thanks for the help. I will try your suggestions and see if I can get the code to work.

 

[shanegarcia]

Hi Palooka,

Your tip about the different approach and not using the array did it for me. I got the code to work. Still can't get the reason why it won't work when called from a sub though. Anyway, thanks for all the help. Really appreciate it.... =)

Shane