Merge doesn't merge

Perhaps I have the concept all wrong, but based on the documentation and
books I have read it seems this should work. I have multiple complex
datasets in an access db. They have 2 shared primary keys. I have another
dataset that shares one key with them. I need to end up with a single flat
dataset comprised of the merged sets. In a simpler world it would be a
normal sql join, but that is not an option.

Right now, I would love for someone to point out my stupidity and show me
the magic setting I have forgotten. An additional complication is that the
last dataset - the one with just one key was a "relation" issue because that
field in the other table was not unique values. It would be a many-to-one
relation.

I have tried using both the relation AND the merge to no avail. I am not
modifying this data. I just need to read it in, merge it based on the key
values, and kick it out as a single table. As a test I tried creating a more
simple 2 table set (in ms sql), read them into datasets, and tried to merge.
I just do not ever see a merged result. here is the code that goes with a
simple webform with one datagrid.

Dim ds1 As New DataSet()

Dim ds2 As New DataSet()

Dim da1 As New SqlDataAdapter()

Dim da2 As New SqlDataAdapter()

Dim cn As New SqlConnection()

cn.ConnectionString() = "server=daddy;database=test;uid=test;pwd=test"

cn.Open()

da1.SelectCommand = New SqlCommand("select * from table1", cn)

da1.Fill(ds1, "tbl1")

da2.SelectCommand = New SqlCommand("select * from table2", cn)

da2.Fill(ds1, "tbl2")

ds1.Merge(ds2, True, MissingSchemaAction.Add)

DataGrid1.DataSource = ds1

DataGrid1.DataBind()

mohamed
Fri Feb 20 10:48:30 CST 2004

------=_NextPart_0001_099879C9
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hi YBOTI!!!
Let me rephrase what I think you want to do . you have three dataset each
has one data table . rather than merging them into a dataset with three
datatables , you want to merge them in one dataset that had ONE data table

YBOTI
Fri Feb 20 21:12:43 CST 2004

Thank you.

Using the AddWithKey is useful. That will save a step. However, an associate
figured out the missing part that I have not seen documented yet. To
successfully merge multiple tables (including extra new schema from new
tables) you must have the tables in different datasets AND give them the
SAME NAME.

For example, if in MyDataset I have a table named "MyBigFatGreekTable"

MyDataset.Merge(MyOtherDataset, ..., ...)

I must also have a table named "MyBigFatGreekTable" in MyOtherDataset. If
the tables you expect to merge have different names, the merge will not take
place. And, as you point out, you must share primary keys between tables.

Thanks again for the tip.

"Mohamoss" <mohamed.mossad@egdsc.microsoft.com> wrote in message
news:a%23onuF99DHA.3860@cpmsftngxa07.phx.gbl...
> Hi YBOTI!!!
> Let me rephrase what I think you want to do . you have three dataset each
> has one data table . rather than merging them into a dataset with three
> datatables , you want to merge them in one dataset that had ONE data table
>
> You mention primary keys inside the access database ;however, for this to
> work " the way I think you want it to " you need to specify the primary
> key to the dataset as well . you could do this on one of two way . either
> you specify it from within your code . so you say
> DS.Tables[0].PrimaryKey = new DataColumn[] {column1]// this method take an
> array of //columns as a parameter even if you have only one column as the
> primary key , still you //have to create an array of data Columns that
only
> has one element .
> Or you choose the missing schema action "AddWithKey" when you fill the
> dataset .
> This way when you do the merge , the dataset object will me able to
> recognize similar tables an instead of creating new ones it will add all
> the data in similer tables into one big table.
> Hope this would help ; however , if this is not what you are up to ,
> please explain more
> Thanks
>