|
Subject:
|
SQL output parameters
|
|
Posted By:
|
ohiggs
|
Post Date:
|
2/9/2004 8:39:26 PM
|
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
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
2/9/2004 8:41:04 PM
|
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
|
|
Reply By:
|
ohiggs
|
Reply Date:
|
2/9/2004 8:45:18 PM
|
Well, the stored proc only inserts or updates. I don't read anything.
ohiggs
|
|
Reply By:
|
ohiggs
|
Reply Date:
|
2/9/2004 8:56:50 PM
|
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
|
|
Reply By:
|
ohiggs
|
Reply Date:
|
2/9/2004 9:09:51 PM
|
Problem Solved.
Thanks anyway.
ohiggs
|
