using RDO 2.0 and VB5: Getting Data from temp tables created in stored procedure

using RDO 2.0 and VB5: Getting Data from temp tables created in stored procedure

Post by Rick Postm » Fri, 27 Mar 1998 04:00:00



SQL Server 6.5
RDO 2.0
VB 5

I am using a temp table in a stored procedure to build a result set.
Testing this stored procedure in a query window in SQL Server produces
the results that I expected.  When using RDO (Remote Data Object 2.0)
from within my VB application, calling this same stored procedure it
appears as though RDO does not return any results, only an empty result
set and no errors. I searched the microsoft web site for a solution and
I did find something(I found article id:Q147938). But the solution that
they provided does not solve my problem.

My stored procedure is as follows:
*******************************************************************************************

create procedure sp_get_mpp_link_package
-----------------------------------------------------------------
-- name: sp_get_mpp_link_package
-- purpose: called from a package object. it's purpose is to
--  retrieve move+package information for a package. in
--  other words, gather up info about all the moves that
--  a package is on, as well as information about any
--  packages that the package might be contained within.
-- author: rick postma
-- date: 03/24/98
-----------------------------------------------------------------
--start parameters

--end parameters
AS
BEGIN

--Declare Variables

--Create temp table that will be used to retain the sequence of the
move+package entity for the specified package
CREATE TABLE #MP_Sequence (
 sequence_num int IDENTITY NOT NULL ,
 mpp_link_id int NOT NULL )

--Get all the mpp_link_id values for the M+P entities in the specified
packages Move+Package structure/flow, in the order that they flow


 BEGIN


  FROM  mpp_link




  else

 END

--now that the order/flow of the M+P structure for this package has been
established, gather all
--the info needed to populate the grid on the track package dialog
SELECT  mp.move_id
 ,mp.package_id_parent
 ,m.description AS move_description
 ,m.comments
 ,m.date_start
 ,m.date_end
 ,m.voyage_flight_trailer
 ,p.package_num
 ,p.package_type_id
 ,p.description AS package_description
 ,p.status_id
FROM #MP_Sequence mps, mpp_link mp, move m, package p
WHERE mp.mpp_link_id = mps.mpp_link_id
AND m.move_id = mp.move_id
AND  p.package_id =* mp.package_id_parent
ORDER BY mps.sequence_num

DROP TABLE #MP_Sequence

END

GO
*******************************************************************************************

My VB Code is as follows:
*******************************************************************************************

Set qry = New rdoQuery
Set qry.ActiveConnection = g_rdo_Connection.ActiveConnection
qry.SQL = "{Call sp_get_mpp_link_package (?)}"
qry(0).Direction = rdParamInput
qry(0) = m_package_id
qry.RowsetSize = 1
Set rslt = qry.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

    Do Until rslt.EOF
        ReDim Preserve vStream(UBound(vStream) + 1)
        vStream(UBound(vStream)) = _
            Array(IIf(IsNull(rslt("Move_id").Value), 0,
rslt("Move_id").Value), _
                  IIf(IsNull(rslt("package_id_parent").Value), 0,
rslt("package_id_parent").Value), _
                  IIf(IsNull(rslt("move_description").Value), "",
rslt("move_description").Value), _
                  IIf(IsNull(rslt("comments").Value), "",
rslt("comments").Value), _
                  IIf(IsNull(rslt("date_start").Value), "",
rslt("date_start").Value), _
                  IIf(IsNull(rslt("date_end").Value), "",
rslt("date_end").Value), _
                  IIf(IsNull(rslt("voyage_flight_trailer").Value), "",
rslt("voyage_flight_trailer").Value), _
                  IIf(IsNull(rslt("Package_Num").Value), "",
rslt("Package_Num").Value), _
                  IIf(IsNull(rslt("Package_Type_id").Value), "",
rslt("Package_Type_id").Value), _
                  IIf(IsNull(rslt("Package_Description").Value), "",
rslt("Package_Description").Value), _
                  IIf(IsNull(rslt("Status_Id").Value), 0,
rslt("Status_Id").Value))
        rslt.MoveNext
    Loop

rslt.Close
Set rslt = Nothing
qry.Close
Set qry = Nothing
*******************************************************************************************

I open the result set with the following parameters "rdOpenForwardOnly,
rdConcurReadOnly" as suggested by microsoft, but my result set is still
empty. Any insight into this matter will be greatly appreciated.

Thanks in advance
Rick Postma

 
 
 

using RDO 2.0 and VB5: Getting Data from temp tables created in stored procedure

Post by Rick Postm » Sat, 28 Mar 1998 04:00:00


Found a solution.

 
 
 

using RDO 2.0 and VB5: Getting Data from temp tables created in stored procedure

Post by Sistemas LTS » Sat, 28 Mar 1998 04:00:00


Why don't you post your solution, it may be helpfull for other people to
see.


Quote:>Found a solution.

 
 
 

using RDO 2.0 and VB5: Getting Data from temp tables created in stored procedure

Post by Todd » Tue, 31 Mar 1998 04:00:00


Please post it...


>Found a solution.