Paging query

I have created a stored proc for paging on a datalist which uses a objectDataSource.

I have a output param itemCount which should return the total rows. Them I am creating a temp table to fetch the records for each page request. My output param works fine if I comment out all the other select statements. But returns null with them. Any help would be appreciated.

CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]
(
@.categoryID int,
@.pageIndex INT,
@.numRows INT,
@.itemCount INT OUTPUT

)
AS

SELECT @.itemCount= COUNT(*) FROM CMRC_Products whereCMRC_Products.CategoryID=@.categoryID

Declare @.startRowIndex INT;
Declare @.finishRowIndex INT;
set @.startRowIndex = ((@.pageIndex -1) * @.numRows) + 1;
set @.finishRowIndex = @.pageIndex * @.numRows

DECLARE @.tCat TABLE (TID int identity(1,1),ProductID int, CategoryID int, SellerUserName varchar(100), ModelName varchar(100), Medium varchar(50),
ProductImage varchar(100),UnitCost money,Description varchar(1500), CategoryName varchar(100), isActive bit,weight money)

INSERT INTO @.tCat(ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight)
SELECT CMRC_Products.ProductID, CMRC_Products.CategoryID, CMRC_Products.SellerUserName, CMRC_Products.ModelName, CMRC_Products.Medium,CMRC_Products.ProductImage,
CMRC_Products.UnitCost, CMRC_Products.Description, CMRC_Categories.CategoryName, CMRC_Products.isActive,CMRC_Products.weight
FROM CMRC_Products INNER JOIN
CMRC_Categories ON CMRC_Products.CategoryID = CMRC_Categories.CategoryID
WHERE (CMRC_Products.CategoryID = @.categoryID) AND (CMRC_Products.isActive = 1)

SELECT ProductID, CategoryID,SellerUserName,ModelName,Medium,ProductImage,UnitCost,Description,CategoryName, isActive,weight
FROM @.tCat
WHERE TID >= @.startRowIndex AND TID <= @.finishRowIndex
GO

spawned:

My output param works fine if I comment out all the other select statements. But returns null with them.

Strange! Other select statements should not effect the output param as non of them refereneces the output param. How did you get the output param's value after executing the sp? Will the output param work fine if you directly execute the stored procedure in SQL via Query Analyzer (or Management Studio) without commenting out some statements?

|||

You are correct, I tested in Query Analyser and the Output was returned OK.

The issue was that the method was returning a DataReader along with a output param. The two don't get along! You have to close the DataReader before the output param is visible. This is 'by design' accourding to MS.http://support.microsoft.com/?id=308051 (See resolution section). Moving to the end of the recordset didn't seem to work but close it does.

This was a real pain as I had to change the method to Read the data into a list<Product> then close the Reader, then set reference to my output param.

Anyway all done now and works a treat. Thanks for you advice.

|||

Then how about returning the count as a result set instead of putting it in the output parameter? I mean you can write your stored procedure this way:

CREATE PROCEDURE [dbo].[CMRC_PRODUCTS_GetListByCategory]
(
@.categoryID int,
@.pageIndex INT,
@.numRows INT,
)
AS

SELECT COUNT(*) FROM CMRC_Products whereCMRC_Products.CategoryID=@.categoryID

//do other things

go

And then in the code you can get the count from the first result set using SqlDataReader.

|||

