• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

SQL Stored Procedure Output C#

I have a stored procedure that perform a query, and outputs a COUNT of the records.

When I run it in SQL Server Management Console; I get a @totalRows value of 32,545 count.

BUT, when I execute this in Asp.net C#, the value of @totalRows always comes back as 2, not 32,545...

Am I doing something wrong?
USE [database]
GO

DECLARE	@return_value int,
		@totalRows int

EXEC	@return_value = sp_PagingNewPubs
		@startRowIndex = 1,
		@maxRows = 6,
		@totalRows = @totalRows OUTPUT

SELECT	@totalRows as N'@totalRows'

C# Code
======
        SqlConnection sqlConn = new SqlConnection();
        sqlConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString.ToString();

        SqlCommand sqlCmd = new SqlCommand();
        sqlCmd.Connection = sqlConn;
        sqlCmd.CommandType = CommandType.StoredProcedure;
        sqlCmd.CommandText = "sp_PagingNewPubs";
        sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int).Value = GridPubs.PageIndex;
        sqlCmd.Parameters.Add("@maxRows", SqlDbType.Int).Value = GridPubs.PageSize;
        sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int).Value = ParameterDirection.Output;

        SqlDataAdapter sqlDad = new SqlDataAdapter();
        sqlDad.SelectCommand = sqlCmd;

        DataTable sqlDt = new DataTable();

        sqlDad.Fill(sqlDt);

        int totalPages = (int)sqlCmd.Parameters["@totalRows"].Value;

Open in new window

0
adworldmedia
Asked:
adworldmedia
  • 2
1 Solution
 
Jesus RodriguezIT ManagerCommented:
Check this link http://www.sqlteam.com/article/stored-procedures-returning-data
 on the section for output variables and try to check you code to

EXEC      @return_value = sp_PagingNewPubs
            @startRowIndex = 1,
            @maxRows = 6,
            totalRows = @totalRows OUTPUT

SELECT      totalRows = N'@totalRows'
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, try this:
SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString.ToString();

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "sp_PagingNewPubs";
sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int).Value = GridPubs.PageIndex;
sqlCmd.Parameters.Add("@maxRows", SqlDbType.Int).Value = GridPubs.PageSize;
SqlParameter paramTotalRows = sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int);
paramTotalRows.Direction = ParameterDirection.Output; 

SqlDataAdapter sqlDad = new SqlDataAdapter();
sqlDad.SelectCommand = sqlCmd;

DataTable sqlDt = new DataTable();

sqlDad.Fill(sqlDt);

int totalPages = Convert.ToInt32(paramTotalRows.Value);

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Explaining it:

In your code your @totalRows parameter has the value of 2 because it is never filled by your sp (you never specify the direction of your parameter), instead it is filled with 2 when you do this:
sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int).Value = ParameterDirection.Output;

Open in new window

That is because the integer value of ParameterDirection.Output is 2, you need to specify the direcction of your parameter by using the Direction property of your SqlParameter class, as in the next code:
SqlParameter paramTotalRows = sqlCmd.Parameters.Add("@totalRows", SqlDbType.Int);
paramTotalRows.Direction = ParameterDirection.Output;

Open in new window

Then, after execute your query, paramTotalRows.Value will contain the value assigned to @totalRows by your SP.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now