locked
Error using output parameter RRS feed

  • Question

  • User-1988524439 posted

    In my asp.net page I am getting the error below and not sure why.

    An SqlParameter with ParameterName 'newReturnID' is not contained by this SqlParameterCollection.

    Below is in my parameters collection.

    <asp:Parameter Name="newReturnID" Type="Int32" Direction="Output" />

    'stored procedure has parameter below

    @newReturnID int output

    INSERT ......

    SET @newReturnID = SCOPE_IDENTITY();

    SELECT @newReturnID AS newReturnID;

    Monday, November 18, 2013 5:38 PM

Answers

  • User2103319870 posted

    Hi,

    Please try with the below code

    Protected Sub SqlPartReturn_Inserted(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlPartReturn.Inserted
            Dim intnewReturnID As Int32 = Convert.ToInt32(e.Command.Parameters("@newReturnID").Value)
            txtReturnID.Text = intnewReturnID.ToString
        End Sub



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 18, 2013 7:45 PM

All replies

  • User2103319870 posted

    Hi,

    Try changing your query like given below

    Declare @newReturnID int output
    
    INSERT ......
    
    SELECT @newReturnID = SCOPE_IDENTITY();



    Monday, November 18, 2013 6:27 PM
  • User-1988524439 posted

    Got the same error.

    Also, I am using the datasource INSERTED event.

    Monday, November 18, 2013 6:52 PM
  • User2103319870 posted

    Could you please post your compelete mark up?

    Monday, November 18, 2013 7:03 PM
  • User-1988524439 posted
    ALTER PROCEDURE [dbo].[ms_insPartReturns]
    	(@LineID 			int,
    	 @ReturnedBy		varchar(8),
    	 @DamageDescription	varchar(1000) = null,
    	 @PartNumber		varchar(30) = null,
    	 @PartCost			smallmoney = 0,
    	 @VendorInvoiceNo	varchar(20) = null,
    	 @VendorListPrice	smallmoney = 0,
    	 @VendorID			int = 0,
    	 @ProfitCenterID	int = 0,
    	 @InvoiceDate		smalldatetime = null,
    	 @RecordID			int = 0,
    	 @ReasonID			int = 0,
    	 @Reason			varchar(50) = null,
    	 @ReasonNotes		varchar(250) = null,
    	 @ApprovalPerson	varchar(100) = null,
    	 @newReturnID		int output
    	) 
    	 
    AS 
    
    	BEGIN
    	
    		DECLARE @RepairOrderID int;
    		DECLARE @InvoiceNumber varchar(20);
    		DECLARE @WhoPays char(1);
    		DECLARE @VendorName varchar(35);
    		
    		BEGIN TRAN T1;
    		
    		IF @LineID = 0
    			BEGIN
    			
    				SELECT @WhoPays = PayorIs 
    				  FROM dbo.RepairOrder 
    				 WHERE (RecordID = @RecordID);
    				 
    				SELECT @VendorName = VendorName, 
    					   @ProfitCenterID = CASE WHEN @ProfitCenterID > 0 THEN @ProfitCenterID
    											  ELSE ProfitCenterID
    											  END
    				  FROM dbo.Vendors 
    				 WHERE (VendorID = @VendorID);
    				
    				INSERT INTO dbo.RepairOrderLines
    					(RecordID
    					,DamageDescription
    					,PartNumber
    					,PriceProfitCenterID
    					,SellingPrice
    					,VendorListPrice
    					,PartCost
    					,VendorID
    					,VendorName
    					,VendorInvoiceNo
    					,PartReceiveDate
    					,PartStatus
    					,PartInvoiced
    					,WhoPays
    					,FinalBill
    					)
    				VALUES
    					(@RecordID
    					,@DamageDescription
    					,@PartNumber
    					,@ProfitCenterID
    					,CASE WHEN @VendorListPrice IS NULL THEN 0
    						  ELSE @VendorListPrice
    						  END
    					,CASE WHEN @VendorListPrice IS NULL THEN 0
    						  ELSE @VendorListPrice
    						  END
    					,@PartCost
    					,@VendorID
    					,@VendorName
    					,@VendorInvoiceNo
    					,@InvoiceDate
    					,5
    					,NULL
    					,@WhoPays
    					,CASE WHEN @VendorListPrice IS NULL THEN 0
    						  ELSE 1
    						  END
    					)
    					
    				SET @LineID = SCOPE_IDENTITY();
    			END
    			
    				
    		SELECT @RecordID = R.RecordID, 
    			   @RepairOrderID = R.RepairOrderID, 
    			   @VendorID = L.VendorID,
    			   @InvoiceNumber = L.VendorInvoiceNo
    		  FROM dbo.RepairOrder AS R INNER JOIN
    			   dbo.RepairOrderLines AS L ON R.RecordID = L.RecordID
    		 WHERE (L.LineID = @LineID);
    	
    		INSERT INTO dbo.PartReturns
    			   ([LineID]
    			   ,[ReasonID]
    			   ,[ReasonNotes]
    			   ,[DamageDescription]
    			   ,[PartNumber]
    			   ,[PartCost]
    			   ,[VendorListPrice]
    			   ,[VendorID]
    			   ,[VendorInvoiceNo]
    			   ,[RepairOrderID]
    			   ,[InvoiceID]
    			   ,[ReturnedBy])
    		SELECT [LineID]
    			  ,@ReasonID
    			  ,@ReasonNotes
    			  ,[DamageDescription]
    			  ,[PartNumber]
    			  ,[PartCost]
    			  ,[VendorListPrice]
    			  ,[VendorID]
    			  ,[VendorInvoiceNo]
    			  ,@RepairOrderID
    			  ,0
    			  ,@ReturnedBy
    	     FROM dbo.RepairOrderLines
    	    WHERE (LineID = @LineID);
    
    		SET @newReturnID = SCOPE_IDENTITY();
    		
    		--create RepairOrderInvoices row for this credit and return new InvoiceID
    		DECLARE @newInvoiceID int;
    		SET @newInvoiceID = 0;
    		SET @PartCost = @PartCost * -1.0;
    		EXEC dbo.ms_insRepairOrderInvoicesCr @RepairOrderID, @InvoiceNumber, @InvoiceDate, @VendorID, @VendorName, @PartCost, @VendorListPrice, 1, @RecordID, 1, @ProfitCenterID, @newInvoiceID OUTPUT;
    		
    		--Update the PartReturns row with InvoiceID created
    		UPDATE dbo.PartReturns 
    		   SET InvoiceID = @newInvoiceID
    		 WHERE (ReturnID = @newReturnID);
    	  
    	  COMMIT TRAN T1;
    	  
    	  SELECT @newReturnID = SCOPE_IDENTITY();

    Below is the SqlDataSource parameters

            <InsertParameters>
                <asp:ControlParameter Name="LineID" ControlID="txtLineID" PropertyName="Text" Type="Int32" />
                <asp:CookieParameter Name="ReturnedBy" CookieName="myeecode" Type="String" />
                <asp:Parameter Name="DamageDescription" Type="String" />
                <asp:Parameter Name="PartNumber" Type="String" />
                <asp:Parameter Name="PartCost" Type="Decimal" />
                <asp:Parameter Name="VendorInvoiceNo" Type="String" />
                <asp:Parameter Name="VendorListPrice" Type="Decimal" />
                <asp:Parameter Name="VendorID" Type="Int32" />
                <asp:Parameter Name="ProfitCenterID" Type="Int32" />
                <asp:Parameter Name="InvoiceDate" Type="DateTime" />
                <asp:ControlParameter Name="RecordID" ControlID="txtRecordID" PropertyName="Text" Type="Int32" />
                <asp:Parameter Name="ReasonID" Type="Int32" />
                <asp:Parameter Name="Reason" Type="String" />
                <asp:Parameter Name="ReasonNotes" Type="String" />
                <asp:Parameter Name="ApprovalPerson" Type="String" />
                <asp:Parameter Name="newReturnID" Type="Int32" Direction="Output" />
            </InsertParameters>

    Below is the inserted event

        Protected Sub SqlPartReturn_Inserted(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlPartReturn.Inserted
            Dim intnewReturnID As Int32 = Convert.ToInt32(e.Command.Parameters("newReturnID").Value)
            txtReturnID.Text = intnewReturnID.ToString
        End Sub

    The error happens on the Dim line


    Monday, November 18, 2013 7:27 PM
  • User2103319870 posted

    Hi,

    Please try with the below code

    Protected Sub SqlPartReturn_Inserted(sender As Object, e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlPartReturn.Inserted
            Dim intnewReturnID As Int32 = Convert.ToInt32(e.Command.Parameters("@newReturnID").Value)
            txtReturnID.Text = intnewReturnID.ToString
        End Sub



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 18, 2013 7:45 PM