adding rows from 1 dataset to another

[EricJ]

Hi all

im a bit confused here, I m trying to add the rows from 1
dataset.tables(0).rows to another ds2.tables(0).rows.add(row) there is
probably a better way to do this than going looping true the rows (if you
know tell me ;p), but that is how im trying to do this atm.
at a certain point i get an error message : This row already belongs to
another table.
i dont know w i am doing wrong (not that it is nice code but i added it
below anyway)

i'll try to explain the situation a bit, i have groups that can contain
elements or other groups and i have to go true a group to c if it has
subgroubs, then go true the subgroups ... there is no way to tell how many
levels there will be. I'm not even sure if it isn't possible to create an
sql statement to get the data all sugestions are welcome.

tnx

--
Juchtmans Eric
Omnipack

Private Function filterGroepen(ByVal gr As clsArtGroep) As DataSet

Try

Dim ds As DataSet

ds = SqlHelper.ExecuteDataset(gstrCnn, CommandType.Text, "Select artgdArtgID
from tblArtgroepdeel where artgdArtgID <> 0 " & _

"and artgdMainArtgID = " & gr.ID & "")

Dim rij, rij2,r As DataRow

Dim dssub As DataSet

Dim g As New clsArtGroep

For Each rij In ds.Tables(0).Rows

g.ID = (CInt(rij(0)))

dssub = filterGroepen(g)

For Each rij2 In dssub.Tables(0).Rows

r = rij2

ds.Tables(0).Rows.Add(r)

Next

Next

Return ds

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Function



structure of the tables to give you an idea (not complete)

tblMAIN (ID, NAME)

tblSUB (ID, MAINID, ELEMENT, SUBID) (or element is filled in or SUBID)

 

[Cor]

Hi EricJ,

First do us next time a favor, paste your code in a textbox and then in the
message, this is terrible reading.

I can only see a part of your code (it is enough I think you can test it
yourself).

I think this is not right

You first have to make a datarow with the structure from ds
r=ds.Tables(0).newrow

r = rij2

If this will go, I don't believe I copy the selective Items, I think I will
try/see tomorrow if there is not a more clever way if nobody gives us an
answer about that

And then add the new row to the

ds.Tables(0).Rows.Add(r)

I hope you succeed,

Because I cannot answer your post more I think today.

Let me know OK?

Cor

 

[Cor]

Hi Eric,
I look to it tomorrow,
What I ment was, paste it in a notebook and back in this message, but for me
it is OK in this way also but some don't like this either.
Cor

 

[EricJ]

it will never be perfect for all :/
i just had a thought (stupid actually) if i start putting the data in a
string from the start it should work and probably be faster (ill have the
problem later on but for now i think i can continue).

but there should be a way to add all the rows from a dataset table to
another dataset table w the same structure, at least i think it should be
possible.

eric

 

[EricJ]

ok this is working (seems i had a for loop to mutch) it would probably work
w the datasets 2

Try
gr = ANode.Tag
sb.Append(gr.ID)
filterGroepen(gr, sb)
Dim strSql As String
strSql = sb.ToString(0, sb.Length)
ds = SqlHelper.ExecuteDataset(gstrCnn, CommandType.Text, "SELECT
distinct artgID, " & _
"artgNaam, artgOms FROM tblArtgroep WHERE artgID not in
(" & strSql & ")")
catch ex As Exception
MessageBox.Show(ex.Message)
End Try

Private Sub filterGroepen(ByVal gr As clsArtGroep, ByRef sb As
System.Text.StringBuilder)
Try
Dim ds As DataSet
Dim r As DataRow
ds = SqlHelper.ExecuteDataset(gstrCnn, CommandType.Text, "Select
artgdArtgID from tblArtgroepdeel " & _
"where artgdArtgID <> 0 and artgdMainArtgID = " & gr.ID &
"")
Dim rij As DataRow
Dim g As New clsArtGroep
For Each rij In ds.Tables(0).Rows
g.ID = (CInt(rij(0)))
sb.Append(", ")
sb.Append(g.ID)
filterGroepen(g, sb)
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

 

[Cor]

Hi Eric,

I found this on MSDN it is a part of an example describing the itemarray in
a datarow.

I was looking for that ExecuteDataset when I did realize you did make your
own class for datasets (Like I do myself also by the way). ))

But it makes it difficult to trace your problem. But I think this fits
almost totaly your problem and if not you can look for that itemarray as a
datarow property
So why would I do that.

Private Sub CreateRowsWithItemArray()
' Make a DataTable using the function below.
Dim dt As DataTable = MakeTableWithAutoIncrement()
Dim dr As DataRow
' Declare the array variable.
Dim myArray(1) As Object
' Create 10 new rows and add to DataRowCollection.
Dim i As Integer
For i = 0 to 9
myArray(0) = DBNull.Value
myArray(1)= "item " & i.ToString()
dr = dt.NewRow()
dr.ItemArray = myArray
dt.Rows.Add(dr)
Next
PrintTable(dt)
End Sub

I hope this helps.

Cor

 

[EricJ]

credit where it belongs ;p i use the data access application block for .NET
for the data access and calling stored procedures ... (bit lazy but it comes
in handy i do make classes for all my tables that handle elements of that
table

but i'll have a look at the itemarray you suggested it looks interesting.

tnx for the time

eric

 

[Cor]

Hi Eric,

I am widening my scoop a little bit.

And while looking in the adonet group, I saw an answer from Kevin about the
ImportRow.

This is all that I found about it on MSDN, but I think it is something you
can try.
I go now looking what that nice example I got from Fergus can do for me.

Calling NewRow adds a row to the table using the existing table schema, but
with default values for the row, and sets the DataRowState to Added. Calling
ImportRow preserves the existing DataRowState, along with other values in
the row.

Cor