Procedure or function sp_Procedure expects parameter @RecordCount which was not supplied

Question

I am using 3 tier architecture in c#. i have create database pagination. But I'm getting the error when accessing a Stored Procedure in SQL Server:

"Procedure or function sp_Procedure expects parameter @RecordCount which was not supplied."

ALTER PROCEDURE [dbo].[sp_Procedure]

@PageIndex INT = 1,
@PageSize INT = 10,
@RecordCount INT output,
@Mode varchar(25)
AS

IF (@Mode='DisplayDataPaging')
BEGIN
  SET NOCOUNT ON;
  SELECT ROW_NUMBER() OVER
  (
        ORDER BY [CustomerID] ASC
  )AS RowNumber
  ,[CustomerID]
  ,[CompanyName]
  ,[ContactName]
 INTO #Results
  FROM [Customers]

  SELECT @RecordCount = COUNT(*)
  FROM #Results

  SELECT * FROM #Results
  WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

  DROP TABLE #Results
END
GO

Business Logic class code :

public DataSet GetAllJobs(BussinessObj objBussiness)
                {
                    DataSet ds = new DataSet();
                    IDbCommand selectCommand = null;
                    SqlParameter[] param = new SqlParameter[4];
                    param[0] = new SqlParameter("@Mode", "DisplayDataPaging");
                    param[1] = new SqlParameter("@PageIndex", objBussiness.PageIndex);
                    param[2] = new SqlParameter("@PageSize", objBussiness.PageSize);
                    param[3] = new SqlParameter("@RecordCount", SqlDbType.Int, 4);
                    param[3].Direction = ParameterDirection.Output;
                    ds = objDataAccess.ExecuteDataset(_spName, param);
                    objBussiness.RecordCount = Convert.ToInt32((param[3]).Value);
                    return ds;
                }

aspx.cs file code :

BussinessObj objBussinessObj = new BussinessObj();
    BussinessLgc objBussinessLogic = new BussinessLgc();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.GetCustomersPageWise(1);
        }
    }

    protected void PageSize_Changed(object sender, EventArgs e)
    {
        this.GetCustomersPageWise(1);
    }

    private void GetCustomersPageWise(int pageIndex)
    {
        objBussinessObj.PageIndex = pageIndex;
        objBussinessObj.PageSize = int.Parse(ddlPageSize.SelectedValue);
        objBussinessLogic.GetAllJobs(objBussinessObj);            
        int recordCount = Convert.ToInt16(objBussinessObj.RecordCount.ToString());
        this.PopulatePager(recordCount, pageIndex);
    }

    protected void Page_Changed(object sender, EventArgs e)
    {
        int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
        this.GetCustomersPageWise(pageIndex);
    }

    private void PopulatePager(int recordCount, int currentPage)
    {
        double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
        int pageCount = (int)Math.Ceiling(dblPageCount);
        List<ListItem> pages = new List<ListItem>();
        if (pageCount > 0)
        {
            pages.Add(new ListItem("First", "1", currentPage > 1));
            for (int i = 1; i <= pageCount; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
            pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
        }
        rptPager.DataSource = pages;
        rptPager.DataBind();
    }

Obviously you don't supply the @RecordCount output parameter which is intended to contain result count. Post your C#. — AFract
If you don't show the code where you are calling the SP, how can we tell what is wrong with it, all we can really say is probably you are not passing the @RecordCount parameter. — Ben Robinson
What is the use of that line afterall? because mine, i tried to comment it out and the loading with paging still worked — rickyProgrammer

Casey Crookston Casey Crookston · Accepted Answer · 2014-12-03T14:03:49

As you are discovering, output parameters are hard to work with. (At least I think so.) From the looks of your stored proc, the only thing @RecordCount is doing is giving you a count of returned rows. There's a much easier way to do this.

From the stored proc, I would remove the parameter @RecordCount. Then in your code...

public DataSet GetAllJobs(BussinessObj objBussiness)
            {
                DataSet ds = new DataSet();
                IDbCommand selectCommand = null;
                SqlParameter[] param = new SqlParameter[4];
                param[0] = new SqlParameter("@Mode", "DisplayDataPaging");
                param[1] = new SqlParameter("@PageIndex", objBussiness.PageIndex);
                param[2] = new SqlParameter("@PageSize", objBussiness.PageSize);
                ds = objDataAccess.ExecuteDataset(_spName, param);
                objBussiness.RecordCount = ds.tables(0).rows.count;
                return ds;
            }

As I'm sure you know, a DataSet can contain an array of DataTables. Your stored proc only returns one record set, or, one table. So this gives you access to that table:

ds.tables(0)

This gives you access to all the rows in that table:

ds.tables(0).rows

And this gives you the number of rows in the table.

ds.tables(0).rows.count

Let me know if this works for you.

Thanks!

Procedure or function sp_Procedure expects parameter @RecordCount which was not supplied

2 Answers