AjW
asked on
Copy Dataset column data to multiple sources
Hi,
I have a question regarding datasets.
I have a dataset with several datatables. One or more of these tables will be required at runtime. Most of these tables have columns (and data) in common, and they will all end up with the same number of rows.
I am using datagridviewrows to allow the user to populate the tables with data. To prevent the user having to repeatedly enter the same information into several tables I reckon I should;
1 - create a new dataset
2 - iterate through my existing tables and look for duplicate columns
3 - create a new column in my new dataset for every duplicate column
4 - display the new dataset in a datagridviewrow
The user enters the data once into the displayed datagridview. I would like the data in the column to be replicated to the datatables with a matching column.
Am I right in thinking that the only way to do this will be to copy the individual data items one-by-one in a For..Next for each table? I've looked but cannot find (understandably) a way to link a single column to multiple destinations.
Any other ideas? I know it sounds daft having repeated data in a dataset but user might need just one table, or several.
Adam
ps, I'm currently using XML to store sample data and saved dataset data. Maximum no. of records is likely to be <100.
I have a question regarding datasets.
I have a dataset with several datatables. One or more of these tables will be required at runtime. Most of these tables have columns (and data) in common, and they will all end up with the same number of rows.
I am using datagridviewrows to allow the user to populate the tables with data. To prevent the user having to repeatedly enter the same information into several tables I reckon I should;
1 - create a new dataset
2 - iterate through my existing tables and look for duplicate columns
3 - create a new column in my new dataset for every duplicate column
4 - display the new dataset in a datagridviewrow
The user enters the data once into the displayed datagridview. I would like the data in the column to be replicated to the datatables with a matching column.
Am I right in thinking that the only way to do this will be to copy the individual data items one-by-one in a For..Next for each table? I've looked but cannot find (understandably) a way to link a single column to multiple destinations.
Any other ideas? I know it sounds daft having repeated data in a dataset but user might need just one table, or several.
Adam
ps, I'm currently using XML to store sample data and saved dataset data. Maximum no. of records is likely to be <100.
Howard Cantrell
just use inner joins in your sql statement before you load your dataset.
ASKER
Thanks planocx, though there is no data so dont see how INNER JOIN would work. I define the datasets in an xsd file, and use them to generate datagridviews. The user populates the Dgvr and then the data from a single column should end up replicated into several other datatables' columns that are finally saved as XML.
I've not used much SQL before but thanks for pointing me at it; the SELECT INTO statement would seem to work; I'll have a play.
AjW
I've not used much SQL before but thanks for pointing me at it; the SELECT INTO statement would seem to work; I'll have a play.
AjW
I would suggest creating a custom DataColumn class for your 'super' DataTable and maybe even a custom DataTable class. The reason behind this would be to add code that would:
* when a column is being added to the 'super' DataTable, record which DataTable it originally came from, then check if column already exists and use existing if found or add as new otherwise.
* when a row value is being added, check if you already have a value but throw an exception if different to what you already have (presumably that shouldn't happen)
* when a row has changes, loop through each column in the row of the super datatable checking for changes and if a value has changed you can find which datatable it originally came from as you've already saved this to the datacolumn object.
Thus you would have smaller standard datasets, and a larger superset which can transfer its changes back.
* when a column is being added to the 'super' DataTable, record which DataTable it originally came from, then check if column already exists and use existing if found or add as new otherwise.
* when a row value is being added, check if you already have a value but throw an exception if different to what you already have (presumably that shouldn't happen)
* when a row has changes, loop through each column in the row of the super datatable checking for changes and if a value has changed you can find which datatable it originally came from as you've already saved this to the datacolumn object.
Thus you would have smaller standard datasets, and a larger superset which can transfer its changes back.
ASKER
Hi, and thanks for the reply. There's an added factor I didnt mention above;
I'm going to contextualise the problem and you'll see why I'm having trouble. Bear with me and see if it makes sense!
client has many products for sale, and sells them through a variety of finance packages.
I have several tables defined in an xsd;
- Product (Reference / Description)
- Finance Method1 (Reference / Price / Initial Payment / Interest Rate / Repayment Term / Monthly repayment / Total repaid)
- Finance Method2 (Reference / Price / Initial Payment / Interest Rate / Repayment Term / Monthly repayment / Total repaid)
- Finance Method3 (Reference / Price / Initial Payment / Interest Rate / Repayment Term / Monthly repayment / Total repaid)
There is no parent/child thing going on here; every row in the Products table will have a corresponding row in the Finance tables;
The Product table is bound a datagridview and either;
a single column is added to that datagridview for 'Price' and the row values are then replicated in the Finance tables
OR
three columns are added to the datagrid view to allow separate 'Price' values to be used for each product and for each finance method.
(the choice of 1 or 3 columns is made at run time via option boxes and the user should be able to switch between 'views')
This can also apply to the Initial Payment / Interest Rate / Repayment Term columns. The client can also choose to start with blank tables, or load data, in which case I use ReadXml to populate the tables.
To complicate things the 'Monthly repayment' and 'Total repaid' are Expression columns and are summed dynamically with the total going to textboxes to show the result of any changes to the tables. This is the real problem as it means I have to continually update my datatables from the datagridviews.
If I can create a bindingsource that links to several tables then the updating and recalculation is quite straightforward as I can use the bindingsource as the datagridview datasource, but I cant find out how to do this.
If I use another dataset as suggested above as an intermediary and then use that as a datasource then I'll have to monitor any editing in the gridview and reflect that back to the dataset, and then through to the other datatables. I'd have to create 4 columns (three containing the individual price column values, and one for the other option of one column to many), and show/hide the columns as needed at run time.
I'm guessing that this is the only way to do it?
The other option of course is to create one MAAAASSIVE table of everything and just show/hide columns as needed :o) Hell, it'd get the job done in the short term..
Oh yes, I've upped the points, cos you probably deserve 'em just for getting this far!
- A
VB.NET, VS 2005
I'm going to contextualise the problem and you'll see why I'm having trouble. Bear with me and see if it makes sense!
client has many products for sale, and sells them through a variety of finance packages.
I have several tables defined in an xsd;
- Product (Reference / Description)
- Finance Method1 (Reference / Price / Initial Payment / Interest Rate / Repayment Term / Monthly repayment / Total repaid)
- Finance Method2 (Reference / Price / Initial Payment / Interest Rate / Repayment Term / Monthly repayment / Total repaid)
- Finance Method3 (Reference / Price / Initial Payment / Interest Rate / Repayment Term / Monthly repayment / Total repaid)
There is no parent/child thing going on here; every row in the Products table will have a corresponding row in the Finance tables;
The Product table is bound a datagridview and either;
a single column is added to that datagridview for 'Price' and the row values are then replicated in the Finance tables
OR
three columns are added to the datagrid view to allow separate 'Price' values to be used for each product and for each finance method.
(the choice of 1 or 3 columns is made at run time via option boxes and the user should be able to switch between 'views')
This can also apply to the Initial Payment / Interest Rate / Repayment Term columns. The client can also choose to start with blank tables, or load data, in which case I use ReadXml to populate the tables.
To complicate things the 'Monthly repayment' and 'Total repaid' are Expression columns and are summed dynamically with the total going to textboxes to show the result of any changes to the tables. This is the real problem as it means I have to continually update my datatables from the datagridviews.
If I can create a bindingsource that links to several tables then the updating and recalculation is quite straightforward as I can use the bindingsource as the datagridview datasource, but I cant find out how to do this.
If I use another dataset as suggested above as an intermediary and then use that as a datasource then I'll have to monitor any editing in the gridview and reflect that back to the dataset, and then through to the other datatables. I'd have to create 4 columns (three containing the individual price column values, and one for the other option of one column to many), and show/hide the columns as needed at run time.
I'm guessing that this is the only way to do it?
The other option of course is to create one MAAAASSIVE table of everything and just show/hide columns as needed :o) Hell, it'd get the job done in the short term..
Oh yes, I've upped the points, cos you probably deserve 'em just for getting this far!
- A
VB.NET, VS 2005
Could you knock a sample together to demonstrate what you have and comment how you'd like it to actually work?
Why can't you check for the existence or visibility of the extra two columns and if not found assume the price from the first one? Presumbly you have the calculation of the totals in a method so that shouldn't be too diffficult.
With regards to monitoring the changes and reflecting back, each row has a state that indicates whether it has been changed so that wouldn't be too difficult to do.
Why can't you check for the existence or visibility of the extra two columns and if not found assume the price from the first one? Presumbly you have the calculation of the totals in a method so that shouldn't be too diffficult.
With regards to monitoring the changes and reflecting back, each row has a state that indicates whether it has been changed so that wouldn't be too difficult to do.
ASKER
I've knocked together a demo app.
Either load some example products or enter some in the left hand grid. The opposing DGV should contain the same number of rows to allow for a price/prices to be entered.
I've added comments onto the form - does it make sense?
Either load some example products or enter some in the left hand grid. The opposing DGV should contain the same number of rows to allow for a price/prices to be entered.
I've added comments onto the form - does it make sense?
ASKER
Ah, well, I have a demoi app, but I can't upload it. There's a link to it here;
[link]www.dread-it.co.uk/demoapp.zip[/link]
[link]www.dread-it.co.uk/demoapp.zip[/link]
OK. I have played around and this is what I got to... let me know what you think and if I shot off in the completely wrong direction....
http://cid-479ffddff28e963e.skydrive.live.com/self.aspx/DemoCode/DemoApp%202008-07-16.zip
There aren't that many comments and I did update your dataset to recognise fields as being auto numbered and readonly where appropriate. See what you make of that demo beast and it'll give me time to run for the hills ;-)
http://cid-479ffddff28e963e.skydrive.live.com/self.aspx/DemoCode/DemoApp%202008-07-16.zip
There aren't that many comments and I did update your dataset to recognise fields as being auto numbered and readonly where appropriate. See what you make of that demo beast and it'll give me time to run for the hills ;-)
ASKER
Heyup,
cheers for the app and all the time you put into it; I've had a play with it, and I think I know the direction you're heading. I think I might have complicated things with my description!
I can do it perfectly in Excel;
[link]www.dread-it.co.uk/demosheet.xls[/link]
(But I'm trying to get out of spreadsheet land with this app as our clients have too many win platforms for me to support them by myself)
I've a feeling what I want isn't going to be that easy using a single datagridview; but I'm going to peruse your code a bit closer and see what I can use!
cheers for the app and all the time you put into it; I've had a play with it, and I think I know the direction you're heading. I think I might have complicated things with my description!
I can do it perfectly in Excel;
[link]www.dread-it.co.uk/demosheet.xls[/link]
(But I'm trying to get out of spreadsheet land with this app as our clients have too many win platforms for me to support them by myself)
I've a feeling what I want isn't going to be that easy using a single datagridview; but I'm going to peruse your code a bit closer and see what I can use!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, been busy.
I've had a look through your code GHCS Mark, and reckon that there's bits in there that will useful. I've not implemented a full solution, but I think code based combination of tabales will be easier than looking to do something through the dataset designer; My datatables will have less than 100 rows.
Thanks for all your work in helping me out; I'll award ya the points
Adam
I've had a look through your code GHCS Mark, and reckon that there's bits in there that will useful. I've not implemented a full solution, but I think code based combination of tabales will be easier than looking to do something through the dataset designer; My datatables will have less than 100 rows.
Thanks for all your work in helping me out; I'll award ya the points
Adam