cannot reuse command objects under oracle oledb (on one system).

Short version:

ODBC configuration for the Oracle ODBC driver includes a checkbox for
"Enable closing cursors". Does anyone know how to get the equivalent
behavior with the Oracle OLEDB Provider?

Long version:

We have an application that manages web-based training. One of the functions
displays the structure of a course and allows the student to select a lesson
to launch. To support this we have a utility dll that contains a function
which returns the structure of a course as an array of objects. The DLL is
written in VB6.

The code in the function gets a list of the elements of a course from one
table into an array. Then it loops through the array and extracts data about
each element from one of 3 different tables. Finally it gets the student's
current status in each course element from another table. Command objects
with defined parameters are used to get the data from the child tables. This
code has been in use for several years against Access, Oracle, and SQLServer
and was not changed for our latest release. It works on our XP Pro
development systems, on our Windows 2000 Pro development systems, and it
worked under Windows 2003 server when we did some Oracle compatibility
testing a few months ago.

I have set up a test server for release testing (Windows 2000 sp4, latest
patches) against Access, SQLServer, and Oracle 9.2. The function errors out
with an "Object was open" error against Oracle on the second element in the
course structure array. It works fine against Access and SQL Server and the
same DLLs work fine against the same Oracle database from my XP system. It
has also worked in the past against Oracle 8.1.5 and Oracle 8.1.7 using the
Microsoft ODBC for Oracle driver. All systems are using the same Oracle
Network client and OLEDB provider.

The error occurs on the command object Execute method. Code snipets are
attached below.

Setting the "Prepared" property on the command object to False does not
help.
Destroying and recreating the command object for each use or using dynamic
SQL does work.

I had a similar (but not identical) problem with command objects in a C++
program a while back that was cleared up by checking the "Enable closing
cursors" checkbox in the Oracle ODBC configuration. I ended up replacing
command objects with dynamic SQL in that case but I would like to leave the
web-based part of the application alone if possible. I was hoping that there
is a similar setting for the OLEDB connection but I cannot find one in any
documentation.

Any help would be appreciated.

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

Windows 2000 SP4
Oracle client 9.2.0.1.0 running against Oracle 9.2 DBMS on separate server.
Oracle OLEDB 9.2.0.4.0

connection string (user id and password are inserted by app init code):
Provider=OraOLEDB.Oracle;Data Source=wbtman2;User
Id=%%uid%%;Password=%%pwd%%

command object defs:

'lesson info
30600 Set cmdLessons = Server.CreateObject("ADODB.Command")

30700 strSQL = "select name, lsnvname, fdisable, fshadow,hidden"

31100 If (nDataOptions And opdataMenu) Then
31200 strSQL = strSQL & ", imgurl, lsnenv"
31300 End If

31400 If (nDataOptions And opdataPhysicalURL) Then
31500 strSQL = strSQL & ", url"
31600 End If

31650 If (nDataOptions And opdataWindowSize) Then
31660 strSQL = strSQL & ", scrmode, screenx, screeny"
31670 End If

31674 If (nDataOptions And opdataDescription) Then
31675 strSQL = strSQL & ", descr"
31676 End If

31700 cmdLessons.CommandText = strSQL & " from lessons where
lesson_id=?"
31800 cmdLessons.ActiveConnection = dbConn
31900 cmdLessons.CommandType = adCmdText
32000 cmdLessons.Prepared = True
32100 Set cParam = cmdLessons.CreateParameter("id", adInteger,
adParamInput)
32200 cmdLessons.Parameters.Append cParam

'block info
32300 Set cmdBlocks = Server.CreateObject("ADODB.Command")

32400 strSQL = "select name, blkvname,hidden"

32520 If (nDataOptions And opdataMenu) Then
32550 strSQL = strSQL & ",lnch1st"
32580 End If

32584 If (nDataOptions And opdataDescription) Then
32585 strSQL = strSQL & ", descr"
32586 End If

