output parameter

SKhna · February 25th, 2008, 06:06 AM

Hi Could someone tell me why i always get value zero in the variable totalRecords ?

  Public Function BindGvData(ByVal iPage As Integer, ByVal iRecordsPerPage As Integer, ByVal iCategoryName As String, ByVal Year As Integer, ByVal Month As Integer)

        Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrin gs("News").ConnectionString)
        Dim cmd As SqlCommand = New SqlCommand("LGFGNews.dbo.Usp_NewsListingPaging", con)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add(New SqlParameter("@iPage", SqlDbType.SmallInt)).Value = iPage
        cmd.Parameters.Add(New SqlParameter("@iRecsPerPage", SqlDbType.TinyInt)).Value = iRecordsPerPage 'Set the GVPaging size here
        cmd.Parameters.Add(New SqlParameter("@iCategoryName", SqlDbType.VarChar)).Value = iCategoryName
        cmd.Parameters.Add(New SqlParameter("@iYear", SqlDbType.Int)).Value = Year
        cmd.Parameters.Add(New SqlParameter("@iMonth", SqlDbType.Int)).Value = Month

        cmd.Parameters.Add(New SqlParameter("@bDesc", SqlDbType.Bit)).Value = True

        cmd.Parameters.Add(New SqlParameter("@iNoRecordsReturned", SqlDbType.Int)).Direction = ParameterDirection.Output
        cmd.Parameters.Add(New SqlParameter("@iMoreRecords", SqlDbType.Int)).Direction = ParameterDirection.Output
        cmd.Parameters.Add(New SqlParameter("@iCountAllNewsInCategory", SqlDbType.Int)).Direction = ParameterDirection.Output

        con.Open()

        Dim dr As SqlDataReader = cmd.ExecuteReader()
        Dim totalRecords As Integer = CType(cmd.Parameters("@iCountAllNewsInCategory").V alue, Integer)

        Return dr
        con.Close()

urtrivedi · February 25th, 2008, 06:51 AM

Check whether your stored procedure is giving proper result in sql analyser.

urt

Help yourself by helping someone.

SKhna · February 25th, 2008, 06:53 AM

yes it does :

Code:


CREATE PROCEDURE Usp_NewsListingPaging

    @iPage Smallint,
    @iRecsPerPage tinyint,
    @iCategoryName varchar(20),
    @iYear int,
    @iMonth int,
    @bDesc bit,  --Descending or not
    @iNoRecordsReturned int OUTPUT,
    @iMoreRecords int OUTPUT,
    @iCountAllNewsInCategory int OUTPUT

/*
    Created:    Sheraz Khan
    Purpose:    Paging for News Section
    Used By:    Bounty News Paging


*/

AS

DECLARE @SkipRows int
DECLARE @LastArticleID int

SET NOCOUNT ON


SELECT @iCountAllNewsInCategory = Count(ArticleID)
FROM dbo.newsArticle as na, dbo.categories as c, dbo.NewsArticleCategories as nac
where c.CategoryName=@iCategoryName 
AND c.CategoryID= nac.CategoryId 
AND nac.NewsArticleID=na.NewsArticleId


