SP w/ADO not returning recordset 
Author Message
 SP w/ADO not returning recordset

I've got a couple of stored procedures of the type shown below. The SP uses
multiple selects in order to collate the data properly prior to returing a
recordset to the user. It works fine from ISQL.

When I import it into my project's SQL Designer, I initially do not allow it
to execute it since it will fail anyway w/o a parameter (hello, MS: Ever
think of ASKING for them first?). After the command is created, I alter its
properties and rerun the command - to no avail. It still doesn't think it
returns a record set.

I know this is a result of the fact that the results are transitory since
they use a temporary table. But I am supposed to be able to retrieve the
data if using a particular type of open statement. The code below should
work but doesn't:

   Set cmd = dbc_Data.Commands("dbo_Get_Prog_Def_Terms_spr")
   With cmd
      .ActiveConnection = dbc_Data.env_Data

      .Execute
   End With
   Set rs = New ADODB.Recordset
   With rs
      .ActiveConnection = dbc_Data.env_Data
      .CursorLocation = adUseClient
      .CursorType = adOpenKeyset
      .LockType = adLockReadOnly
      .Open cmd
   End With
   rs.MoveLast

It faiils at the rs.MoveLast statement saying the object isn't open.

SP follows:

BEGIN

SELECT Termination_Class
INTO #tmp_Term_Class
FROM Codes_Prog_Term_Groups

SELECT
Termination_Code,
Description,
Termination_Class
FROM Codes_Termination

WHERE Codes_Termination.Active <> 0
  AND ( Codes_Termination.Termination_Class = "ALL" OR
       Codes_Termination.Termination_Class IN ( SELECT * FROM
#tmp_Term_Class ))

ORDER BY Codes_Termination.Termination_Code

END



Sun, 28 Sep 2003 04:45:52 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. ADO RecordCount is not working when Recordset is returned by COM Component

2. ADO not returning correct recordset

3. Ado Called Stored Proc not returning recordset

4. pass parameter to SQL SP from VB and return recordset

5. Raiserror in a SP that return a Recordset

6. Returning SP from SQL with ADO

7. ODBC Drive returns records using MDAC 2.0 but not MDAC 2.1 SP 2

8. Not returning rs from sp!

9. recordset from Oracle sp w/ ADO

10. ADO SP ReturnValue Params not working and problems with Command.Append

11. Creating ADO recordset w/o database .Append not working correctly IIS5 win2000 Prof not .Net

12. Dynamic ADO Recordset Not Dynamic (Does Not Refresh Data)

 

 
Powered by phpBB® Forum Software