32800 cmdBlocks.CommandText = strSQL & " from blocks where block_id=?"
32900 cmdBlocks.ActiveConnection = dbConn
33000 cmdBlocks.CommandType = adCmdText
33100 cmdBlocks.Prepared = True
33200 Set cParam = cmdBlocks.CreateParameter("id", adInteger,
adParamInput)
33300 cmdBlocks.Parameters.Append cParam

'refitem info
33400 Set cmdRefitems = Server.CreateObject("ADODB.Command")

33500 strSQL = "select name,refvname,fdisable,hidden "

39000 If (nDataOptions And opdataMenu) Then
39100 strSQL = strSQL & ", imgurl, url"
39200 End If

39300 If (nDataOptions And opdataPhysicalURL) Then
39400 strSQL = strSQL & ", url"
39500 End If

39600 If (nDataOptions And opdataWindowSize) Then
39700 strSQL = strSQL & ", scrmode, screenx, screeny"
39800 End If

39810 If (nDataOptions And opdataDescription) Then
39820 strSQL = strSQL & ", descr"
39830 End If

39900 cmdRefitems.CommandText = strSQL & " from refitems where
refitem_id=?"
40000 cmdRefitems.ActiveConnection = dbConn
40100 cmdRefitems.CommandType = adCmdText
40200 cmdRefitems.Prepared = True
40300 Set cParam = cmdRefitems.CreateParameter("id", adInteger,
adParamInput)
40400 cmdRefitems.Parameters.Append cParam

'status info
***this is the command object that throws the error later***
*** the if condition is true when the error occurs***
40500 If (nDataOptions And opdataStatus) Or (nDataOptions And
opdataState) Then

40600 strSQL = "select status, statusdt, fscore, offln from stats
where stud_id=" & CStr(nStudentID) & _
" and LNKTYPE=? and LNK=? and COURSE_ID"
40700 If bSharedStatus Then
40800 strSQL = strSQL & " is null"
40900 Else
41000 strSQL = strSQL & " = " & CStr(nCourseID)
41100 End If

41200 Set cmdStats = Server.CreateObject("ADODB.Command")
41300 cmdStats.CommandText = strSQL

41400 cmdStats.ActiveConnection = dbConn
41500 cmdStats.CommandType = adCmdText
41600 cmdStats.Prepared = True
41700 Set cParam = cmdStats.CreateParameter("typ", adChar,
adParamInput, 1)
41800 cmdStats.Parameters.Append cParam
41900 Set cParam = cmdStats.CreateParameter("id", adInteger,
adParamInput)
42000 cmdStats.Parameters.Append cParam

42100 End If


Section where error occurs. Error occurs at line 46900 for the second course
element entry (i=1)

45300 For i = 0 To nBounds

45400 Set objItem = aReturn(i)

45500 If objItem.EntryType = "l" Then
45600 cmdLessons.Parameters("id").Value = objItem.ID
45700 Set rsInfo = cmdLessons.Execute
45800 ElseIf objItem.EntryType = "b" Then
45900 cmdBlocks.Parameters("id").Value = objItem.ID
46000 Set rsInfo = cmdBlocks.Execute
46100 ElseIf objItem.EntryType = "r" Then
46200 cmdRefitems.Parameters("id").Value = objItem.ID
46300 Set rsInfo = cmdRefitems.Execute
46400 End If

46500 If (nDataOptions And opdataStatus) Or _
(nDataOptions And opdataState) Then
46600 cmdStats.Parameters("id").Value = objItem.ID
46700 cmdStats.Parameters("typ").Value =
UCase(objItem.EntryType)
46800 strSQL = cmdStats.CommandText & _
"//LNK:" & CStr(objItem.ID) & _
"//LNKTYPE:" & UCase(objItem.EntryType)
'debugging string
***error here*** 46900 Set rsStats = cmdStats.Execute
47000 End If

47100 If Not rsInfo.EOF Then
47200 GetStructureItemData1 dbConn, rsInfo, rsStats, objItem,
nCourseID, nDataOptions
47300 End If
47400 rsInfo.Close

47500 If (nDataOptions And opdataStatus) Or _
(nDataOptions And opdataState) Then
47600 rsStats.Close
47700 End If

47800 GetStructureItemData2 dbConn, objItem, nDataOptions

'get prerequisite script
47900 If (nDataOptions And opdataPrerequisiteScript) Then
48000 Call GetLsnBlkPrerequisite(dbConn, nCourseID, objItem.ID,
_
bTemp, strTmp, objItem.EntryType)
48100 objItem.PrerequisiteScript = strTmp
48200 End If

48300 Next

Re: cannot reuse command objects under oracle oledb (on one system).

"Mark Schupp" wrote in message
news:e1$fOVqSFHA.584@TK2MSFTNGP15.phx.gbl...
> Short version:
>
> ODBC configuration for the Oracle ODBC driver includes a checkbox for
> "Enable closing cursors". Does anyone know how to get the equivalent
> behavior with the Oracle OLEDB Provider?
>
> Long version:
>
> We have an application that manages web-based training. One of the
> functions displays the structure of a course and allows the student to
> select a lesson to launch. To support this we have a utility dll that
> contains a function which returns the structure of a course as an array of
> objects. The DLL is written in VB6.
>
> The code in the function gets a list of the elements of a course from one
> table into an array. Then it loops through the array and extracts data
> about each element from one of 3 different tables. Finally it gets the
> student's current status in each course element from another table.
> Command objects with defined parameters are used to get the data from the
> child tables. This code has been in use for several years against Access,
> Oracle, and SQLServer and was not changed for our latest release. It works
> on our XP Pro development systems, on our Windows 2000 Pro development
> systems, and it worked under Windows 2003 server when we did some Oracle
> compatibility testing a few months ago.
>
> I have set up a test server for release testing (Windows 2000 sp4, latest
> patches) against Access, SQLServer, and Oracle 9.2. The function errors
> out with an "Object was open" error against Oracle on the second element
> in the course structure array. It works fine against Access and SQL Server
> and the same DLLs work fine against the same Oracle database from my XP
> system. It has also worked in the past against Oracle 8.1.5 and Oracle
> 8.1.7 using the Microsoft ODBC for Oracle driver. All systems are using
> the same Oracle Network client and OLEDB provider.
>
> The error occurs on the command object Execute method. Code snipets are
> attached below.
>
> Setting the "Prepared" property on the command object to False does not
> help.
> Destroying and recreating the command object for each use or using dynamic
> SQL does work.
>
> I had a similar (but not identical) problem with command objects in a C++
> program a while back that was cleared up by checking the "Enable closing
> cursors" checkbox in the Oracle ODBC configuration. I ended up replacing
> command objects with dynamic SQL in that case but I would like to leave
> the web-based part of the application alone if possible. I was hoping that
> there is a similar setting for the OLEDB connection but I cannot find one
> in any documentation.
>
> Any help would be appreciated.
>
> --
> --Mark Schupp
> Head of Development
> Integrity eLearning
> www.ielearning.com
>
> Windows 2000 SP4
> Oracle client 9.2.0.1.0 running against Oracle 9.2 DBMS on separate
> server.
> Oracle OLEDB 9.2.0.4.0
>
> connection string (user id and password are inserted by app init code):
> Provider=OraOLEDB.Oracle;Data Source=wbtman2;User
> Id=%%uid%%;Password=%%pwd%%
>
> command object defs:
>
> 'lesson info
> 30600 Set cmdLessons = Server.CreateObject("ADODB.Command")
>
> 30700 strSQL = "select name, lsnvname, fdisable, fshadow,hidden"
>
> 31100 If (nDataOptions And opdataMenu) Then
> 31200 strSQL = strSQL & ", imgurl, lsnenv"
> 31300 End If
>
> 31400 If (nDataOptions And opdataPhysicalURL) Then
> 31500 strSQL = strSQL & ", url"
> 31600 End If
>
> 31650 If (nDataOptions And opdataWindowSize) Then
> 31660 strSQL = strSQL & ", scrmode, screenx, screeny"
> 31670 End If
>
> 31674 If (nDataOptions And opdataDescription) Then
> 31675 strSQL = strSQL & ", descr"
> 31676 End If
>
> 31700 cmdLessons.CommandText = strSQL & " from lessons where
> lesson_id=?"
> 31800 cmdLessons.ActiveConnection = dbConn
> 31900 cmdLessons.CommandType = adCmdText
> 32000 cmdLessons.Prepared = True
> 32100 Set cParam = cmdLessons.CreateParameter("id", adInteger,
> adParamInput)
> 32200 cmdLessons.Parameters.Append cParam
>
> 'block info
> 32300 Set cmdBlocks = Server.CreateObject("ADODB.Command")
>
> 32400 strSQL = "select name, blkvname,hidden"
>
> 32520 If (nDataOptions And opdataMenu) Then
> 32550 strSQL = strSQL & ",lnch1st"
> 32580 End If
>
> 32584 If (nDataOptions And opdataDescription) Then
> 32585 strSQL = strSQL & ", descr"
> 32586 End If
>
> 32800 cmdBlocks.CommandText = strSQL & " from blocks where block_id=?"
> 32900 cmdBlocks.ActiveConnection = dbConn
> 33000 cmdBlocks.CommandType = adCmdText
> 33100 cmdBlocks.Prepared = True
> 33200 Set cParam = cmdBlocks.CreateParameter("id", adInteger,
> adParamInput)
> 33300 cmdBlocks.Parameters.Append cParam
>
> 'refitem info
> 33400 Set cmdRefitems = Server.CreateObject("ADODB.Command")
>
> 33500 strSQL = "select name,refvname,fdisable,hidden "
>
> 39000 If (nDataOptions And opdataMenu) Then
> 39100 strSQL = strSQL & ", imgurl, url"
> 39200 End If
>
> 39300 If (nDataOptions And opdataPhysicalURL) Then
> 39400 strSQL = strSQL & ", url"
> 39500 End If
>
> 39600 If (nDataOptions And opdataWindowSize) Then
> 39700 strSQL = strSQL & ", scrmode, screenx, screeny"
> 39800 End If
>
> 39810 If (nDataOptions And opdataDescription) Then
> 39820 strSQL = strSQL & ", descr"
> 39830 End If
>
> 39900 cmdRefitems.CommandText = strSQL & " from refitems where
> refitem_id=?"
> 40000 cmdRefitems.ActiveConnection = dbConn
> 40100 cmdRefitems.CommandType = adCmdText
> 40200 cmdRefitems.Prepared = True
> 40300 Set cParam = cmdRefitems.CreateParameter("id", adInteger,
> adParamInput)
> 40400 cmdRefitems.Parameters.Append cParam
>
> 'status info
> ***this is the command object that throws the error later***
> *** the if condition is true when the error occurs***
> 40500 If (nDataOptions And opdataStatus) Or (nDataOptions And
> opdataState) Then
>
> 40600 strSQL = "select status, statusdt, fscore, offln from stats
> where stud_id=" & CStr(nStudentID) & _
> " and LNKTYPE=? and LNK=? and COURSE_ID"
> 40700 If bSharedStatus Then
> 40800 strSQL = strSQL & " is null"
> 40900 Else
> 41000 strSQL = strSQL & " = " & CStr(nCourseID)
> 41100 End If
>
> 41200 Set cmdStats = Server.CreateObject("ADODB.Command")
> 41300 cmdStats.CommandText = strSQL
>
> 41400 cmdStats.ActiveConnection = dbConn
> 41500 cmdStats.CommandType = adCmdText
> 41600 cmdStats.Prepared = True
> 41700 Set cParam = cmdStats.CreateParameter("typ", adChar,
> adParamInput, 1)
> 41800 cmdStats.Parameters.Append cParam
> 41900 Set cParam = cmdStats.CreateParameter("id", adInteger,
> adParamInput)
> 42000 cmdStats.Parameters.Append cParam
>
> 42100 End If
>
>
> Section where error occurs. Error occurs at line 46900 for the second
> course element entry (i=1)
>
> 45300 For i = 0 To nBounds
>
> 45400 Set objItem = aReturn(i)
>
> 45500 If objItem.EntryType = "l" Then
> 45600 cmdLessons.Parameters("id").Value = objItem.ID
> 45700 Set rsInfo = cmdLessons.Execute
> 45800 ElseIf objItem.EntryType = "b" Then
> 45900 cmdBlocks.Parameters("id").Value = objItem.ID
> 46000 Set rsInfo = cmdBlocks.Execute
> 46100 ElseIf objItem.EntryType = "r" Then
> 46200 cmdRefitems.Parameters("id").Value = objItem.ID
> 46300 Set rsInfo = cmdRefitems.Execute
> 46400 End If
>
> 46500 If (nDataOptions And opdataStatus) Or _
> (nDataOptions And opdataState) Then
> 46600 cmdStats.Parameters("id").Value = objItem.ID
> 46700 cmdStats.Parameters("typ").Value =
> UCase(objItem.EntryType)
> 46800 strSQL = cmdStats.CommandText & _
> "//LNK:" & CStr(objItem.ID) & _
> "//LNKTYPE:" & UCase(objItem.EntryType)
> 'debugging string
> ***error here*** 46900 Set rsStats = cmdStats.Execute
> 47000 End If
>
> 47100 If Not rsInfo.EOF Then
> 47200 GetStructureItemData1 dbConn, rsInfo, rsStats, objItem,
> nCourseID, nDataOptions
> 47300 End If
> 47400 rsInfo.Close
>
> 47500 If (nDataOptions And opdataStatus) Or _
> (nDataOptions And opdataState) Then
> 47600 rsStats.Close
> 47700 End If
>
> 47800 GetStructureItemData2 dbConn, objItem, nDataOptions
>
> 'get prerequisite script
> 47900 If (nDataOptions And opdataPrerequisiteScript) Then
> 48000 Call GetLsnBlkPrerequisite(dbConn, nCourseID,
> objItem.ID, _
> bTemp, strTmp, objItem.EntryType)
> 48100 objItem.PrerequisiteScript = strTmp
> 48200 End If
>
> 48300 Next
>
>

