locked
Although the stored procedure returns the expected output, but the SqlDataSource reflects different value RRS feed

  • Question

  • User1700504264 posted

    Hello

    I am using this stored procedure to insert a new record (it validates the passed parameters and returns a value depending on it)

    USE [VALUATOR]
    GO
    /****** Object:  StoredProcedure [dbo].[spNewActivity]    Script Date: 12/05/2018 7:39:27 AM ******/
    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 = 1,
    	@kpivalue int = 100,
    	--@newActivityID bigint = NULL OUTPUT,
    	@result int OUTPUT
    	--@isContinoues int,
    	--@isAutoTarget 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
    	BEGIN
    		SELECT @result=-2
    		RETURN -2; -- Activity starts before the start of the file interval
    	END
    		
    	IF @startdate > @fileEndDate
    	BEGIN
    		SELECT @result=-3
    		RETURN -3; -- Activity starts after the end of the file interval
    	END
    		
    	IF @enddate < @fileStartDate
    	BEGIN
    		SELECT @result=-4
    		RETURN -4; -- Activity ends before the file starts
    	END
    		
    	IF @enddate > @fileEndDate
    	BEGIN
    		SELECT @result=-5
    		RETURN -5; -- Activity ends after the end of the file interval
    	END
    		
    	-- Create Activity
    
    	INSERT INTO Activity (Section, title, startDate, endDate, KPIType, kpiValue, [status], isContinoues, isAutoTarget) VALUES (@section,@title, @startdate, @enddate, @kpitype, @kpivalue, 0, 0, 0 )
    	SELECT @result=1;
    	
    End
    

    I executed it on <g class="gr_ gr_319 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="319" data-gr-id="319">SQL</g> server, it works fine and returns the correct result depending on passed parameters.

    then I used this code to execute it in the ASP.net web form

    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 Else
                        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>" & GetLocalResourceObject("success") & "</strong> " & GetLocalResourceObject("successdetails") & "</div>"
                        rptrSections.DataBind()
                        ClearForm()
    
                End Select
    
            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

    the expected value of the variable (result) may be (-2,-3,-4,-5, or 1) 

    but it is set to (-1) each time I run it

    Any help <g class="gr_ gr_883 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="883" data-gr-id="883">please</g>?

    Wednesday, December 5, 2018 4:58 AM

Answers

  • User-271186128 posted

    Hi Sir,

    Dim result As Integer = Nothing
                result = ActivitySqlDataSource.Insert
                Select Case result 

    The SqlDataSource.Insert Method returns a value that represents the number of rows inserted into the underlying database, instead of the output parameter.

    I suggest you could refer to Fei Han's reply on this thread to get the output parameter:

    Insert button event:

    Protected Sub btnCreateFile_Click(sender As Object, e As EventArgs)
        FilesSQLDataSource.InsertParameters.Clear()
        FilesSQLDataSource.InsertParameters.Add("name", txtTitle.Text)
        FilesSQLDataSource.InsertParameters.Add("startDate", txtStartDate.Text)
        FilesSQLDataSource.InsertParameters.Add("endDate", txtEndDate.Text)
        FilesSQLDataSource.InsertParameters.Add("type", ddlType.SelectedValue)
    
        Dim newFileIDParam As New Parameter("newFileID", DbType.Int16, "")
        newFileIDParam.Direction = ParameterDirection.Output
    
        FilesSQLDataSource.InsertParameters.Add(newFileIDParam)
    
        Dim result As Integer
        result = FilesSQLDataSource.Insert()
    End Sub

    In the SQLDataSource_Inserted event:

    Protected Sub FilesSQLDataSource_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs) Handles FilesSQLDataSource.Inserted
    
        'your code logic here
    
        Dim newFID As String = e.Command.Parameters("@newFileID").Value   'Get the output parameter
    
        ltrlFiles.Text = newFID
    End Sub

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 6, 2018 8:24 AM