loop throu dataset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

loop throu dataset

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-09-13 : 15:36:56

I tried to loop through dataset one to dynamically assign values to dataset two. I run into a build error as follow:

DataSet ds=new DataSet();
da.Fill (ds, "Price");

DataTable newdt=new DataTable();
DataRow dr = newdt.NewRow();
for (int i= 0; i<=ds.Tables["Price"].Rows.Count-1; i++)
newdt.Columns.Add(i.ToString());
for (int i=0; i<=ds.Tables[0].Columns.Count-1; i++)
for (int y = 0; y<=ds.Tables[0].Rows.Count-1; y++)
dr(i)=ds.Tables[0].Rows(y).Items(i).ToString;

'dr' denotes a 'variable' where a 'method' was expected

The task sounds so simpple. I want to assign a set of values from one
dataset/datatable to another by looping through one field at a time. The error description seems easy to understand, however, I just could not find a way to get around it.

The sample code I tried to copy is in vb.net as:
dr(i) = dtOld.rows(y).items(i).tostring.

Hommer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 05:45:48

try putting
DataRow dr = newdt.NewRow();
after you put columns into a dataTable

Go with the flow & have fun! Else fight the flow

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-09-14 : 09:10:18

I started out that way then I got this error:
Embedded statement cannot be a declaration or labeled statement

BTW, does anybody know where to find additional information on build error?

Thanks!

chadmat
The Chadinator

1974 Posts

Posted - 2004-09-14 : 13:33:04

you need to use square brackets [], not parens () for the indexer.

This is the line it is puking on:
dr(i)=ds.Tables[0].Rows(y).Items(i).ToString;

it should be:

dr[i]=ds.Tables[0].Rows[y].Items[i].ToString();

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

chadmat
The Chadinator

1974 Posts

Posted - 2004-09-14 : 13:41:41

BTW, What is it that you are ultimately trying to accomplish?

I have a feeling you are going about it wrong. Let me what you are trying to do.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-09-14 : 15:45:14

Thank you Chad and Spirit!
Chad's [] vs () did the trick except I have to use ItemArray instead of Items, which does not exist(:.
I need to spend some time on it to make the data looks right.

BTW, when I could not find any sample codes for what I am ultimately trying to accomplish, I certainly started to doubt that I am barking on the wrong tree.

Here is the water down version of the explanation.
The data in the table looks like this:
DisctType/QtyBreak/UnitPrice
A........./0 to 100/$10.00
A........./101 to 300/$9.00
B........./5000 & up/$2.25

The UI has to make data look as follow:
DisctType/A........./A.........../B
QtyBreak/0 to 100/101 to 300/5000 & up
UnitPrice/$10.00../$9.00....../$2.25

It is a flip-flop. Rows become columns.

My options are:
A) Select the data in the way it should look like at first place. I don't think this is possible unless I use temp table and cursor. T_SQL would not let you "Select Top 3 columns from tblA list by horizontally". Neither is this a cross tab. There is no computed data on the body.
B) Iterate through the data table and populate the datagrid cell by cell. I did not find any luck here. The data grid doesn't have rows/columns collection.
C) The one I am currently trying. Passing data from one data table to another, then bind the "right" one to data grid, or
D) Ask the users to change their way of looking at the data. We all know how that will work. Their answer is why don't you just give us what we want.

chadmat
The Chadinator

1974 Posts

Posted - 2004-09-14 : 16:43:37

Here is how I would rewrite your code. I didn't compile this so there might be an error or 2, but I think it is right. I am pretty sure it does what you want:


DataSet ds=new DataSet();
da.Fill (ds, "Price");

DataTable newdt=new DataTable();

foreach (DataRow dr in ds.Tables["Price"].Rows)
{
   DataColumn dc = new DataColumn(newdt.Columns.Count + 1, typeof(System.String))
   newdt.Columns.Add(dc);
}

foreach (DataRow dr in ds.Tables[0].Rows)
{
   DataRow newRow = newdt.NewRow();
   foreach(DataColumn dc in dr.Columns)
   {
      newRow[dc.Ordinal] = dr[dc].ToString();
   }
   newDT.Rows.Add(newRow);
}

HTH

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 04:51:52

here's an idea that might be useful:


USE Northwind
Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + '|', '') + cast(OrderId as varchar(10)) + ',' + CustomerId + ',' + cast(EmployeeId as varchar(10))
FROM Orders
SELECT @ColumnList

this is returned in a dataset in 3 rows and one column.
then you simply do dr[0].ToString().Split('|').Split(',') to get the item array and then u fill that item array into a datagrid.
if it helps great.

Go with the flow & have fun! Else fight the flow

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-09-15 : 09:24:25

I will try both solutions later.

Spirit,

Your script returned data in one row one column, if that is what you mean.

You guys are great!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 11:17:37

yes sorry... i thought it would be usefull.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources