locked
Stored Procedure returns wrong value ! RRS feed

  • Question

  • User1700504264 posted

    Hello

    I am using SQLDataSource to call a stored procedure in the insert command, it returns (-1) all the time although it is successfully inserted.

    This is the stored procedure code

    USE [VALUATOR]
    GO
    /****** Object:  StoredProcedure [dbo].[spNewActivity]    Script Date: 10/25/2018 10:03:34 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Ghassan Aljabiri
    -- Create date: 20-OCT-2018
    -- =============================================
    ALTER PROCEDURE [dbo].[spNewActivity] 
    	-- Add the parameters for the stored procedure here
    	@file bigint,
    	@section bigint,
    	@title nvarchar(max), 
    	@startdate date,
    	@enddate date,
    	@kpitype int,
    	@kpivalue int
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    
    	-- Check if Activity within File interval
    	DECLARE @fileStartDate date
    	SET @fileStartDate= (
    		SELECT [startDate] FROM [File] WHERE ID=@file
    	) 
    
    	DECLARE @fileEndDate date
    	SET @fileEndDate= (
    		SELECT [endDate] FROM [File] WHERE ID=@file
    	)
    
    	
    	IF @startdate < @fileStartDate
    		RETURN -2; -- Activity starts before the start of the file interval
    	IF @startdate > @fileEndDate
    		RETURN -3; -- Activity starts after the end of the file interval
    	IF @enddate < @fileStartDate
    		RETURN -4; -- Activity ends before the file starts
    	IF @enddate > @fileEndDate
    		RETURN -5; -- Activity ends after the end of the file interval
    
    
    	-- Create Activity
    	DECLARE @newActivityID bigint
    	INSERT INTO Activity (Section, title, startDate, endDate, KPIType, kpiValue, status) VALUES (@section,@title, @startdate, @enddate, @kpitype, @kpivalue, 0 )
    	SET @newActivityID=SCOPE_IDENTITY();
    
    
    	-- Read Record Frequency
    	DECLARE @type int
    	SET @type=(
    	SELECT f.[Type]
    		FROM [File] f WHERE f.[ID]=@file
    	)
    
    	-- Calculate daily expected achievment
    	DECLARE @dailyExpectedAchievment real
    	SET  @dailyExpectedAchievment= @kpivalue / (DATEDIFF(DAY,@startdate,@enddate))
    
    	-- Create a list of intervals
    	--DECLARE @intervals TABLE (intervalStartDate DATE, intervalEndDate Date)
    	DECLARE @intervalStart date = @startdate
    	DECLARE @intervalEnd date
    	--DECLARE @shouldLoop int =1
    	While(@intervalStart <= @enddate)
    		BEGIN
    			SET @intervalEnd= 
    			(CASE @type
    				WHEN 1 THEN
    					 @intervalStart	
    				WHEN 2 THEN
    					DATEADD(DAY, 7-(DATEPART(dw, @intervalStart)), @intervalStart)
    				WHEN 3 THEN
    					EOMONTH(@intervalStart)
    				WHEN 4 THEN
    					DATEADD(MONTH,2,EOMONTH(@intervalStart))
    				WHEN 5 THEN
    					DATEADD(MONTH,5,EOMONTH(@intervalStart))
    				WHEN 6 THEN
    					DATEADD(MONTH,11,EOMONTH(@intervalStart))
    				WHEN 7 THEN
    					 @fileEndDate
    			END)
    
    			-- Validate if intervalEnd exceeds Activity end or File End
    			IF @intervalEnd > @enddate
    				SET @intervalEnd = @enddate
    
    			-- ADD Record
    			INSERT INTO Record (Activity, intervalStart, intervalEnd, visits, expectedRecord) VALUES (@newActivityID, @intervalStart, @intervalEnd,0, DATEDIFF(DAY,@intervalStart,@intervalEnd)*@dailyExpectedAchievment)
    			-- move to the next
    			SET @intervalStart = DATEADD(DAY,1,@intervalEnd);
    		END
    	 
    	 RETURN @newActivityID
    
    	 End
    

    if I call it in the SSMS, it returns the correct value!

    USE [VALUATOR]
    GO
    
    DECLARE	@return_value int
    
    EXEC	@return_value = [dbo].[spNewActivity]
    		@file = 1,
    		@section = 1,
    		@title = 'Some good Activity title',
    		@startdate = '10-25-2018',
    		@enddate = '10-27-2018',
    		@kpitype = 1,
    		@kpivalue = 100
    
    SELECT	'Return Value' = @return_value
    
    GO
    

    this code returns the correct value (<g class="gr_ gr_428 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="428" data-gr-id="428">new</g> inserted record id)

    I call it in the ASP.net from using this code

    Protected Sub btnSaveActivity_Click(sender As Object, e As EventArgs) Handles btnSaveActivity.Click
            Try
                Dim result As Integer = Nothing
                result = ActivitySqlDataSource.Insert
                Select Case result
                    'Case -1
                    '    ltrlActivities.Text = "<div class='alert alert-warning alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>Sorry!</strong> Start date could not be After End date.</div>"
                    Case -2
                        ltrlActivities.Text = "<div class='alert alert-warning alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>Sorry!</strong> Start date could not be before File start date</div>"
                    Case -3
                        ltrlActivities.Text = "<div class='alert alert-warning alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>Sorry!</strong> Start date could not be after File end date</div>"
                    Case -4
                        ltrlActivities.Text = "<div class='alert alert-warning alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>Sorry!</strong> Activity could not start before the file starts.</div>"
                    Case -5
                        ltrlActivities.Text = "<div class='alert alert-warning alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>Sorry!</strong> Activity could not end after the file end</div>"
                    Case 1
                        ltrlActivities.Text = "<div class='alert alert-success alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>Success!</strong> Activity has been added.</div>"
                End Select
                rptrSections.DataBind()
            Catch ex As Exception
                ltrlActivities.Text = "<div class='alert alert-warning alert-dismissible' role='alert'><button type='button' class='close' data-dismiss='alert' aria-label='Close'><span aria-hidden='True'>&times;</span></button><strong>Sorry!</strong> Something went wrong.</div>"
            End Try
        End Sub

    But the value of the (result) variable is -1 although it successfully inserts the <g class="gr_ gr_323 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="323" data-gr-id="323">data !</g>

    Any help is appreciated

    Friday, October 26, 2018 10:42 AM

Answers

All replies