I'm running "sp_help mytable1" from ASP against SQL Server 2000 over ADODB. Other T-SQL commands work fine, including commands giving more than one result set. But, for this very command, the 6th and 7th record sets give:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
when I try to fetch the data.
I have a loop that, for any returned record sets, displays all data.
If I, instead of looping over all record sets, code one data-displaying loop per record set (i.e. a "manual" variant), the sp_help call works provided that I, before displaying the 6th and 7th record set, code an extra "set rs = rs.nextrecordset". I.e., for those record sets giving the above error, I have to do an extra nextrecordset.
So far, it's only with sp_help tablename this has occurred.
The general loop works fine in VB6 including the two problem recordsets. It's only in ASP that record set no. 6 and 7 are inaccessible without extra Next's.
Any hints?Can you post your asp code ?|||ASP code (the general one) follows below.
Enter sp_help mytable1 in a HTML form calling it.
<html>
<%
REM *************************
REM Subrutiner
REM *************************
REM Print a recordset's field details
sub printfields
FOR EACH FIELD IN rs.FIELDS
response.write("<p>Field name: " & FIELD.NAME)
response.write("<br>Type: " & LTRIM(cSTR(FIELD.TYPE)))
response.write("<br>Defined size: " & LTRIM(cSTR(FIELD.DEFINEDSIZE)))
response.write("<br>Actual size: " & LTRIM(cSTR(FIELD.ACTUALSIZE)))
NEXT
end sub
REM *************************
REM Huvudrutinen
REM *************************
REM *** Create connection ***
dim conn
set conn = createobject("ADODB.Connection.2.6")
conn.provider = "SQLOLEDB"
conn.connectionstring = "data source=myserver1;
initial catalog=mydatabase1;Integrated Security=SSPI;Trusted_Connection=Yes;"
REM *** Open and run ***
conn.open
set rs = conn.execute( request.form("sqlcmd"))
REM *** Output to web client ***
response.write("<u>" & request.form("sqlcmd") & "</u><br>")
REM printfields
do until rs is Nothing
do until rs.eof
for each field in rs.fields
response.write("<br>" & field.name & " = " & rs(field.name))
next
response.write("<br>----------")
rs.movenext
loop
set rs = rs.nextrecordset
loop
REM *** Close connection ***
conn.close
set conn = nothing
%>
</html>|||I *think* I've solved it by checking if the next recordset is closed - if so, do an extra next recordset. Code included below. Still don't understand why the recordset get a State value of "closed".
(How do I write an "if object is NOT nothing then..." ?)
<html>
<%
REM *** Create connection ***
dim conn
set conn = createobject("ADODB.Connection.2.6")
conn.provider = "SQLOLEDB"
conn.connectionstring = "data source=myserver1;
initial catalog=mydatabase1;
Integrated Security=SSPI;Trusted_Connection=Yes;"
REM *** Open and run ***
conn.open
set rs = conn.execute( request.form("sqlcmd"))
REM *** Output to web client ***
response.write("<u>" & request.form("sqlcmd") & "</u><br>")
REM do until rs.state = adStateClosed
do until rs is Nothing
do until rs.eof
for each field in rs.fields
response.write("<br>" & field.name & " = " & rs(field.name))
next
response.write("<br>----------")
rs.movenext
loop
REM next recordset
set rs = rs.nextrecordset
REM if there is a next recordset, check if it's closed and if so, go to next after this
REM Don't know why it's needed.
if rs is nothing then
response.write("nop")
else
if rs.state = adStateClosed then
set rs = rs.nextrecordset
end if
end if
loop
REM *** Close connection ***
conn.close
set conn = nothing
%>
</html>sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment