ADO Open Recordset problem 
Author Message
 ADO Open Recordset problem

I am trying to open a record set using the following syntax:

Set rs = New ADODB.Recordset
rs.Open sSQL, cnHaz, adOpenForwardOnly, adLockReadOnly, adCmdText
If (Not rs.EOF) And (Not rs.BOF) Then
    .....

In the above,
sSQL = "cp_GetUserSecurity 'ABCDEF9999'"
cnHaz is an ADODB.Connection object.

cp_GetUserSecurity is a stored procedure defined as


Create Table #FacilitySecuredObj (SecuredObjectID int, FormName varchar(50),
ControlName varchar(50), ControlIndex smallint, FacilityID smallint)

Insert  #FacilitySecuredObj
Select  SecuredObjectID, FormName, ControlName,ControlIndex ,FacilityID
From  securedObjectControls , Facility

Create Table #UserSecurity (SecuredObjectID int, FacilityID smallint, Access
char(4))

Insert #UserSecurity
SELECT  SecurityKey.SecuredObjectID, SecurityKey.FacilityID,
SecurityKey.Access
FROM  SecurityKey

SELECT  T1.FacilityID, T2.Access, T1.FormName, T1.ControlName,
T1.ControlIndex
FROM   #FacilitySecuredObj T1 LEFT JOIN #UserSecurity T2 ON
 T1.SecuredObjectID = T2.SecuredObjectID AND
 T1.FacilityID = T2.FacilityID
GO

After the rs.Open is executed in debug mode, I put my cursor over rs.EOF and
the following message is displayed as a tooltip message "Operation is not
allowed when the object is closed" when it should be telling me that rs.EOF
is false.

If a simpler stored procedure is executed using this same code, no problem.
It appears to be a problem when the stored procedure creates temporary
tables, etc. even thou the final outcome is rows returned by the last
SELECT.

***I have tried using several different ADO object library versions (2.6,
2.6, 2.1) and they all produce the same result.

Can anyone help me?

Thank you in advance for your help.



Wed, 11 Feb 2004 04:20:23 GMT  
 ADO Open Recordset problem
Never mind.  I just figured it out by adding 3 words in the sp after hours
of troubleshooting.  The 3 words are SET NOCOUNT ON.  See
http://www.adodb.com/errors_new_result.asp?errors_id=692 for details if you
are interested.



Quote:
> I am trying to open a record set using the following syntax:

> Set rs = New ADODB.Recordset
> rs.Open sSQL, cnHaz, adOpenForwardOnly, adLockReadOnly, adCmdText
> If (Not rs.EOF) And (Not rs.BOF) Then
>     .....

> In the above,
> sSQL = "cp_GetUserSecurity 'ABCDEF9999'"
> cnHaz is an ADODB.Connection object.

> cp_GetUserSecurity is a stored procedure defined as


> Create Table #FacilitySecuredObj (SecuredObjectID int, FormName
varchar(50),
> ControlName varchar(50), ControlIndex smallint, FacilityID smallint)

> Insert  #FacilitySecuredObj
> Select  SecuredObjectID, FormName, ControlName,ControlIndex ,FacilityID
> From  securedObjectControls , Facility

> Create Table #UserSecurity (SecuredObjectID int, FacilityID smallint,
Access
> char(4))

> Insert #UserSecurity
> SELECT  SecurityKey.SecuredObjectID, SecurityKey.FacilityID,
> SecurityKey.Access
> FROM  SecurityKey

> SELECT  T1.FacilityID, T2.Access, T1.FormName, T1.ControlName,
> T1.ControlIndex
> FROM   #FacilitySecuredObj T1 LEFT JOIN #UserSecurity T2 ON
>  T1.SecuredObjectID = T2.SecuredObjectID AND
>  T1.FacilityID = T2.FacilityID
> GO

> After the rs.Open is executed in debug mode, I put my cursor over rs.EOF
and
> the following message is displayed as a tooltip message "Operation is not
> allowed when the object is closed" when it should be telling me that
rs.EOF
> is false.

> If a simpler stored procedure is executed using this same code, no
problem.
> It appears to be a problem when the stored procedure creates temporary
> tables, etc. even thou the final outcome is rows returned by the last
> SELECT.

> ***I have tried using several different ADO object library versions (2.6,
> 2.6, 2.1) and they all produce the same result.

> Can anyone help me?

> Thank you in advance for your help.



Wed, 11 Feb 2004 04:58:25 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. ADO Open recordset problem

2. ADO Open recordset problem

3. Open ADO recordset on another ADO recordset - possible?

4. ADO-Problems w. Recordset.Open method

5. Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

6. ADO Recordset .OPEN problem

7. Problem with SQL on ADO Recordset Open

8. Problem opening recordset for update ADO 2.1/Jet 4.0/Access 2000 stored proc

9. ADO 2.5 Recordset.Open Timeout problem

10. ADO fails to release Recordset cursor after opening disconnected Recordset

11. Opening an ADOX Catalog Whilst ADO Recordset is Open

12. Opening recordsets(OpenRecordset vs. Recordset.Open)

 

 
Powered by phpBB® Forum Software