Combining one dataset with different dataadapters

[tommesbx]

Ok, this is driving me nuts: I want to fill my dataset with data from different tables through ONE oleDbDataAdapter. The code-fragment looks like this:

oleDBConn = New OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + source)
oleDBDataAdap = New OleDb.OleDbDataAdapter(initSelect, oledbConn)
oleDBDataAdap.MissingSchemaAction = MissingSchemaAction.AddWithKey
oleDBDataAdap.Fill(ds, table1)
oleDBDataAdap.Fill(ds, table2)

Two severe problems here:

1) I just cant find the fitting SQL-clause. I want the data from two different tables out of the database. If im setting initSelect like "SELECT * FROM table1; SELECT * FROM table2;", ive got an error message. So I tried "SELECT * FROM table1, table2". Even though this worked, the dataset is filled in a strange way. Every datarow from each table is put in four(!) times?! (Even using "SELECT DISTINCT" didnt work)

There has to be a way to do this by the help of only one dataAdapter, or am I wrong? I know I can use different adapters for each table, but this is kind of unhandy.

Next thing I tried was to use the oleDBCommand with different SQL-clauses. To make it short, this also lead me to nowhere.

One additional problem: you can see I used the .Fill-command two times. If I changed these lines to "oleDbDataAdap.fill(ds)" (which was my first attempt), the dataset would simply stay empty. Is there another, more "clever" way to update my dataset with the content of every table?

2) Can I write back to content through this ONE adapter simply using the .update-method? Examples, perhabs?

Any help appreciated!

Greets
Thomas

 

[stustarz]

Why dont you create a query or stored procedure on the database side of your application that combines the data from the two tables, then use the data adapter to retrieve records from your new query.

If this isnt possible you will need to write the SQL using an inner or outer join on the two table fields that are related.

If you know what I mean