More info, only weirder.

I was able to duplicate the problem in a VBScript ASP page and play around
with it. Given the contents of the database the actual execution sequence
that takes place is:

'first loop iteration

cmdLessons.Parameters("id").Value = 1011
Set rsInfo = cmdLessons.Execute

'followed by
cmdStats.Parameters("id").Value = 1011
cmdStats.Parameters("typ").Value = "L"
Set rsStats = cmdStats.Execute

'data extracted from recordsets here

rsInfo.Close
rsStats.Close

'2nd loop iteration (the first iteration succeeds)

cmdLessons.Parameters("id").Value = 1012
Set rsInfo = cmdLessons.Execute

'followed by
cmdStats.Parameters("id").Value = 1012
cmdStats.Parameters("typ").Value = "L"
Set rsStats = cmdStats.Execute '*** Object open error occurs here***

Here is the weirdness. If I move the cmdStats segment to above the
cmdLessons segment then the error goes away. There is also virtually
identical code at other places in the application that appears to operate
properly.

My gut reaction at this point is to scrap the usage of command objects
entirely (would rather scrap the use of Oracle but don't have that option)
even though that will affect performance somewhat. I will try rebuilding the
test server to a lower service pack tomorrow and see what happens.

--
--Mark Schupp

Re:solved it

It finally occurred to me to check the MDAC version (duhh!). ComCheck
showed version conflicts with many of the DLLs. Reinstalled MDAC 2.7 sp1 and
the problem went away.

--
--Mark Schupp


"Mark Schupp" wrote in message
news:ucZWLF0SFHA.2520@TK2MSFTNGP09.phx.gbl...
> "Mark Schupp" wrote in message
> news:e1$fOVqSFHA.584@TK2MSFTNGP15.phx.gbl...
>> Short version:
>>
>> ODBC configuration for the Oracle ODBC driver includes a checkbox for
>> "Enable closing cursors". Does anyone know how to get the equivalent
>> behavior with the Oracle OLEDB Provider?
>>
>> Long version:
>>
>> We have an application that manages web-based training. One of the
>> functions displays the structure of a course and allows the student to
>> select a lesson to launch. To support this we have a utility dll that
>> contains a function which returns the structure of a course as an array
>> of objects. The DLL is written in VB6.
>>
>> The code in the function gets a list of the elements of a course from one
>> table into an array. Then it loops through the array and extracts data
>> about each element from one of 3 different tables. Finally it gets the
>> student's current status in each course element from another table.
>> Command objects with defined parameters are used to get the data from the
>> child tables. This code has been in use for several years against Access,
>> Oracle, and SQLServer and was not changed for our latest release. It
>> works on our XP Pro development systems, on our Windows 2000 Pro
>> development systems, and it worked under Windows 2003 server when we did
>> some Oracle compatibility testing a few months ago.
>>
>> I have set up a test server for release testing (Windows 2000 sp4, latest
>> patches) against Access, SQLServer, and Oracle 9.2. The function errors
>> out with an "Object was open" error against Oracle on the second element
>> in the course structure array. It works fine against Access and SQL
>> Server and the same DLLs work fine against the same Oracle database from
>> my XP system. It has also worked in the past against Oracle 8.1.5 and
>> Oracle 8.1.7 using the Microsoft ODBC for Oracle driver. All systems are
>> using the same Oracle Network client and OLEDB provider.
>>
>> The error occurs on the command object Execute method. Code snipets are
>> attached below.
>>
>> Setting the "Prepared" property on the command object to False does not
>> help.
>> Destroying and recreating the command object for each use or using
>> dynamic SQL does work.
>>
>> I had a similar (but not identical) problem with command objects in a C++
>> program a while back that was cleared up by checking the "Enable closing
>> cursors" checkbox in the Oracle ODBC configuration. I ended up replacing
>> command objects with dynamic SQL in that case but I would like to leave
>> the web-based part of the application alone if possible. I was hoping
>> that there is a similar setting for the OLEDB connection but I cannot
>> find one in any documentation.
>>
>> Any help would be appreciated.
>>
>> --
>> --Mark Schupp
>> Head of Development
>> Integrity eLearning
>> www.ielearning.com
>>
>> Windows 2000 SP4
>> Oracle client 9.2.0.1.0 running against Oracle 9.2 DBMS on separate
>> server.
>> Oracle OLEDB 9.2.0.4.0
>>
>> connection string (user id and password are inserted by app init code):
>> Provider=OraOLEDB.Oracle;Data Source=wbtman2;User
>> Id=%%uid%%;Password=%%pwd%%
>>
>> command object defs:
>>
>> 'lesson info
>> 30600 Set cmdLessons = Server.CreateObject("ADODB.Command")
>>
>> 30700 strSQL = "select name, lsnvname, fdisable, fshadow,hidden"
>>
>> 31100 If (nDataOptions And opdataMenu) Then
>> 31200 strSQL = strSQL & ", imgurl, lsnenv"
>> 31300 End If
>>
>> 31400 If (nDataOptions And opdataPhysicalURL) Then
>> 31500 strSQL = strSQL & ", url"
>> 31600 End If
>>
>> 31650 If (nDataOptions And opdataWindowSize) Then
>> 31660 strSQL = strSQL & ", scrmode, screenx, screeny"
>> 31670 End If
>>
>> 31674 If (nDataOptions And opdataDescription) Then
>> 31675 strSQL = strSQL & ", descr"
>> 31676 End If
>>
>> 31700 cmdLessons.CommandText = strSQL & " from lessons where
>> lesson_id=?"
>> 31800 cmdLessons.ActiveConnection = dbConn
>> 31900 cmdLessons.CommandType = adCmdText
>> 32000 cmdLessons.Prepared = True
>> 32100 Set cParam = cmdLessons.CreateParameter("id", adInteger,
>> adParamInput)
>> 32200 cmdLessons.Parameters.Append cParam
>>
>> 'block info
>> 32300 Set cmdBlocks = Server.CreateObject("ADODB.Command")
>>
>> 32400 strSQL = "select name, blkvname,hidden"
>>
>> 32520 If (nDataOptions And opdataMenu) Then
>> 32550 strSQL = strSQL & ",lnch1st"
>> 32580 End If
>>
>> 32584 If (nDataOptions And opdataDescription) Then
>> 32585 strSQL = strSQL & ", descr"
>> 32586 End If
>>
>> 32800 cmdBlocks.CommandText = strSQL & " from blocks where
>> block_id=?"
>> 32900 cmdBlocks.ActiveConnection = dbConn
>> 33000 cmdBlocks.CommandType = adCmdText
>> 33100 cmdBlocks.Prepared = True
>> 33200 Set cParam = cmdBlocks.CreateParameter("id", adInteger,
>> adParamInput)
>> 33300 cmdBlocks.Parameters.Append cParam
>>
>> 'refitem info
>> 33400 Set cmdRefitems = Server.CreateObject("ADODB.Command")
>>
>> 33500 strSQL = "select name,refvname,fdisable,hidden "
>>
>> 39000 If (nDataOptions And opdataMenu) Then
>> 39100 strSQL = strSQL & ", imgurl, url"
>> 39200 End If
>>
>> 39300 If (nDataOptions And opdataPhysicalURL) Then
>> 39400 strSQL = strSQL & ", url"
>> 39500 End If
>>
>> 39600 If (nDataOptions And opdataWindowSize) Then
>> 39700 strSQL = strSQL & ", scrmode, screenx, screeny"
>> 39800 End If
>>
>> 39810 If (nDataOptions And opdataDescription) Then
>> 39820 strSQL = strSQL & ", descr"
>> 39830 End If
>>
>> 39900 cmdRefitems.CommandText = strSQL & " from refitems where
>> refitem_id=?"
>> 40000 cmdRefitems.ActiveConnection = dbConn
>> 40100 cmdRefitems.CommandType = adCmdText
>> 40200 cmdRefitems.Prepared = True
>> 40300 Set cParam = cmdRefitems.CreateParameter("id", adInteger,
>> adParamInput)
>> 40400 cmdRefitems.Parameters.Append cParam
>>
>> 'status info
>> ***this is the command object that throws the error later***
>> *** the if condition is true when the error occurs***
>> 40500 If (nDataOptions And opdataStatus) Or (nDataOptions And
>> opdataState) Then
>>
>> 40600 strSQL = "select status, statusdt, fscore, offln from stats
>> where stud_id=" & CStr(nStudentID) & _
>> " and LNKTYPE=? and LNK=? and COURSE_ID"
>> 40700 If bSharedStatus Then
>> 40800 strSQL = strSQL & " is null"
>> 40900 Else
>> 41000 strSQL = strSQL & " = " & CStr(nCourseID)
>> 41100 End If
>>
>> 41200 Set cmdStats = Server.CreateObject("ADODB.Command")
>> 41300 cmdStats.CommandText = strSQL
>>
>> 41400 cmdStats.ActiveConnection = dbConn
>> 41500 cmdStats.CommandType = adCmdText
>> 41600 cmdStats.Prepared = True
>> 41700 Set cParam = cmdStats.CreateParameter("typ", adChar,
>> adParamInput, 1)
>> 41800 cmdStats.Parameters.Append cParam
>> 41900 Set cParam = cmdStats.CreateParameter("id", adInteger,
>> adParamInput)
>> 42000 cmdStats.Parameters.Append cParam
>>
>> 42100 End If
>>
>>
>> Section where error occurs. Error occurs at line 46900 for the second
>> course element entry (i=1)
>>
>> 45300 For i = 0 To nBounds
>>
>> 45400 Set objItem = aReturn(i)
>>
>> 45500 If objItem.EntryType = "l" Then
>> 45600 cmdLessons.Parameters("id").Value = objItem.ID
>> 45700 Set rsInfo = cmdLessons.Execute
>> 45800 ElseIf objItem.EntryType = "b" Then
>> 45900 cmdBlocks.Parameters("id").Value = objItem.ID
>> 46000 Set rsInfo = cmdBlocks.Execute
>> 46100 ElseIf objItem.EntryType = "r" Then
>> 46200 cmdRefitems.Parameters("id").Value = objItem.ID
>> 46300 Set rsInfo = cmdRefitems.Execute
>> 46400 End If
>>
>> 46500 If (nDataOptions And opdataStatus) Or _
>> (nDataOptions And opdataState) Then
>> 46600 cmdStats.Parameters("id").Value = objItem.ID
>> 46700 cmdStats.Parameters("typ").Value =
>> UCase(objItem.EntryType)
>> 46800 strSQL = cmdStats.CommandText & _
>> "//LNK:" & CStr(objItem.ID) & _
>> "//LNKTYPE:" & UCase(objItem.EntryType)
>> 'debugging string
>> ***error here*** 46900 Set rsStats = cmdStats.Execute
>> 47000 End If
>>
>> 47100 If Not rsInfo.EOF Then
>> 47200 GetStructureItemData1 dbConn, rsInfo, rsStats, objItem,
>> nCourseID, nDataOptions
>> 47300 End If
>> 47400 rsInfo.Close
>>
>> 47500 If (nDataOptions And opdataStatus) Or _
>> (nDataOptions And opdataState) Then
>> 47600 rsStats.Close
>> 47700 End If
>>
>> 47800 GetStructureItemData2 dbConn, objItem, nDataOptions
>>
>> 'get prerequisite script
>> 47900 If (nDataOptions And opdataPrerequisiteScript) Then
>> 48000 Call GetLsnBlkPrerequisite(dbConn, nCourseID,
>> objItem.ID, _
>> bTemp, strTmp, objItem.EntryType)
>> 48100 objItem.PrerequisiteScript = strTmp
>> 48200 End If
>>
>> 48300 Next
>>
>>
>
> More info, only weirder.
>
> I was able to duplicate the problem in a VBScript ASP page and play around
> with it. Given the contents of the database the actual execution sequence
> that takes place is:
>
> 'first loop iteration
>
> cmdLessons.Parameters("id").Value = 1011
> Set rsInfo = cmdLessons.Execute
>
> 'followed by
> cmdStats.Parameters("id").Value = 1011
> cmdStats.Parameters("typ").Value = "L"
> Set rsStats = cmdStats.Execute
>
> 'data extracted from recordsets here
>
> rsInfo.Close
> rsStats.Close
>
> '2nd loop iteration (the first iteration succeeds)
>
> cmdLessons.Parameters("id").Value = 1012
> Set rsInfo = cmdLessons.Execute
>
> 'followed by
> cmdStats.Parameters("id").Value = 1012
> cmdStats.Parameters("typ").Value = "L"
> Set rsStats = cmdStats.Execute '*** Object open error occurs here***
>
> Here is the weirdness. If I move the cmdStats segment to above the
> cmdLessons segment then the error goes away. There is also virtually
> identical code at other places in the application that appears to operate
> properly.
>
> My gut reaction at this point is to scrap the usage of command objects
> entirely (would rather scrap the use of Oracle but don't have that option)
> even though that will affect performance somewhat. I will try rebuilding
> the test server to a lower service pack tomorrow and see what happens.
>
> --
> --Mark Schupp
>
>