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!
Re: Datagridview from two different datasets
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.
Re: Datagridview from two different datasets
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.
Re: Datagridview from two different datasets
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?
Re: Datagridview from two different datasets
Also, is there anyway to have a dataset formed by two different queries?
Re: Datagridview from two different datasets
Re: Datagridview from two different datasets
Re: Datagridview from two different datasets
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.
Re: Datagridview from two different datasets
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)
Re: Datagridview from two different datasets
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 & "'")