Synced databinding between multiple grids

[Jim]

Hi,

I have an app that I wrote using business objects as datasource, but that I
am considering rewriting using DataSets, etc. instead. I am looking for
some guidance to make this decision.

The app is for scheduling payments of invoices and has three grids (regular
DataGridViews):
A Vendor grid showing a summary view of each vendor (with total Due, etc.)
An Invoice grid listing all invoices for the selected vendor (including a
total amount of payments scheduled for each invoice)
A Payment grid listing all payments scheduled for the selected Invoice.

Any payments added / removed or modifed in the payment grid (or mass-updated
via code) needs to be reflected instantly in the other two grids.

What is the best way to accomplish this?

Thanks,
Jim

Ps. Note that both the Vendor grid and the Invoice grid need aggregate
values (TotalDue, PaymentTotal, etc.)

 

[Cor Ligthert[MVP]]

Jim,

Your datatables needs in your datasets needs to have relation.

Now the datasources of your childtables become the relations.

In fact is that all you need to do.

Here a very short tip how to do that.
http://www.vb-tips.com/DataGridRelation.aspx

Cor

 

[Jim]

Cor,

Your datatables needs in your datasets needs to have relation. Now the
datasources of your childtables become the relations.
In fact is that all you need to do.

Yes, that takes care of showing the Invoices for the selected Vendor (for
example), but here is the problem: The vendor grid (parent) is based on the
same DB table as the invoice grid (the children), but the vendor grid is a
summary view and the invoice grid is a "non-summary" view.

The DB tables are:
Invoice (VendNo, InvNo, DueDate, Amount)
Payment (VendNo, InvNo, SeqNo, PayDate, PayAmount)

The Vendor Grid should contain the equivalent of this simplified SQL
statement:
SELECT Invoice.VendNo, SUM(Invoice.BalDue) as TotalDue,
SUM(Payment.PayAmount) as TotalPay
FROM Invoice LEFT OUTER JOIN Payment ON
Invoice.VendNo = Payment.VendNo AND
Invoice.InvNo = Payment.InvNo
GROUP BY Invoice.VendNo

I could of course do this as a view on the DB server and use that for the
vendor grid and use the Invoice table for the Invoice grid, but how do you
keep them in sync when payments are added / deleted or modified from the
invoices? Would I have to persist the data to the DB and rebind the grids?
It would probably be too slow.

Thanks,
Jim

 

[Cor Ligthert[MVP]]

Jim,

Why not 2 resultsets one with your invoice grouped by vendNo, InvNO and one
for the payments?

Cor

 

[Jim]

Cor,

Why not 2 resultsets one with your invoice grouped by vendNo, InvNO and
one for the payments?

Could you please elaborate on that and how it would solve the problem I
brought up in my last post?

Thanks,
Jim

 

[frebe]

The DB tables are:

Invoice (VendNo, InvNo, DueDate, Amount)
Payment (VendNo, InvNo, SeqNo, PayDate, PayAmount)

The Vendor Grid should contain the equivalent of this simplified SQL
statement:
SELECT Invoice.VendNo, SUM(Invoice.BalDue) as TotalDue,
SUM(Payment.PayAmount) as TotalPay
FROM Invoice LEFT OUTER JOIN Payment ON
 Invoice.VendNo = Payment.VendNo AND
 Invoice.InvNo = Payment.InvNo
GROUP BY Invoice.VendNo

I could of course do this as a view on the DB server and use that for the
vendor grid and use the Invoice table for the Invoice grid, but how do you
keep them in sync when payments are added / deleted or modified from the
invoices?  Would I have to persist the data to the DB and rebind the grids?
It would probably be too slow.

If this is too slow in your environment, you should consider changing
environment. After inserting a row in the payment table, the summary
SQL above need to re-run and the corresponding grid to be updated. It
should be a matter of millis.

//frebe