parameter modification within mdx

Is it possible to modify a parameter value within a dataset using mdx?

lets say the param values of @.bucket = 6

how would you go about writing @.bucket + 1

so @.bucket now would equal 7

is this possible?

Your options are:

1. Call an SSAS stored procedure from the generated MDX statement, e.g.

SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(MyAssemblyName.MyStoredProcedureName(@.ProductProduct), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

2. If you want to use an SSRS code-behind function, you need to use an expression-based query, e.g.:

="SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(" & Code.SomeProcedure(Parameters!Product.Value & "), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

Parameter dependency on other params

Hi,
I have a param in my report which depends on the other two parameters. So
every time I change either Param1 or Param2 the list for Param3 is
automatically refreshed. While this works fine in one of the reports, I am
having following problems in the another report -
1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
2. Even a mouse-click in the Param2 box, half of the time, takes the cursor
to Param1 box. A very "cautious" click prevent it from going back to Param1
box :-)
3. The report "hangs" at time in the "Preview" mode and it adds 3-4 blank
pages at the end of the report although I know that the report should not
exceed 1 page.
4. I get "Object reference not set to an instance of an object" and "A
generic error occured in GDI+" randomly.
Param1 and Param2 are of type datetime and are not related. Param3 is of
type string and comes from a query based on the values for Param1 and Param2.
Has anybody else faced this ? Any solutions.
RegardsYou might be seeing a problem in preview in the report designer. Try
deploying it and see if the same problem happens. I have seen other issues
in the designer with cascading parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dude" <Dude@.discussions.microsoft.com> wrote in message
news:5DA4AA93-DE58-486A-9E01-76EED48324BC@.microsoft.com...
> Hi,
> I have a param in my report which depends on the other two parameters. So
> every time I change either Param1 or Param2 the list for Param3 is
> automatically refreshed. While this works fine in one of the reports, I am
> having following problems in the another report -
> 1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
> 2. Even a mouse-click in the Param2 box, half of the time, takes the
cursor
> to Param1 box. A very "cautious" click prevent it from going back to
Param1
> box :-)
> 3. The report "hangs" at time in the "Preview" mode and it adds 3-4 blank
> pages at the end of the report although I know that the report should not
> exceed 1 page.
> 4. I get "Object reference not set to an instance of an object" and "A
> generic error occured in GDI+" randomly.
> Param1 and Param2 are of type datetime and are not related. Param3 is of
> type string and comes from a query based on the values for Param1 and
Param2.
> Has anybody else faced this ? Any solutions.
> Regards|||I don't see the report hang but the "Available List" is not refreshed when I
modify the Param1 or Param2. Only after I click on "View Report" is the list
modified.
I found the sam thing happening in the other report which I thought was
working. The only difference is that it has another string Param (Param0)
which is used as an Input param for the Param3. Param0 is populated by a
query. So what I am seeing is -
1. On change of Param0 (a drop down list), Param3 is refreshed automatically
2. On change of Param1 and Param2, Param3 is not refreshed
3. Param3 is refreshed, once I click "View Report".
Is this the expected behaviour ? anything to do with the type (datetime) ?
"Bruce L-C [MVP]" wrote:
> You might be seeing a problem in preview in the report designer. Try
> deploying it and see if the same problem happens. I have seen other issues
> in the designer with cascading parameters.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dude" <Dude@.discussions.microsoft.com> wrote in message
> news:5DA4AA93-DE58-486A-9E01-76EED48324BC@.microsoft.com...
> > Hi,
> > I have a param in my report which depends on the other two parameters. So
> > every time I change either Param1 or Param2 the list for Param3 is
> > automatically refreshed. While this works fine in one of the reports, I am
> > having following problems in the another report -
> > 1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
> > 2. Even a mouse-click in the Param2 box, half of the time, takes the
> cursor
> > to Param1 box. A very "cautious" click prevent it from going back to
> Param1
> > box :-)
> > 3. The report "hangs" at time in the "Preview" mode and it adds 3-4 blank
> > pages at the end of the report although I know that the report should not
> > exceed 1 page.
> > 4. I get "Object reference not set to an instance of an object" and "A
> > generic error occured in GDI+" randomly.
> >
> > Param1 and Param2 are of type datetime and are not related. Param3 is of
> > type string and comes from a query based on the values for Param1 and
> Param2.
> >
> > Has anybody else faced this ? Any solutions.
> >
> > Regards
>
>|||I think you are seeing a bug in Report Designer. If you deploy it and test
it I bet you will see it is OK. I tracked down something similar for someone
else awhile back.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dude" <Dude@.discussions.microsoft.com> wrote in message
news:2FF737A5-D781-4FA1-A205-7BFE7533EEA5@.microsoft.com...
> I don't see the report hang but the "Available List" is not refreshed when
I
> modify the Param1 or Param2. Only after I click on "View Report" is the
list
> modified.
> I found the sam thing happening in the other report which I thought was
> working. The only difference is that it has another string Param (Param0)
> which is used as an Input param for the Param3. Param0 is populated by a
> query. So what I am seeing is -
> 1. On change of Param0 (a drop down list), Param3 is refreshed
automatically
> 2. On change of Param1 and Param2, Param3 is not refreshed
> 3. Param3 is refreshed, once I click "View Report".
> Is this the expected behaviour ? anything to do with the type (datetime) ?
> "Bruce L-C [MVP]" wrote:
> > You might be seeing a problem in preview in the report designer. Try
> > deploying it and see if the same problem happens. I have seen other
issues
> > in the designer with cascading parameters.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Dude" <Dude@.discussions.microsoft.com> wrote in message
> > news:5DA4AA93-DE58-486A-9E01-76EED48324BC@.microsoft.com...
> > > Hi,
> > > I have a param in my report which depends on the other two parameters.
So
> > > every time I change either Param1 or Param2 the list for Param3 is
> > > automatically refreshed. While this works fine in one of the reports,
I am
> > > having following problems in the another report -
> > > 1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
> > > 2. Even a mouse-click in the Param2 box, half of the time, takes the
> > cursor
> > > to Param1 box. A very "cautious" click prevent it from going back to
> > Param1
> > > box :-)
> > > 3. The report "hangs" at time in the "Preview" mode and it adds 3-4
blank
> > > pages at the end of the report although I know that the report should
not
> > > exceed 1 page.
> > > 4. I get "Object reference not set to an instance of an object" and "A
> > > generic error occured in GDI+" randomly.
> > >
> > > Param1 and Param2 are of type datetime and are not related. Param3 is
of
> > > type string and comes from a query based on the values for Param1 and
> > Param2.
> > >
> > > Has anybody else faced this ? Any solutions.
> > >
> > > Regards
> >
> >
> >|||I think it was just a bit slow and I was a bit impatient :-)) Though I did
test it in the deployed version, I was seeing the problem as I didn't wait
enough for it to refresh the params. Thanks.
"Bruce L-C [MVP]" wrote:
> I think you are seeing a bug in Report Designer. If you deploy it and test
> it I bet you will see it is OK. I tracked down something similar for someone
> else awhile back.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Dude" <Dude@.discussions.microsoft.com> wrote in message
> news:2FF737A5-D781-4FA1-A205-7BFE7533EEA5@.microsoft.com...
> > I don't see the report hang but the "Available List" is not refreshed when
> I
> > modify the Param1 or Param2. Only after I click on "View Report" is the
> list
> > modified.
> >
> > I found the sam thing happening in the other report which I thought was
> > working. The only difference is that it has another string Param (Param0)
> > which is used as an Input param for the Param3. Param0 is populated by a
> > query. So what I am seeing is -
> > 1. On change of Param0 (a drop down list), Param3 is refreshed
> automatically
> > 2. On change of Param1 and Param2, Param3 is not refreshed
> > 3. Param3 is refreshed, once I click "View Report".
> >
> > Is this the expected behaviour ? anything to do with the type (datetime) ?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > You might be seeing a problem in preview in the report designer. Try
> > > deploying it and see if the same problem happens. I have seen other
> issues
> > > in the designer with cascading parameters.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Dude" <Dude@.discussions.microsoft.com> wrote in message
> > > news:5DA4AA93-DE58-486A-9E01-76EED48324BC@.microsoft.com...
> > > > Hi,
> > > > I have a param in my report which depends on the other two parameters.
> So
> > > > every time I change either Param1 or Param2 the list for Param3 is
> > > > automatically refreshed. While this works fine in one of the reports,
> I am
> > > > having following problems in the another report -
> > > > 1. A "Tab" after modifying the Param1, brings me back to Param1 box ?
> > > > 2. Even a mouse-click in the Param2 box, half of the time, takes the
> > > cursor
> > > > to Param1 box. A very "cautious" click prevent it from going back to
> > > Param1
> > > > box :-)
> > > > 3. The report "hangs" at time in the "Preview" mode and it adds 3-4
> blank
> > > > pages at the end of the report although I know that the report should
> not
> > > > exceed 1 page.
> > > > 4. I get "Object reference not set to an instance of an object" and "A
> > > > generic error occured in GDI+" randomly.
> > > >
> > > > Param1 and Param2 are of type datetime and are not related. Param3 is
> of
> > > > type string and comes from a query based on the values for Param1 and
> > > Param2.
> > > >
> > > > Has anybody else faced this ? Any solutions.
> > > >
> > > > Regards
> > >
> > >
> > >
>
>

