I don't want to mess any of your debate here. But why don't you use the
@@IDENTITY function. Check out the stored procedure below...
Especially this part.
-----------------------------------------------------------------------
-- Capture essential data about the insert event before it goes away --
-----------------------------------------------------------------------
SELECT
@intRowCount = @@ROWCOUNT,
@intErrorBuffer = @@ERROR,
@intPackageID = @@IDENTITY
END
---
--Procedure: sp_AddPackage
--
--Purpose: This procedure adds a new record to the packages table
--
--
--ReturnStatus Codes:
--
-- 0 Successful completion
-- 1 Errors inserting new tblPackages record
--
--
-- VERSION/DATE PROGRAMMER DESCRIPTION OF WORK
ACCOMPLISHED
--
--------------- --------------
----------------------------------------------------
--9-16-2002 Eric Cantrall Initital
Creation
--
CREATE PROCEDURE sp_AddPackage
@intShipmentID INTEGER = NULL,
@strTrackingNum VARCHAR(25) = NULL,
@dblWeight FLOAT = NULL,
@strUCCNo VARCHAR(25) = NULL,
@intPackageNo INTEGER = NULL,
@dblRatedWgt FLOAT = NULL,
@dblShipRate FLOAT = NULL,
@dblMiscChgs FLOAT = NULL,
@dblTotalChgs FLOAT = NULL,
@dblFuelSurChg FLOAT = NULL,
@dblResSurChg FLOAT = NULL,
@dblDelConfChg FLOAT = NULL,
@dblCODValue FLOAT = NULL,
@dblCODChg FLOAT = NULL,
@dblInsValue FLOAT = NULL,
@dblInsChg FLOAT = NULL,
@dblSatDelChg FLOAT = NULL,
@dblSatPickupChg FLOAT = NULL,
@dblAddHandlingChg FLOAT = NULL,
@dblHandlingChg FLOAT = NULL,
@dblHazMatChg FLOAT = NULL,
@dblCallTagChg FLOAT = NULL,
@blnOversize1 INTEGER = NULL,
@blnOversize2 INTEGER = NULL,
@blnDimWgt INTEGER = NULL,
@strChargeType VARCHAR(16) = NULL,
@strChgAcctNum VARCHAR(16) = NULL,
@intSuccessCode INTEGER = 51112,
@intPackageID INTEGER OUTPUT,
@RetValue INTEGER OUTPUT,
@intFailureCode INTEGER = 51113,
@blnDebugMode BIT = 0
AS
SET NOCOUNT ON
---------------------------------
-- Local variable declarations --
---------------------------------
DECLARE @strMessage NVARCHAR(440)
DECLARE @intRowCount INTEGER
DECLARE @intErrorBuffer INTEGER
DECLARE @intReturnValue INTEGER
DECLARE @intErrorCode INTEGER
------------------------------
-- Initialization and Setup --
------------------------------
SELECT @intErrorBuffer = 0
SELECT @intReturnValue = 0
SELECT @strMessage = ""
SELECT @RetValue = 1
----------------------------------------------------
-- Insert a new record
----------------------------------------------------
BEGIN
INSERT tblPackages(
intShipmentID,
strTrackingNum,
dblWeight,
strUCCNo,
intPackageNo,
dblRatedWgt,
dblShipRate,
dblMiscChgs,
dblTotalChgs,
dblFuelSurChg,
dblResSurChg,
dblDelConfChg,
dblCODValue,
dblCODChg,
dblInsValue,
dblInsChg,
dblSatDelChg,
dblSatPickupChg,
dblAddHandlingChg,
dblHandlingChg,
dblHazMatChg,
dblCallTagChg,
blnOversize1,
blnOversize2,
blnDimWgt,
strChargeType,
strChgAcctNum)
SELECT
intShipmentID = @intShipmentID,
strTrackingNum = @strTrackingNum,
dblWeight = @dblWeight,
strUCCNo = @strUCCNo,
intPackageNo = @intPackageNo,
dblRatedWgt = @dblRatedWgt,
dblShipRate = @dblShipRate,
dblMiscChgs = @dblMiscChgs,
dblTotalChgs = @dblTotalChgs,
dblFuelSurChg = @dblFuelSurChg,
dblResSurChg = @dblResSurChg,
dblDelConfChg = @dblDelConfChg,
dblCODValue = @dblCODValue,
dblCODChg = @dblCODChg,
dblInsValue = @dblInsValue,
dblInsChg = @dblInsChg,
dblSatDelChg = @dblSatDelChg,
dblSatPickupChg = @dblSatPickupChg,
dblAddHandlingChg = @dblAddHandlingChg,
dblHandlingChg = @dblHandlingChg,
dblHazMatChg = @dblhazMatChg,
dblCallTagChg = @dblCallTagChg,
blnOversize1 = @blnOversize1,
blnOversize2 = @blnOversize2,
blnDimWgt = @blnDimWgt,
strChargeType = @strChargeType,
strChgAcctNum = @strChgAcctNum
-----------------------------------------------------------------------
-- Capture essential data about the insert event before it goes away --
-----------------------------------------------------------------------
SELECT
@intRowCount = @@ROWCOUNT,
@intErrorBuffer = @@ERROR,
@intPackageID = @@IDENTITY
END
----------------------------------------------------------
-- Determine success / failure of insert record attempt --
----------------------------------------------------------
IF ((@intRowCount = 1) AND (@intErrorBuffer = 0))
BEGIN
SELECT @strMessage = @strMessage + CHAR(13) + CHAR(10) + CHAR(9)
+ 'Inserted Package (A:'
+ ISNULL(@strTrackingNum, 'NULL')
+ ')'
END -----------------------------------------
ELSE -- Errors Trying to insert Package row --
BEGIN -----------------------------------------
SELECT @RetValue = -20000
SELECT @intReturnValue = (@intReturnValue | 1)
SELECT @strMessage = @strMessage + CHAR(13) + CHAR(10) + CHAR(9)
+ 'Errors inserting Package (A:'
+ ISNULL(@strTrackingNum, 'NULL')
+ ')'
END
-------------------------------------------------------------
-------------------------------------------------------------------
-- Determine whether to pass a success or failure raiserror code --
------------------------------------------------------------------
SELECT @intErrorCode = CASE WHEN (@intReturnValue = 0)
THEN @intSuccessCode
ELSE @intFailureCode
END
------------------------------------------------------
-- Return Success / Failure Knowledge to the Caller --
------------------------------------------------------
RETURN(@intReturnValue)
GO
-----Original Message-----
From: Eliezer Broder [mailto:[EMAIL PROTECTED]
Sent: Friday, February 04, 2005 1:33 PM
To: [email protected]
Subject: Re: [AspNetAnyQuestionIsOk] SQL error: "Procedure 'Requistions_add'
expects parameter '@RequistionId', which was not supplied."
Kiran,
Almost... The problem is that my DBA won't let me leave that little
SELECT-statement in the SP. He wants my VB.NET code to retrieve the
Output parameter *without* using a SELECT-statement. And on that, I
ask, how should I do that?
Thanks so much (again)!
Eliezer
On Fri, 4 Feb 2005 13:07:17 -0500, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> 1)
> In your case you do not need the output parameter.
>
> You can modify the procedure as
> CREATE procedure Requistions_add
> ( @CreatedTs datetime
> , @PersonId_Creator smallint
> .....
> )
> ...
> select @RequistionId --this line added by Eliezer to get it to run with
> ExecuteScalar
>
> The final select statement will take care of retrieving the scalar
> value.
>
> Remember to remove your parameter object too when you remove the output
> parameter in stored procedure.
>
> Just have the return value stored in a variable. Reducing number of
> objects being used will improve the performance.
>
> 2) Output parameters are generally used in conjunction with DataReaders.
> Also note that you can read values from your output parameters only
> after your datareader object is closed.
>
> Hope this helps.
>
> Kiran.
>
>
> -----Original Message-----
> From: Eliezer Broder [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 04, 2005 10:49 AM
> To: [email protected]
> Subject: Re: [AspNetAnyQuestionIsOk] SQL error: "Procedure
> 'Requistions_add' expects parameter '@RequistionId', which was not
> supplied."
>
> Kiran,
>
> You were correct! Thank you. That solved one problem. After fixing
> that, it compiled and ran. But the only other problem is that it
> returns only "0". I added a Select at the end of the SP to get it to
> return a real value - and it does. But the DBA says that's not how an
> output parameter should be used. Here's the full code of the SP:
>
> CREATE procedure Requistions_add
> ( @RequistionId int OUTPUT
> , @CreatedTs datetime
> , @PersonId_Creator smallint
> , @OrganizationId_Charge smallint
> , @OrganizationId_Install smallint
> , @FacilityId_Install smallint
> , @BusinessNeedDescr varchar( 255 )
> , @ContactLastName varchar( 255 )
> , @ContactFirstName varchar( 255 )
> , @Phone_Contact varchar( 255 )
> , @Email_Contact varchar( 255 )
> , @PCScope varchar( 255 )
> , @PCName varchar( 255 )
> , @HelpDeskTicket varchar( 255 )
> , @InstallationInstructions text
> )
> as set nocount on set xact_abort on
>
> if @CreatedTs is null
> set @CreatedTs = current_timestamp
>
> Insert into dbo.Requistions
> ( CreatedTs
> , PersonId_Creator
> , OrganizationId_Charge
> , OrganizationId_Install
> , FacilityId_Install
> , BusinessNeedDescr
> , ContactFirstName , ContactLastName
> , Phone_Contact
> , Email_Contact
> , PCScope
> , PCName
> , HelpDeskTicket
> , InstallationInstructions
> )
> values
> ( @CreatedTs
> , @PersonId_Creator
> , @OrganizationId_Charge
> , @OrganizationId_Install
> , @FacilityId_Install
> , @BusinessNeedDescr
> , @ContactFirstName , @ContactLastName
> , @Phone_Contact
> , @Email_Contact
> , @PCScope
> , @PCName
> , @HelpDeskTicket
> , @InstallationInstructions
> )
>
> set @RequistionId = scope_identity()
>
> select @RequistionId --this line added by Eliezer to get it to run with
> ExecuteScalar
>
> return 0
> GO
>
> WITH that Select at the end, my ExecuteScalar works; without it, I
> always get a "0" value from my ExecuteScalar.
>
> What is, then, the proper way to get the value of an output parameter?
>
> Thanks very much!
> Eliezer
>
> On Thu, 3 Feb 2005 17:08:44 -0500, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
> >
> > You need to change the
> > paramNewReqID.Direction = ParameterDirection.ReturnValue
> >
> > to
> > paramNewReqID.Direction = ParameterDirection.Output
> >
> > since you declared it as an output parameter in stored procedure.
> >
> > Kiran.
> >
> > -----Original Message-----
> > From: Eliezer Broder [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, February 03, 2005 5:03 PM
> > To: [email protected]
> > Subject: [AspNetAnyQuestionIsOk] SQL error: "Procedure
> > 'Requistions_add' expects parameter '@RequistionId', which was not
> > supplied."
> >
> > I've got a SQL Server 2000 stored procedure called 'Requistions_add'
> > that takes a whole bunch of input parameters and one output parameter:
> >
> > CREATE procedure Requistions_add
> > ( @RequistionId int OUTPUT
> > , @CreatedTs datetime
> > , @PersonId_Creator smallint
> > , @OrganizationId_Charge smallint
> > , @OrganizationId_Install smallint
> > , @FacilityId_Install smallint
> > , @BusinessNeedDescr varchar( 255 )
> > , @ContactLastName varchar( 255 )
> > , @ContactFirstName varchar( 255 )
> > , @Phone_Contact varchar( 255 )
> > , @Email_Contact varchar( 255 )
> > , @PCScope varchar( 255 )
> > , @PCName varchar( 255 )
> > , @HelpDeskTicket varchar( 255 )
> > , @InstallationInstructions text
> > )
> >
> > [ etc ...]
> >
> > The VB.NET code looks like this:
> >
> > Dim cmSaveReq As New SqlCommand
> > cmSaveReq.Connection = cnDeliveryAndInstallation
> > cmSaveReq.CommandType = CommandType.StoredProcedure
> > cmSaveReq.CommandText = "dbo.Requistions_add"
> >
> > 'set up output parameter
> > Dim paramNewReqID As New SqlParameter
> > paramNewReqID.ParameterName = "@RequistionId"
> > paramNewReqID.SqlDbType = SqlDbType.Int
> > paramNewReqID.Direction = ParameterDirection.ReturnValue
> > cmSaveReq.Parameters.Add(paramNewReqID)
> >
> > cmSaveReq.Parameters.Add("@CreatedTs", Now)
> > cmSaveReq.Parameters.Add("@PersonId_Creator",
> personID.Text)
> > cmSaveReq.Parameters.Add("@OrganizationId_Charge",
> > costCenterDeptCharged.Text)
> >
> > [ some more input params added ]
> >
> > If cnDeliveryAndInstallation.State = ConnectionState.Closed
>
> > Then
> > cnDeliveryAndInstallation.Open()
> > Dim intNewReqID As Integer = cmSaveReq.ExecuteScalar
> >
> > On that last line of ExecuteScalar - I get that error (see subject
> > line, please). What is going on here? I AM supplying it with the
> > parameter!
> >
> > Any help would be more than appreciated!
> >
> > Thanks,
> > Eliezer
>
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/>
* To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]
e>
* Your use of Yahoo! Groups is subject to the Yahoo! Terms of
<http://docs.yahoo.com/info/terms/> Service.
[Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
