merge two datasources and bind to gridview (MS Access backend). Nothing happens?

Hi,

I've seen this this problem on the internet but just can't seem to get it working. I'm filling two datasources, and i can see they are filling ok cause i can set the gridview to

grdStatusReports.DataSource = ds.Tables(0) or
grdStatusReports.DataSource = ds.Tables(1)

and so I can see that the two tables have filled without problem. But, what I actually want to do is merge the two tables into one table and then bind to the gridview. I'm probably doing something ridiculous but so far I've tried everything i could see on the internet like setting AcceptChangesDuringFill = False or calling HasChanges() , calling Update() and just nothing happens. No matter what I do the tables are not merged... or if they are i don't know how to get to that...

any help?
Dim cnnADO As New OleDbConnection(strConnection)
strSql = "TRANSFORM Avg([CSAT Scores]) AS [CSAT Avg]"
strSql = strSql + " SELECT [Company Name]"
strSql = strSql + " FROM [CSAT Data]"
strSql = strSql + " GROUP BY [Company Name]"
strSql = strSql + " ORDER BY [Company Name]"
strSql = strSql + " PIVOT Quarter"

da = New OleDbDataAdapter(strSql, cnnADO) 'adapter deals with connection open and closing methods
da.AcceptChangesDuringFill = False
da.Fill(ds, "Table(myTable1)") 'Fill Dataset

blnCheck = ds.HasChanges()

strSql = "TRANSFORM Avg([CSAT Scores]) AS [CSAT Avg]"
strSql = strSql + " SELECT [Company Name]"
strSql = strSql + " FROM [CSAT History]"
strSql = strSql + " GROUP BY [Company Name]"
strSql = strSql + " ORDER BY [Company Name]"
strSql = strSql + " PIVOT Quarter"

'Add another table to dataset
Dim cmd As New OleDbCommand(strSql, cnnADO)
da.SelectCommand = cmd

da.Fill(ds, "Table(myTable2)") 'Fill Dataset

blnCheck = ds.HasChanges()

ds.Tables(0).Merge(ds.Tables(1))
           
ds.AcceptChanges()
blnCheck = ds.HasChanges()
da.Update(ds.Tables(0))

blnCheck = ds.HasChanges()

grdStatusReports.DataSource = ds.Tables(0)
grdStatusReports.DataBind()

Open in new window

AidenAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

silemoneCommented:
0
AidenAAuthor Commented:
hi, no that didn't help, was the first page i found on web also...
0
foobarrCommented:
1) Is it possible to merge both datasets from MS Acess instead?

2) What you can do is create your own datatable from the two datasets

http://www.devasp.net/net/articles/display/184.html -> To create datatable

For your datasets...

for each drow as datarow in ds.tables(0)
   'then using datatable example
  datatable.rows.add(drow("column1"), drow("column2"), etc.etc.
next

for each drow as datarow in ds.tables(1)
   'then using datatable example
  datatable.rows.add(drow("column1"), drow("column2"), etc.etc.
next


then bind that to your grid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AidenAAuthor Commented:
yeah i solved this anyway, not sure what i changed even, think i was probably just doing something silly.

but i decided to do it in MS Access anyway, where i create a transform query which runs on a union query and then calling that in code and selecting particular data from it, which seemed to produce the results i was looking for.

so, i'll give some marks there anyway for suggesting a similar approach,

thanks, Aiden
0
AidenAAuthor Commented:
came up with a solution myself and solved initial query
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.