I am trying to resolve a problem. I have the following.

One dataset, we'll call dsPatientInformation

Table #1 inside dsPatientInformation called tbPatientDemo.
tbPatientDemo source comes from a SQL DB and has fields,
PatientID, PatientName, PatientState

Table #2 inside dsPatientInformation called tbPatientBalance.
tbPatientBalance source comes from ACCESS and has two fields,
PatientID, PatientBalance

I need data to end up in the following format,
PatientID, PatientName, PatientState, PatientBalance

Now yes I could add a new column to Table #1 called PatientBalance, then loop through the rows of Table #1 and perform a lookup on Table #2 to insert the patient balance into my new column.

Using a merge would just append the records from Table #2 into Table #1, that would not solve my problem.

I would love to be able to perform a query of
Select Table1.PatientID, Table1.PatientName, Table1.PatientState, Table2.PatientBalance From Table1 Join On Table2 Where Table1.PatientID = Table2.PatientID
against the two tables within the dataset, but I cannot find any method to do this.

Any assistance would be appreciated.

Hi,

I think your solution is to move everything to XML from the DataSet and then transform into a new XML - the one that you need - using XSLT

I think you would have to full fill your tables in the dataset direct from the sources you have - Sql and access. After that you can create a relation between those tables in the dataset ( remeber it is disconected data by now, so the data source is not importante any more ). After this, you save all the information as a xml document, and the transforms it into a new XML using a xslt - you have to write your own xslt -, then you read the xml from a table and you will have the information in the format you want

Best Regards

I have exactly the same problem, as I am sure do a lot of people since this seems a fairly trypical action.

Is this still the only way to do this? It seems completely daft that a 'disconnected model of the DB' won't let you use the most basic SQL commands that exist in a real connected DB!

Thank you for any help you can give.

Hi,

another option can be given by the merge method of a dataset, but you have to see if it "fits" in what you want to do

Here is a link in msdn for checking the DataSet.Merge Method