Combining datatables

19 Jul 2006 5:57 AM

Bonzol

Hey there, just starting out using data tables, can anyone tell me how
to combine 2 tables?

Ive tried just coyping the info from 1 table to another, which both
have exactly the same table layout
data is a datatable
data1 is a datatable

data.Rows.Add(data1.Rows(0).Item(0))

but it dosnt work, can anyone tall me what im doing wrong?

19 Jul 2006 6:06 AM

Steven Nagy

Not sure if I understand completely, but are you asking how you can
copy the CONTENTS of one datatable into another? Or do you want to
MERGE the two tables, creating a new table that has columns from both?
How are both tables constructed previously? Post code if necessary.

19 Jul 2006 6:19 AM

Bonzol

Ahh sorry for not being clear,

I want to copy the contents of one table to another

I have been playing around and have now tried

Dim itemadd As DataRow = data.NewRow()
itemadd = data1.Rows(0).Item(0)
data.Rows.Add(itemadd)

but does not work.

19 Jul 2006 6:35 AM

Bonzol

tables were previously created via

Public Function jWildCard(ByVal table1 As String, ByVal returncolumn
As String, ByVal table2 As String, ByVal checkcolumn As String, ByVal
table1joinField As String, ByVal table2joinField As String, ByVal
checkvalue As String) As DataTable
        '
        Dim StringToReturn As String

        StringToReturn = ""
        Dim SQL As String
           SQL = "SELECT " + table1 + "." + returncolumn + " FROM " +
table1 + " INNER JOIN " + table2 + " ON " + table1 + "." +
table1joinField + " = " + table2 + "." + table2joinField + " WHERE(" +
table2 + "." + checkcolumn + " LIKE '%" + checkvalue + "%')"
        Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter
        dataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQL,
Me.OleDbConnection1)

        Dim dt As System.Data.DataTable
        dt = New System.Data.DataTable
        dataAdapter.Fill(dt)

        Return dt

this is done twice and both tables have the same Select table and
return coloumn.

19 Jul 2006 11:57 PM

Steven Nagy

Ok try this:

Dim newRow as datarow = secondtable.NewRow()
newRow.ItemArray = oldRow.Itemarray()
secondtable.Rows.add(newRow)

.... where oldRow is a row from your old table.
This code would be inserted in a 'for each' loop to get each row from
the old table.

Good luck.

20 Jul 2006 1:06 PM

Rob Panosh

Steven,

If you are using the 2.0 framwork you can to the following:

Dim newTable as New System.Data.DataTable = dt.DefaultView.ToTable

Just make sure you don't have any rowfilters set on the default view.

Cheers,
Rob Panosh

Steven Nagy wrote:
Show quoteHide quote

> Ok try this:
>
> Dim newRow as datarow = secondtable.NewRow()
> newRow.ItemArray = oldRow.Itemarray()
> secondtable.Rows.add(newRow)
>
> ... where oldRow is a row from your old table.
> This code would be inserted in a 'for each' loop to get each row from
> the old table.
>
> Good luck.

20 Jul 2006 1:09 PM

Rob Panosh

Sorry mean't to send to Bonzol.

Cheers,
Rob

Rob Panosh wrote:
Show quoteHide quote

> Steven,
>
> If you are using the 2.0 framwork you can to the following:
>
> Dim newTable as New System.Data.DataTable = dt.DefaultView.ToTable
>
> Just make sure you don't have any rowfilters set on the default view.
>
> Cheers,
> Rob Panosh
>
>
> Steven Nagy wrote:
> > Ok try this:
> >
> > Dim newRow as datarow = secondtable.NewRow()
> > newRow.ItemArray = oldRow.Itemarray()
> > secondtable.Rows.add(newRow)
> >
> > ... where oldRow is a row from your old table.
> > This code would be inserted in a 'for each' loop to get each row from
> > the old table.
> >
> > Good luck.

20 Jul 2006 3:54 PM

Cor Ligthert [MVP]

Rob,

> Dim newTable as New System.Data.DataTable = dt.DefaultView.ToTable

Dim newTable as DataTable = dt.copy

does the same without thinking about the rowfilter and version.

