Datagridview from two different datasets

Hey ya'll, I could really use some help.

I'm working with a very poorly designed database (out of my control to change or alter, I can only query it), and as a result I have two datasets (each from a different database query) that I need to combine based on 3 similar fields. The database will not run the query I need that would join the 3 tables together so I am attempting to work around that.

I have datasetA from which I created datatableA that has the following columns: reportnumber, corenumber, subsample, size, name

datasetB from which I created datatableB that has the following columns: reportnumber, corenumber, subsample, depth, density, velocity, susceptibiliy, impedence

Is there anyway I can combine the two datatables into one datagridview? Thanks for any and all help!

How about:
===========
dim sql as string = "select * from A,B where a.reportnumber=b.reportnumber and a.corenumber=b.corenumber and a.subsample=b.subsample"
AnAdapter = new sqldataadapter(sql,yourconnection)
Atable = new datatable
Anadapter.fill(Atable)
YourDataGridview.Datasource = Atable
============
Modify to show the columns you want.

You could create a empty datatable, like

Code:

---

        Dim dt As New DataTable
        dt.Columns.Add("corenumber")
        dt.Columns.Add("subsample")

---

then use a FOR loop to load the records you want into the the new datatable.
Then use that datatable as the dgv datasource.

I'm really sorry it took me so long to respond. Still doing a lot of hurricane recovery down here in Louisiana.

faceint, thanks for the suggestion but I have tried that and the size of my query (I posted a very modified version here to save space) overloads the database and kicks me out of it.

wes4dbt, how would I go about loading the records from both datatables to one so that the records will match up, not repeat?

Also, is there anyway to have a dataset formed by two different queries?

duplicate

duplicate

It would be something like this,

[CODE]
Dim dt As New DataTable
dt.Columns.Add("corenumber",Type.GetType("System.String"))
dt.Columns.Add("subsample",Type.GetType("System.String"))

For Each r as DataRow in DataTableA.Rows
' add "r" to the NewDataTable
Next

For Each r as DataRow in DataTableB.Rows
dim dr as DataRow() = NewDataTable.Select( "reportnumber = '" & r("reportnumber").tostring & "'")
if dr.GetUpperbounds(0)<> -1 Then
'edit row
Else
' add new row to NewDataTable
End If
Next

I used ReportNumber as a way of find a unique record but that was just a guess. I don't know what you would use to locate a specific record. That would depend on how the tables were designed.

So, wes4dbt, you're saying create a third datatable and load the rows from the others to it? How would you suggest doing that with three common columns instead of just one? (Again, the database design is terrible)

Quote:

---

How would you suggest doing that with three common columns instead of just one?

---

Just use the "AND" operator,

Code:

---

dr = ds.groups.Select("groupid='" & var1 & "' and userid='" & var2 & "' and crop='" & var3 & "'")

---

I noticed in your other post that you created a new column that is an expression of the three columns. Now you could just,

Code:

---

dr = ds.yourdatatable.Select("unique='" & r("unique").ToString & "'")

---