locked
stored procedure executing successfully but no result in asp.net page RRS feed

  • Question

  • User157772347 posted

    the SP is running and showing the results but now i have some updates on this code in which i want to Declare 2 outputs @TotalActive INT OUTPUT and @TotalRefund INT OUTPUT but while adding those 2 in the declaration, the SP is executing without errors but the results not showing in the web page the page will be empty at all.

    USE [uriic2018_traveldev]
       GO
    
       SET ANSI_NULLS ON
       GO
       SET QUOTED_IDENTIFIER ON
       GO
       ALTER PROCEDURE [dbo].[spGetReportProducer_Pager]
       @ClientId bigint=null,
       @Fromdate nvarchar(max)=null,
       @Todate nvarchar(max)=null,
       @SortExpression NVARCHAR(MAX),
       @SortDirection NVARCHAR(MAX),
       @PageIndex INT = 1,
       @PageSize INT = 50,
       @RecordCount INT OUTPUT,
       @ActivePolicy INT OUTPUT,
       @CancelledPolicy INT OUTPUT,
       @TotalPolicy INT OUTPUT,
       @OriginalPremium DECIMAL OUTPUT,
       @UpdatedPremium DECIMAL OUTPUT
               AS
               BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY 
            CASE WHEN ( @SortExpression = 'PolicyNumber' AND @SortDirection='ASC')
                    THEN P.PolicyNumber
            END ASC,
            CASE WHEN ( @SortExpression = 'PolicyNumber' AND @SortDirection='DESC')
                    THEN P.PolicyNumber
            END DESC,
            CASE WHEN ( @SortExpression = 'Firstname' AND @SortDirection='ASC')
                    THEN P.FirstName
            END ASC,
            CASE WHEN ( @SortExpression = 'Firstname' AND @SortDirection='DESC')
                    THEN P.FirstName
            END DESC,
            CASE WHEN ( @SortExpression = 'Middlename' AND @SortDirection='ASC')
                    THEN P.MiddleName
            END ASC,
            CASE WHEN ( @SortExpression = 'Middlename' AND @SortDirection='DESC')
                    THEN P.MiddleName
            END DESC,
            CASE WHEN ( @SortExpression = 'Lastname' AND @SortDirection='ASC')
                    THEN P.LastName
            END ASC,
            CASE WHEN ( @SortExpression = 'Lastname' AND @SortDirection='DESC')
                    THEN P.LastName
            END DESC,
            CASE WHEN ( @SortExpression = 'OriginalPremium' AND @SortDirection='ASC')
                    THEN P.Premium
            END ASC,
            CASE WHEN ( @SortExpression = 'OriginalPremium' AND @SortDirection='DESC')
                    THEN P.Premium
            END DESC,
            CASE WHEN ( @SortExpression = 'UpdatedPremium' AND @SortDirection='ASC')
                    THEN P.TotalPremium
            END ASC,
            CASE WHEN ( @SortExpression = 'UpdatedPremium' AND @SortDirection='DESC')
                    THEN P.TotalPremium
            END DESC,
            CASE WHEN ( @SortExpression = 'IssuedDate' AND @SortDirection='ASC')
                    THEN P.IssuedDate
            END ASC,
            CASE WHEN ( @SortExpression = 'IssuedDate' AND @SortDirection='DESC')
                    THEN P.IssuedDate
            END DESC,
            CASE WHEN ( @SortExpression = 'EffectiveDate' AND @SortDirection='ASC')
                    THEN P.Effectivedate
            END ASC,
            CASE WHEN ( @SortExpression = 'EffectiveDate' AND @SortDirection='DESC')
                    THEN P.Effectivedate
            END DESC,
            CASE WHEN ( @SortExpression = 'ExpiryDate' AND @SortDirection='ASC')
                    THEN P.ExpiryDate
            END ASC,
            CASE WHEN ( @SortExpression = 'ExpiryDate' AND @SortDirection='DESC')
                    THEN P.ExpiryDate
            END DESC,
            CASE WHEN ( @SortExpression = 'Status' AND @SortDirection='ASC')
                    THEN P.[Status]
            END ASC,
            CASE WHEN ( @SortExpression = 'Status' AND @SortDirection='DESC')
                    THEN P.[Status]
            END DESC,
            CASE WHEN ( @SortExpression = 'Policy' AND @SortDirection='ASC')
                    THEN P.Policy
            END ASC,
            CASE WHEN ( @SortExpression = 'Policy' AND @SortDirection='DESC')
                    THEN P.Policy
            END DESC
      ) AS RowNumber
        ,P.PolicyId
        ,P.PolicyNumber
        ,P.FirstName
        ,P.MiddleName
        ,P.LastName
        ,P.Premium as OriginalPremium
        ,P.TotalPremium as UpdatedPremium
        ,format(P.IssuedDate, 'dd/MM/yyyy') as IssuedDate
        ,format(P.Effectivedate, 'dd/MM/yyyy') as Effectivedate
        ,format(P.ExpiryDate, 'dd/MM/yyyy') as ExpiryDate
        ,P.[Status]
        ,P.Policy
        ,C.CompanyName AS ProducerName
    
      INTO #Results
      FROM PolicyDetails P
      left join Clients C on C.ClientId=p.ClientId
      where  (@ClientId IS NULL OR P.ClientId=@ClientId)
        AND  (P.IssuedDate between @Fromdate and @Todate OR (@Fromdate='' OR @Todate=''))
    
    
    
      SELECT @RecordCount = COUNT(*)
      FROM #Results
    
      SELECT @ActivePolicy = COUNT(*)
      FROM #Results WHERE [OriginalPremium] > 0
    
      SELECT @CancelledPolicy = COUNT(*)
      FROM #Results WHERE [OriginalPremium] < 0
    
      Set @TotalPolicy = @ActivePolicy+@CancelledPolicy
    
      SELECT @OriginalPremium = SUM(OriginalPremium)
      FROM #Results
    
      SELECT @UpdatedPremium = SUM(UpdatedPremium)
      FROM #Results
    
      SELECT * FROM #Results
    
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    
      DROP TABLE #Results
         END

    Sunday, September 22, 2019 4:17 PM

All replies

  • User61956409 posted

    Hi ahmedsalahaddin,

    Declare 2 outputs @TotalActive INT OUTPUT and @TotalRefund INT OUTPUT but while adding those 2 in the declaration, the SP is executing without errors but the results not showing in the web page the page will be empty at all.

    Please execute your stored procedure "spGetReportProducer_Pager" on your SQL server and check if you can get expected value.

    DECLARE  @TotalActive INT
    DECLARE  @TotalRefund INT  
    --declare other variables
    
    --you may need provide other parameters
    EXEC spGetReportProducer_Pager @TotalActive OUTPUT, @TotalRefund OUTPUT
    PRINT @TotalActive;
    PRINT @TotalRefund

    Besides, in your ASP.NET application code behind, please check if your code look like below.

    using (SqlConnection conn = new SqlConnection(connstr))
    {
        SqlCommand comm = new SqlCommand("spGetReportProducer_Pager", conn);
        comm.CommandType = CommandType.StoredProcedure;
    
        //code logic here
        //add other required parameters
    
        comm.Parameters.Add("@TotalActive", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@TotalRefund", SqlDbType.Int).Direction = ParameterDirection.Output;
    
        conn.Open();
        comm.ExecuteNonQuery();
        var TotalActive = comm.Parameters["@TotalActive"].Value;
        var TotalRefund = comm.Parameters["@TotalRefund"].Value;
    
        conn.Close();
    }

    With Regards,

    Fei Han

    Monday, September 23, 2019 6:06 AM