SELECT @iMoreRecords = @iCountAllNewsInCategory - @iRecsPerPage * (@iPage - 1) 
--print  @iMoreRecords
IF @iPage > 1
  BEGIN 
        --For pages > 1 compute how many records to ignore, set ROWCOUNT and SELECT ID into @LastID
        SET @SkipRows = @iRecsPerPage * (@iPage - 1)
        SET ROWCOUNT @SkipRows 

    If @bDesc=0
    BEGIN

    SELECT @LastArticleID=ArticleID
    FROM dbo.NewsArticle na, dbo.Categories c, dbo.NewsArticleCategories nac
    WHERE c.CategoryName=@iCategoryName
    AND c.CategoryID=nac.CategoryID 
    AND nac.NewsArticleID=na.NewsArticleId
    ORDER BY na.ArticleId

        --Set rowcount to @PageSize and SELECT page for output (note the WHERE clause)
        SET ROWCOUNT @iRecsPerPage 

        SELECT  na.NewsArticleID as NewsArticleID, na.ArticleID as ArticleID, na.Heading as Heading, na.HeadingCon as HeadingCon, convert(varchar(10),na.ArticleDate,101) as ArticleDate,@iMoreRecords as Returning
        FROM dbo.Categories c  WITH (NOLOCK) 
        INNER JOIN dbo.NewsArticleCategories nac ON c.CategoryID = nac.CategoryID
        INNER JOIN dbo.NewsArticle na ON nac.NewsArticleID = na.NewsArticleID
        WHERE UPPER(c.CategoryName) = UPPER(@iCategoryName)
        AND Year(na.ArticleDate)= @iYear
        ANd Month(na.ArticleDate)=@iMonth
        AND na.ActiveFlag = 1
        AND ArticleID>@LastArticleId
        ORDER BY ArticleID 


    END
    ELSE 
    BEGIN

    SELECT @LastArticleID=ArticleID
    FROM dbo.NewsArticle na, dbo.Categories c, dbo.NewsArticleCategories nac
    WHERE c.CategoryName=@iCategoryName
    AND c.CategoryID=nac.CategoryID 
    AND nac.NewsArticleID=na.NewsArticleID
    ORDER BY na.ArticleId DESC


        --Set rowcount to @PageSize and SELECT page for output (note the WHERE clause)
        SET ROWCOUNT @iRecsPerPage 

        SELECT  na.NewsArticleID as NewsArticleID, na.ArticleID as ArticleID, na.Heading as Heading,na.HeadingCon as HeadingCon, convert(varchar(10),na.ArticleDate,101) as ArticleDate,@iMoreRecords as Returning
        FROM dbo.Categories c  WITH (NOLOCK) 
        INNER JOIN dbo.NewsArticleCategories nac ON c.CategoryID = nac.CategoryID
        INNER JOIN dbo.NewsArticle na ON nac.NewsArticleID = na.NewsArticleID
        WHERE UPPER(c.CategoryName) = UPPER(@iCategoryName)
        AND Year(na.ArticleDate)= @iYear
        ANd Month(na.ArticleDate)=@iMonth
        AND na.ActiveFlag = 1
        AND ArticleID<@LastArticleId
        ORDER BY ArticleID desc
    END   

        SELECT @iNoRecordsReturned = @@ROWCOUNT
    SET ROWCOUNT 0
  END
ELSE
  BEGIN
        SET ROWCOUNT @iRecsPerPage
    IF @bDesc = 0
    BEGIN
            /* For page #1 just set rowcount to pagesize */ 
        SELECT  na.NewsArticleID as NewsArticleID, na.ArticleID as ArticleID, na.Heading as Heading, na.HeadingCon as HeadingCon, convert(varchar(10),na.ArticleDate,101) as ArticleDate,@iMoreRecords as Returning
        FROM dbo.Categories c  WITH (NOLOCK) 
        INNER JOIN dbo.NewsArticleCategories nac ON c.CategoryID = nac.CategoryID
        INNER JOIN dbo.NewsArticle na ON nac.NewsArticleID = na.NewsArticleID
        WHERE UPPER(c.CategoryName) = UPPER(@iCategoryName)
        AND Year(na.ArticleDate)= @iYear
        ANd Month(na.ArticleDate)=@iMonth
        AND na.ActiveFlag = 1
        ORDER BY ArticleID 
    END
    ELSE
    BEGIN
        SELECT  na.NewsArticleID as NewsArticleID, na.ArticleID as ArticleID, na.Heading as Heading,na.HeadingCon as HeadingCon, convert(varchar(10),na.ArticleDate,101) as ArticleDate,@iMoreRecords as Returning
        FROM dbo.Categories c  WITH (NOLOCK) 
        INNER JOIN dbo.NewsArticleCategories nac ON c.CategoryID = nac.CategoryID
        INNER JOIN dbo.NewsArticle na ON nac.NewsArticleID = na.NewsArticleID
        WHERE UPPER(c.CategoryName) = UPPER(@iCategoryName)
        AND Year(na.ArticleDate)= @iYear
        ANd Month(na.ArticleDate)=@iMonth
        AND na.ActiveFlag = 1
        ORDER BY ArticleID desc

    END

    SELECT @iNoRecordsReturned = @@ROWCOUNT

    SET ROWCOUNT 0
  END
-- set @iCountAllNewsInCategory=5
--print @iCountAllNewsInCategory
GO

planoie · February 25th, 2008, 10:58 AM

But have you actually TESTED the SPROC to see if it is doing what you expect it to?

-Peter
peterlanoie.blog