Solved

Copy Dataset column data to multiple sources

Posted on 2008-06-25
12
1,165 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now