Param to SP, adDecimal

I am passing a parameter to a stored proce that will have the value of a
percent, like .068
Here is where I set it up:
Set prmPercent = .CreateParameter("Percent", adDecimal, adParamInput, , 0.5)
in the stored proc, I have
@.Percent dec(18,9),
When I run it, I get "Percision is Invalid" when it calls the proc. I can't
find much on it in Book on-line, or from searching this forum.
Do I have to set the @.Percent in the stored proc up differently?
Thanks,
SteveYou have to set precision and scale properties also.
Set prmPercent.Precision = 18
Set prmPercent.NumericScale = 9
AMB
"SteveInBeloit" wrote:

> I am passing a parameter to a stored proce that will have the value of a
> percent, like .068
> Here is where I set it up:
> Set prmPercent = .CreateParameter("Percent", adDecimal, adParamInput, , 0.
5)
> in the stored proc, I have
> @.Percent dec(18,9),
> When I run it, I get "Percision is Invalid" when it calls the proc. I can
't
> find much on it in Book on-line, or from searching this forum.
> Do I have to set the @.Percent in the stored proc up differently?
> Thanks,
> Steve|||I can't get that to compile, I used
Set prmPercent = .CreateParameter("Percent", adDecimal,
adParamInput, , 0.5)
Set prmPercent.Precision = 18
Set prmPercent.NumericScale = 9
I get "Invalid use of Properties" and it highlights the Set
prmPercent.Precision = 18 statement.
Any idea?
"Alejandro Mesa" wrote:
> You have to set precision and scale properties also.
> Set prmPercent.Precision = 18
> Set prmPercent.NumericScale = 9
>
> AMB
> "SteveInBeloit" wrote:
>|||Sorry, try:
with prmPercent
.Precision = 18
.NumericScale = 9
end with
AMB
"SteveInBeloit" wrote:
> I can't get that to compile, I used
> Set prmPercent = .CreateParameter("Percent", adDecimal,
> adParamInput, , 0.5)
> Set prmPercent.Precision = 18
> Set prmPercent.NumericScale = 9
> I get "Invalid use of Properties" and it highlights the Set
> prmPercent.Precision = 18 statement.
> Any idea?
>
> "Alejandro Mesa" wrote:
>|||AMB
THANK YOU VERY MUCH! That did it. I struggled with this quite some time
today.
Thanks again,
Steve
"Alejandro Mesa" wrote:
> Sorry, try:
> with prmPercent
> .Precision = 18
> .NumericScale = 9
> end with
>
> AMB
> "SteveInBeloit" wrote:
>

