parameter

Hi,

I am using a stored proc which takes in a parameter from a webpage. I am using freetext and have to pass in a parameter inside quotes so that freetext treats the phrase as a single word.

something similar to this.

freetext(SITE_NAME,' " '+ @.Search+' " ')

this is giving error.

Please Help

try using the char function passing it the numeric ascii code for double quotes in your sql statement: char(39)

|||

Hi bendJoe,

I am using freetext and have to pass in a parameter inside quotes so that freetext treats the phrase as a single word

Could you please give us an example what the @.Search parameter could be? Also, why do you want FreeText function treate your search words as a singe world?

Based on my understanding, we can pass in more than one words into FreeText predicate and those words will be seperated and assigend a weight value automatically. And that's how FreeText function is designed. See the defination in Books Online:

Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on thefreetext_string, assigns each term a weight, and then finds the matches.

Separates the string into individual words based on word boundaries (word-breaking).

Generates inflectional forms of the words (stemming).

Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

paramater values inside IN clause

I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesn=B4t retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) =3D NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=3D1
and (requeststate=3D'expd' or requeststate=3D'done' or
requeststate=3D'closed' or requeststate=3D'atrib')
and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
(facturationtype.code =3D request.invoicetype
and request.destinationentity =3D entity.code
and request.service =3D service.code
and request.code=3Drequestitem.request
and item.code =3D requestitem.item
and requestitem.brand =3D brand.code
and facturationtype.code =3D facturationstep.facturationtypecode
and request.campaign=3Dcampaign.code)
and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
service.state=3D1 and brand.state=3D1 and campaign.state=3D1
and entity.state=3D1 and facturationtype.state=3D1 and
request.toinvoice=3D1 and facturationstep.state =3D 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
=3D t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?Which parameter are you using to pass in multiple values? The parameters for
the stored procedure don't seem quite 'large' enough to hold many values in
a comma delimited string.
You are passing them in as a comma delimited string?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?|||For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters =
for
> the stored procedure don't seem quite 'large' enough to hold many values =
in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesn=B4t retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) =3D NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=3D1
> and (requeststate=3D'expd' or requeststate=3D'done' or
> requeststate=3D'closed' or requeststate=3D'atrib')
> and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
> efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
> (facturationtype.code =3D request.invoicetype
> and request.destinationentity =3D entity.code
> and request.service =3D service.code
> and request.code=3Drequestitem.request
> and item.code =3D requestitem.item
> and requestitem.brand =3D brand.code
> and facturationtype.code =3D facturationstep.facturationtypecode
> and request.campaign=3Dcampaign.code)
> and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
> service.state=3D1 and brand.state=3D1 and campaign.state=3D1
> and entity.state=3D1 and facturationtype.state=3D1 and
> request.toinvoice=3D1 and facturationstep.state =3D 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> =3D t2.request) as pesopedido
> from #ttemp t2
>=20
> drop table #ttemp
> GO
>=20
>=20
>=20
> Can anyone help?|||For @.strService, the comma delimited string should have single quotes around
each element. Your example [@.strService ('111,'222')] is incorrect.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters
> for
> the stored procedure don't seem quite 'large' enough to hold many values
> in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesnt retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) = NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=1
> and (requeststate='expd' or requeststate='done' or
> requeststate='closed' or requeststate='atrib')
> and client like @.strClient and efectivedate >= @.dtBeguinDate and
> efectivedate <= @.dtEndDate) and requestitem.state=1 and
> (facturationtype.code = request.invoicetype
> and request.destinationentity = entity.code
> and request.service = service.code
> and request.code=requestitem.request
> and item.code = requestitem.item
> and requestitem.brand = brand.code
> and facturationtype.code = facturationstep.facturationtypecode
> and request.campaign=campaign.code)
> and item.state=1 and requestitem.state=1 and request.state=1 and
> service.state=1 and brand.state=1 and campaign.state=1
> and entity.state=1 and facturationtype.state=1 and
> request.toinvoice=1 and facturationstep.state = 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign = @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> = t2.request) as pesopedido
> from #ttemp t2
> drop table #ttemp
> GO
>
> Can anyone help?|||I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes aro=
und
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Table DDL would help in diagnosis.
If service.code is a numeric datatype then, @.strService ('111,222') would be
correct. If service.code is a character datatype then each comma separated
element needs quotes around it.
Please verify the datatype for service.code.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes
> around
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would=
be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:
ers
ues
me
=3D1|||IN() list in SQL cannot accept a parameter. Either for scalar values use the
equality operator ( = ) or for multiple values use an appropriate technique
detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||The way I see it, @.strBrand has a length problem.
It's defined as a varchar(10), and with @.strService('dsmt','merch'), you are
trying to push 14 characters into it.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151085462.464011.201720@.c74g2000cwc.googlegroups.com...
Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would
> be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:|||Your problem is, quite simply, that you're using IN wrong. This is what
you're telling SQL Server when you pass it a @.strService value of
('dsmt,merch'):
@.strService IN ('dsmt,merch')
What you're trying to achieve is this:
@.strService IN ('dsmt', 'merch')
The way to do this is to break up your comma-delimited string into a temp
table or table variable and join/subquery on it. Here's one method:
http://groups.google.com/group/micr...de=source&hl=en
Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?sql

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

Paging in Stored Procedure

I got this code on internet for Stored Proc paging. This will work if my final result is the order of eployee id.
In my case my final result is in the order of emplyee name. What i have to change in below code so i can have paging in SQL plus my order is by name. I dont want to create Temp table. Becuase i think Creating temp table is overhead to sql server
and in that case i'll use Datagrid defualt paging and return all rows always.(My total number of rows will not be more than 300 and in 70% cases they are below 100)

CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@.startRowIndex int,
@.maximumRows int
)
AS

DECLARE @.first_id int, @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @.first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

i'm using SQL server 2000|||

If name is unique, then just change it to:

DECLARE @.first_name varchar(100), @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_name = name FROM employees ORDER BY name

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE name >= @.first_name
ORDER BY e.name

SET ROWCOUNT 0

If name is not unique, you might get some overlap, but it won't be a critical issue unless you have a lot of overlap. You could use two columns and include the employeeId for uniqueness, if you wanted to avoid overlapping rows.

|||name will not be Unique|||

This should work:

DECLARE @.first_name varchar(100), @.employeeId int, @.startRow int
-- A check can be added to make sure @.startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @.startRowIndex
SELECT @.first_name = name, @.employeeId = employeeId FROM employees ORDER BY name

-- Now, set the row count to MaximumRows and get
-- all records >= @.first_id
SET ROWCOUNT @.maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE name > @.first_name
or (name = @.first_name
and employeeId > @.employeeId)
ORDER BY e.name, e.employeeId

SET ROWCOUNT 0

Something like that...