Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

looping through report items of a rendered report

Hi all,
Situation: I have a WinForm application that uses the SQL 2005 reportviewer
component. That reportviewer uses a local report. The report definition
contains a report item (textbox) called "txtEmail" in the table header.
Would it be possible to walk (in runtime!) throught all the report items of
a rendered report so I can get the value of the report item "txtEmail"? I've
tried this code to walk through all the items in a rendered report:
ListControls(reportViewer.Controls);
private static void ListControls(Control.ControlCollection control)
{
foreach (Control e in control)
{
Console.WriteLine(" * " + e.ToString());
ListControls(e.Controls);
}
}
but it didn't list the actual report items. I expected to see some output
like " * txtEmail" but that control isn't listed.
Purpose: I have a report that lists a single email address in the table
header. I have overridden the
default export behaviour of the reportviewer so I can render the report to
PDF and mail it to a emailaddress that is available in the rendered report.
Any suggestions of how to accomplish this'
I am using VS2005 with local report attached to a WinForm reportviewer
component.
Kind regards,
PeterIn the past I passed a reference of ReportItems to my custom assembly, then
would walk through and grab the values I needed. For my purposes, turned
out there was a more efficient method with hidden textboxes. Also I had
some pages with static text and no rows. Inspecting the ReportItems
collection on these pages would throw an internal error and the report would
fail. Anyway, here's the code I used:
static string _SchoolName;
public string
SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
input)
{
string ReturnVal = null;
try
{
Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
rptItemSchoolNameHidden = null;
rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
if (rptItemSchoolNameHidden != null)
{
if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
{
_SchoolName =rptItemSchoolNameHidden.Value.ToString();
}
}
}
catch
{
//ignore report item not found error.
}
return _SchoolName;
}
Steve MunLeeuw
"Peter Bons" <joepie@.blakjsd.bl> wrote in message
news:uhlMDzS6GHA.4592@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Situation: I have a WinForm application that uses the SQL 2005
> reportviewer
> component. That reportviewer uses a local report. The report definition
> contains a report item (textbox) called "txtEmail" in the table header.
> Would it be possible to walk (in runtime!) throught all the report items
> of a rendered report so I can get the value of the report item "txtEmail"?
> I've tried this code to walk through all the items in a rendered report:
> ListControls(reportViewer.Controls);
> private static void ListControls(Control.ControlCollection control)
> {
> foreach (Control e in control)
> {
> Console.WriteLine(" * " + e.ToString());
> ListControls(e.Controls);
> }
> }
> but it didn't list the actual report items. I expected to see some output
> like " * txtEmail" but that control isn't listed.
> Purpose: I have a report that lists a single email address in the table
> header. I have overridden the
> default export behaviour of the reportviewer so I can render the report to
> PDF and mail it to a emailaddress that is available in the rendered
> report.
> Any suggestions of how to accomplish this'
> I am using VS2005 with local report attached to a WinForm reportviewer
> component.
> Kind regards,
> Peter
>
>|||Hi!
Just checking if you managed to solve the problem. I actually wanted to
ask if I could have an embedded code function instead of an external
assembly where in I could access the ReportItems collection for given
"textbox" and loop through all the values.
Thanks in advance for any suggestions.
Steve MunLeeuw wrote:
> In the past I passed a reference of ReportItems to my custom assembly, then
> would walk through and grab the values I needed. For my purposes, turned
> out there was a more efficient method with hidden textboxes. Also I had
> some pages with static text and no rows. Inspecting the ReportItems
> collection on these pages would throw an internal error and the report would
> fail. Anyway, here's the code I used:
> static string _SchoolName;
> public string
> SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
> input)
> {
> string ReturnVal = null;
> try
> {
> Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
> rptItemSchoolNameHidden = null;
> rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
> if (rptItemSchoolNameHidden != null)
> {
> if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
> {
> _SchoolName => rptItemSchoolNameHidden.Value.ToString();
> }
> }
> }
> catch
> {
> //ignore report item not found error.
> }
> return _SchoolName;
> }
> Steve MunLeeuw
>
> "Peter Bons" <joepie@.blakjsd.bl> wrote in message
> news:uhlMDzS6GHA.4592@.TK2MSFTNGP04.phx.gbl...
> > Hi all,
> >
> > Situation: I have a WinForm application that uses the SQL 2005
> > reportviewer
> > component. That reportviewer uses a local report. The report definition
> > contains a report item (textbox) called "txtEmail" in the table header.
> > Would it be possible to walk (in runtime!) throught all the report items
> > of a rendered report so I can get the value of the report item "txtEmail"?
> > I've tried this code to walk through all the items in a rendered report:
> >
> > ListControls(reportViewer.Controls);
> >
> > private static void ListControls(Control.ControlCollection control)
> >
> > {
> >
> > foreach (Control e in control)
> >
> > {
> >
> > Console.WriteLine(" * " + e.ToString());
> >
> > ListControls(e.Controls);
> >
> > }
> >
> > }
> >
> > but it didn't list the actual report items. I expected to see some output
> > like " * txtEmail" but that control isn't listed.
> >
> > Purpose: I have a report that lists a single email address in the table
> > header. I have overridden the
> > default export behaviour of the reportviewer so I can render the report to
> > PDF and mail it to a emailaddress that is available in the rendered
> > report.
> >
> > Any suggestions of how to accomplish this'
> > I am using VS2005 with local report attached to a WinForm reportviewer
> > component.
> >
> > Kind regards,
> > Peter
> >
> >
> >