param prob

hi ,
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtoset(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

my second dataset query is like following,

SET [FilteredBUList] AS strtoset(@.BU)

SET [customtimeset] AS.....

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
intersect( [FilteredBUList],...) on rows

FROM Profitability

i need to map previous query BU parameter to the second dataset parameter. pls tel m the proper way.
cant access report parameter in this way from different dataset.
WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtotuple(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

param into xsl in an xml template query

Is it possible to pass a parameter to the *xsl* in a template query?
- i dont mean to the sql - thats easy - i want to pass a param to the xsl.
TIA
Paul
pauly wrote:
> Is it possible to pass a parameter to the *xsl* in a template query?
> - i dont mean to the sql - thats easy - i want to pass a param to
> the xsl. TIA
> Paul
I have a sneaky feeling i've asked this before... and the answer was 'no' -
the only way of getting other information to the xsl is to get it into the
xml itself, via sql:param.
However if anyone knows a way of doing what i'm asking, i'd love to know!
Paul
|||Hi,
i make this from MSXMLDOM.
i load a XSL stylesheet into an XML DOM object and set param value with
VBScript or VB6 Etc...{use it in extended proc ?!}
an other way is loading XSL in MSXML.template DOM Object with param in url
like this
MySteelsheet.xsl?param1=toto
in the steelshet after the root node steelsheet write one param node named
param1
i dont have testing this but it is in my book "Wrox XSLT second edition"
i hope to help you.
Boss Hog
PS:excuse my english but i'm poor lonesome frenchy...lol
"pauly" <paulyateswithoutthespam@.hotmail.com> wrote in message
news:2rqj73F1cis87U1@.uni-berlin.de...
> Is it possible to pass a parameter to the *xsl* in a template query?
> - i dont mean to the sql - thats easy - i want to pass a param to the
xsl.
> TIA
> Paul
>
|||"pauly" <paulyateswithoutthespam@.hotmail.com> wrote in message
news:2rqj73F1cis87U1@.uni-berlin.de...
> Is it possible to pass a parameter to the *xsl* in a template query?
> - i dont mean to the sql - thats easy - i want to pass a param to the xsl.
Not directly. But why not just pass the values as part of the XML?
Bryant
|||hi,
of course it is possible to set param in XML file transformed by XSL.
@.+
Boss Hog
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:uRsqykNpEHA.2068@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> "pauly" <paulyateswithoutthespam@.hotmail.com> wrote in message
> news:2rqj73F1cis87U1@.uni-berlin.de...
xsl.
> Not directly. But why not just pass the values as part of the XML?
> --
> Bryant
>
sql

param for MySQL data source in SSIS 2005

Hi experts,

I 'm trying to get countNumber from a select statement in MySQL source. Something likes this:

select @.countNumber = count(*) from tableName where condition_1 = xxx

I use ExecuteSQLTask to get the result set with result name @.countNumber along with ADO.NET connection type.
I 've installed mysql-connector-net-5.0.5 cause the source is MySQL 5x.

And I got a task failed error which is described below:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "new_process": "Result binding by name "@.new_process" is not supported for this connection type. ".

Please help me to fix this error, thanks alot

Best reguards,

Try this and see if it helps,

change the query like, Select count(*) from tableName where condition_1 = xxx

Create a variable of the type object. Make sure in the Execute SQL Task you have selected Result Set as Single Row.

In the ResultSet, give the ResultName as 0 and map it to the variable you have created.

|||

Rick wrote:

Try this and see if it helps,

change the query like, Select count(*) from tableName where condition_1 = xxx

Create a variable of the type object. Make sure in the Execute SQL Task you have selected Result Set as Single Row.

In the ResultSet, give the ResultName as 0 and map it to the variable you have created.

No, do not make the data type of the variable "object." Make it an integer.

|||

Phil Brammer wrote:

No, do not make the data type of the variable "object." Make it an integer.

Sorry to ask Phil, but can you let me know what is the issue in using the datatype object ?

|||

Rick wrote:

Phil Brammer wrote:

No, do not make the data type of the variable "object." Make it an integer.

Sorry to ask Phil, but can you let me know what is the issue in using the datatype object ?

Sure, the overhead is not needed. Plus, I don't think you can use an object variable in a precedence constraint, for instance (Unless SSIS can implicitly cast it). Object variables are designed to hold more than one row of data and/or multiple columns.|||Thanks Phil |||Hi experts,

At my first try, the task fails with integer data type.
I'm sorry Phil . But you are right when saying that object type is used to store more than one record with many columns.

I try it again with the object data type. It's cool but I have to run ScriptTask to get to cast type of the object var into int32.

Here is the way it runs:
Dim vars As Variables
Dim result As Int32
Dts.VariableDispenser.LockForRead("objVar")
Dts.VariableDispenser.GetVariables(vars)
Try
result = CInt(vars("objVar").Value)
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Dts.Variables("intCount").Value = result

If you have other better ways to solve my problem, it will be appreciated

thanks and best reguards,

khanhmy|||

Have you tried

Define variable 'CountNumber' of type Int32

Query

Select count(*) As CountNumber from TABLENAME where condition_1 = xxxx

In the resultset window map CountNumber to User::CountNumber.

Hope this helps!

|||To back up Jay L's post, this is what I do. Now, I guess it depends on what data type is returned from the COUNT(*) against a MySQL database. Since it's like Oracle, it's probably a DAMN numeric(nn,0) which SSIS has issues with.

You could try:

select cast(count(*) as REAL) from TABLENAME where condition_1 = xxxx

Try stuffing that into a variable of type, double.

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