Solved

Copy Dataset column data to multiple sources

Posted on 2008-06-25
12
1,169 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:AjW
  • 6
  • 4
12 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 21865150
just use inner joins in your sql statement before you load your dataset.
0
 

Author Comment

by:AjW
ID: 21865510
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
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21865608
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:AjW
ID: 21962329
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


0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 21962969
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.
0
 

Author Comment

by:AjW
ID: 22015551
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?
0
 

Author Comment

by:AjW
ID: 22015659
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]
0
 
LVL 3

Expert Comment

by:GHCS_Mark
ID: 22020735
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 ;-)
0
 

Author Comment

by:AjW
ID: 22089387
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!
0
 
LVL 3

Accepted Solution

by:
GHCS_Mark earned 500 total points
ID: 22090090
In that case, you'd probably want to simplify what I have by just adding or hiding the columns that you want to display when they switch between the modes... However, when they edit a cell you would just detected the column name they were under and if its single price, update the other three...  

I'm not sure you can set a DataGridViewColumn hidden but if you can then just do that, otherwise you will need to replicate the column building code (which can be found in the Form.Designer.vb) and remove/add the columns you want visible.

Unfortunately, in the nxet week or so I have a lot on at work and personally so I won't be able to modify the code until after that.
0
 

Author Comment

by:AjW
ID: 22251207
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question