I'm using VB6, SQL 2000, ADO 2.7 library, Win2000. I have code in a procedure that utilizes the CreateParameter, Append method of the command object to execute a stored proc. I have the parameter declared as OUPTUT in my stored proc.
 The procedure runs and inserts the record but my output parameter keeps coming back 0. :

    ---- some code here -----

    Set Param = .CreateParameter(mREQ_ID, 3, adParamOutput, 0, REQ_ID)
    .Parameters.Append Param
    Param.Value = REQ_ID

    ---- some code here -----

    Set rs = .Execute

    REQ_ID = Val("" & .Parameters(0).Value)

Can anyone assist?



ohiggs

You have to read the entire recordset and close it before the OUTPUT parameter value becomes available.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Well, the stored proc only inserts or updates. I don't read anything.

ohiggs

The stores proc is:

CREATE PROCEDURE [DBO].[PRC_WRITE_PURCHASEREQ]

    @mREQ_ID INT OUTPUT,
    @mAppl_ID INT,
    @mDept_ID INT,
    @mBldg_ID INT,
    @mTitle VARCHAR (50),
    @mFName VARCHAR (50),
    @mLName VARCHAR (50),
    @mWorkPhone VARCHAR (20),
    @mMailCode VARCHAR (10),
    @mRoomNo VARCHAR (10),
    @mPN_SW INT,
    @mPN_HW INT,
    @mPN_Other INT,
    @mPN_OtherDesc VARCHAR(150),
    @mDateNeeded VARCHAR (50),
    @mVendor_ID INT,
    @mProdType CHAR(20),
    @mCost_Center INT,
    @mDirector_ID INT

AS
    UPDATE TBL_PURCHASEREQUEST
    SET Appl_ID = @mAppl_ID,
            Dept_ID = @mDept_ID,
            Bldg_ID = @mDept_ID,
            Title = @mTitle,
            FName = @mFName,
            LName = @mLName,
            WorkPhone = @mWorkPhone,
            MailCode = @mMailCode,
            RoomNo = @mRoomNo,
            PN_SW = @mPN_SW,
            PN_HW = @mPN_HW,
            PN_Other = @mPN_Other,
            PN_OtherDesc = @mPN_OtherDesc,
            RequestDate = @mDateNeeded,
            Vendor_ID = @mVendor_ID,
            ProdType = @mProdType,
            Cost_Center = @mCost_Center,
            Director_ID = @mDirector_ID,
            UpdateDate = GetDate(),
            UpdateUser = SYSTEM_USER
    WHERE Req_ID = @mREQ_ID

    IF @@ROWCOUNT = 0

        BEGIN
            INSERT TBL_PURCHASEREQUEST(Appl_ID, Dept_ID,Bldg_ID,Title,FName,
                LName, WorkPhone, MailCode, RoomNo, PN_SW, PN_HW,PN_Other, PN_OtherDesc,
                RequestDate, Vendor_ID, ProdType, Cost_Center, Director_ID)

            VALUES(@mAppl_ID, @mDept_ID, @mBldg_ID, @mTitle, @mFName,
                @mLName, @mWorkPhone, @mMailCode, @mRoomNo, @mPN_SW, @mPN_HW, @mPN_Other, @mPN_OtherDesc,
                @mDateNeeded, @mVendor_ID, @mProdType, @mCost_Center, @mDirector_ID)

            SELECT @mREQ_ID = @@IDENTITY
        END

ohiggs

Problem Solved.

Thanks anyway.

ohiggs