Ok, I thought only DataSets alowed that (ie Tables[0], Table[1] etc, or does the Reader just return it at the end of the recordset.

I'll have a play around with it. Thanks for the tip.

Cheers,Shaun.

|||

I am having exa ctly the same problem.

You mentioned that you used a method to Read the data into a list<Product> then close the Reader, then set reference to my output param. This is exactly what i am trying to do now could you tell me the code for this.

many thanks

martin

|||

In my Product.cs I have the following new method:

//Used for paged results in catagory searchpublicvoid ProductList(int ProductID,string Medium,string ModelName,string ProductImage,double UnitCost,bool IsAdult)

{

_ProductID = ProductID;

_Medium = Medium;

_ModelName = ModelName;

_ProductImage = ProductImage;

_UnitCost = UnitCost;

_IsAdult = IsAdult;

}

***************************

In my CatalogManager .cs (I didn't put it in the provider project as it is a pain in the ar*e to maintain) I have the following:

public

staticList <Product> GetProductsByCategoryPaging(int categoryID,int pageIndex,int numRows,outint itemCount)

{

using (SqlConnection connection =newSqlConnection(ConfigurationManager.ConnectionStrings["CommerceTemplate"].ConnectionString))

{

using (SqlCommand command =newSqlCommand("CMRC_PRODUCTS_GetListByCategoryPaging", connection))

{

command.CommandType =

CommandType.StoredProcedure;

command.Parameters.Add(

"@.itemCount",SqlDbType.Int, 4);

command.Parameters[

"@.itemCount"].Direction =ParameterDirection.Output;

command.Parameters.Add(

newSqlParameter("@.categoryID", categoryID));

command.Parameters.Add(

newSqlParameter("@.pageIndex", pageIndex));

command.Parameters.Add(

newSqlParameter("@.numRows", numRows));

connection.Open();

//populate listList<Product> list =newList<Product>();using (SqlDataReader rdr = command.ExecuteReader()) {while (rdr.Read()) {Product temp =newProduct();

temp.ProductList(

(

int)rdr["ProductID"],

rdr[

"Medium"].ToString(),

rdr[

"ModelName"].ToString(),

rdr[

"ProductImage"].ToString(),Convert.ToDouble (rdr["UnitCost"]),

(

bool)rdr["IsAdult"]);

list.Add(temp);

}

rdr.Close();

}

itemCount = (

int)command.Parameters["@.itemCount"].Value;return list;

}

}

}

You'll need to include the this declaration in the CatalogManager:

using System.Collections.Generic;

Good luck I feel you pain :-)

|||

really, many thanks for that it was driving me crazy

cheers

martin

|||

Once again many thanks for your help with this. Everhting is working fine.

Just one question did you use querystrings and if you did, how did you validate

them. I am having a lot of problems trying to figure out how to do this.

many thanks for all your help

martin

|||

Yes I did use query strings. My catalog page handles many queries.

I had a switch statement in the Page Load.

switch (SearchType) <== I have a param for query type.

{

case"c"://category search

cid =

Convert.ToInt32(Request.QueryString["cid"]);

dlCatalog.DataSource = objDSCategory;

dlCatalog.DataBind();

break;case"a"://artist search

dlCatalog.DataSource = objDSArtist;

dlCatalog.DataBind();

break;

etc

}

in the obj DataSource in aspx:

<

asp:ObjectDataSourceID="objDSGallery"runat="server"OldValuesParameterFormatString="{0}"SelectMethod="GetProductsByGalleryPaging"TypeName="CatalogManager"OnSelected="objDSGallery_Selected"OnSelecting="objDSGallery_Selecting"><SelectParameters><asp:QueryStringParameterName="GalleryID"QueryStringField="gid"Type="Int32"/><asp:QueryStringParameterName="pageIndex"QueryStringField="PageIndex"DefaultValue="0"/><asp:QueryStringParameterName="numRows"QueryStringField="NumRows"DefaultValue="6"/><asp:ParameterName="itemCount"Direction="Output"Type="Int32"/></SelectParameters></asp:ObjectDataSource>

Let me know if you have any more dramas, I can email you the code I'm using for reference.

The OnSelected and Selecting methods are used for updating the paging links.

I can email you the code if you like.

Cheers,Shaun.

|||

thanks for your reply

i would be grateful for the code my address istbcmartingharvey@.yahoo.co.jp

many thanks

martin

|||

Is your Yahoo account still active? got a return reply saying:

554 delivery error: dd This user doesn't have a yahoo.co.jp account (tbcmartingharvey@.yahoo.co.jp)

Is that your correct mail address?

Cheers,Shaun

ps I'll be away for several days (biz not pleasure :-( ) so will not get the code to you till Tues. Probably best I provide it as a download from my server so other users can access it too.

|||

thanks for your reply shaun

must be the summer heat

itstbcmartinharvey@.yahoo.co.jp

thank you

martin