http://msdn2.microsoft.com/en-us/library/system.data.datatable.copy.aspx

I got the idea you did forever miss this one.
(However this cannot be the solution from the problem from the OP)

Cor

20 Jul 2006 10:48 PM

Steven Nagy

Yes the OP asked about combining 2 tables.
We don't want to overwrite the second table with a copy of the first.
Thus I think my solution might still be the best...
Its untested code though so am not sure how the datarow will handle the
itemarray if any of its copied contents do not match the columns
collection in the parent table.

Cor Ligthert [MVP] wrote:
Show quoteHide quote

> Rob,
>
> > Dim newTable as New System.Data.DataTable = dt.DefaultView.ToTable
> Dim newTable as DataTable = dt.copy
>
> does the same without thinking about the rowfilter and version.
>
> http://msdn2.microsoft.com/en-us/library/system.data.datatable.copy.aspx
>
> I got the idea you did forever miss this one.
> (However this cannot be the solution from the problem from the OP)
>
> Cor

21 Jul 2006 4:09 AM

Cor Ligthert [MVP]

Steven,

> Yes the OP asked about combining 2 tables.
> We don't want to overwrite the second table with a copy of the first.
> Thus I think my solution might still be the best...
> Its untested code though so am not sure how the datarow will handle the
> itemarray if any of its copied contents do not match the columns
> collection in the parent table.
>

Simple it throws an error and if not catched it stops.

The items in a datarow have forever to be confirm the datacolumn colletion
in a datatable.

Although that your first solution can be the solution will than the
"importrow" methode probably be better because that keeps the rowstate,
while yours is setting that as added.

Cor

20 Jul 2006 2:56 AM

Cor Ligthert [MVP]

Bonzol,

Your select is impossible with a Option Strict on.

Therefore it can give unpredictiable results depending on the values that
are in your variables.

Therefore I doubt if it is wise to start with trying to use methods as
merge, importrow etc, if you have not solved this before and know what you
are doing.

Just my thought,

Cor

Show quoteHide quote

"Bonzol" <Bon***@hotmail.com> schreef in bericht
news:1153290930.522145.304110@b28g2000cwb.googlegroups.com...
> tables were previously created via
>
>
> Public Function jWildCard(ByVal table1 As String, ByVal returncolumn
> As String, ByVal table2 As String, ByVal checkcolumn As String, ByVal
> table1joinField As String, ByVal table2joinField As String, ByVal
> checkvalue As String) As DataTable
>        '
>        Dim StringToReturn As String
>
>        StringToReturn = ""
>        Dim SQL As String
>           SQL = "SELECT " + table1 + "." + returncolumn + " FROM " +
> table1 + " INNER JOIN " + table2 + " ON " + table1 + "." +
> table1joinField + " = " + table2 + "." + table2joinField + " WHERE(" +
> table2 + "." + checkcolumn + " LIKE '%" + checkvalue + "%')"
>        Dim dataAdapter As System.Data.OleDb.OleDbDataAdapter
>        dataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQL,
> Me.OleDbConnection1)
>
>        Dim dt As System.Data.DataTable
>        dt = New System.Data.DataTable
>        dataAdapter.Fill(dt)
>
>
>        Return dt
>
>
> this is done twice and both tables have the same Select table and
> return coloumn.
>

19 Jul 2006 6:15 AM

Cor Ligthert [MVP]

Bonzol,

One of the things you have to know when working with datarows and datatables
is that you never can have a datarow in more than one table. Simple because
of the fact that the datarow has a property that references to the table it
is in.

However you can use copies of datarow.

But first answer the question from Steven.

Cor

Show quoteHide quote

"Bonzol" <Bon***@hotmail.com> schreef in bericht
news:1153288677.442118.315240@s13g2000cwa.googlegroups.com...
> Hey there, just starting out using data tables, can anyone tell me how
> to combine 2 tables?
>
> Ive tried just coyping the info from 1 table to another, which both
> have exactly the same table layout
> data is a datatable
> data1 is a datatable
>
> data.Rows.Add(data1.Rows(0).Item(0))
>
> but it dosnt work, can anyone tall me what im doing wrong?
>