Binding to Global DataSet

[Arg81]

I'm either having a blonde day or a bad day, I haven't decided yet...

I use a global dataset to store all of my customer information so that all forms access it without having to load the data everytime. This works great.
I load all of the tableAdapters on my main menu and then set varDsCustomer = me.dsCustomer
The 3 tables are Customer, CustomerSite and CustomerContact

I then have a search form. By typing in a string into a box and searching, it will list all matching customers in the first combobox. This is done with;

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] expCustName = [/SIZE][SIZE=2][COLOR=#a31515]"CustomerName LIKE '"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtSearchCust.Text & [/SIZE][SIZE=2][COLOR=#a31515]"%'"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sortCustName = [/SIZE][SIZE=2][COLOR=#a31515]"CustomerName ASC"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cboCustomer
.DataSource = varDSCustomer.Tables([/SIZE][SIZE=2][COLOR=#a31515]"Customer"[/COLOR][/SIZE][SIZE=2]).Select(expCustName, sortCustName)
.DisplayMember = [/SIZE][SIZE=2][COLOR=#a31515]"CustomerName"
[/COLOR][/SIZE][SIZE=2].ValueMember = [/SIZE][SIZE=2][COLOR=#a31515]"CustomerID"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE]

However, I want the 2nd combobox to only show Sites for the selected customer (on the press of a button). I've read that I need to set the display member and value member to that of my DataSet Relation, and I have done so using;

[/COLOR][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2][COLOR=#000000] [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2][COLOR=#000000].cboCustomerSite[/COLOR]
.DataSource = varDSCustomer
.DisplayMember = [/SIZE][SIZE=2][COLOR=#a31515]"Customer.Customer_CustomerSite.CustomerSiteName"
[/COLOR][/SIZE][SIZE=2].ValueMember = [/SIZE][SIZE=2][COLOR=#a31515]"Customer.Customer_CustomerSite.CustomerSiteID"
[/COLOR][/SIZE][SIZE=2].Focus()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]

But the first site(s) in the Site table are listed. This is because my Customer and customerSite tables are linked by CustomerID. For some reason, when the Customer combobox is filled, the CustomerID seems to be staying at "1".

How do I use the CustomerID from the Customer combobox (so when the customer changes, so does the ID obviously) to then sort the 2nd combobox?
Or is this not possible when using Global DataSets this way?
[/SIZE]

 

[cjard]

I use a global dataset to store all of my customer information so that all forms access it without having to load the data everytime.

Actually, that's what the database is for. Load what you need, not everything! Forms typically deal with different datas, so the concept of a global DS is rather moot

I load all of the tableAdapters on my main menu and then set varDsCustomer = me.dsCustomer

Probably not what I'd do..

The 3 tables are Customer, CustomerSite and CustomerContact

I then have a search form. By typing in a string into a box and searching, it will list all matching customers in the first combobox. This is done with;

[SIZE=2][COLOR=#0000ff]With [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cboCustomer[/SIZE]
[SIZE=2].DataSource = varDSCustomer.Tables([/SIZE][SIZE=2][COLOR=#a31515]"Customer"[/COLOR][/SIZE][SIZE=2]).Select(expCustName, [/SIZE]

I wasnt aware that arrays of datarow could be used in this fashion. Typically, you would use a fill for this op, asking the database to provide only matching customers. Using a dataset like a database is a performance-poor decision because youre essentially asking a dataset to be something that it is not.

ps, in type-specific code, this should be:

[SIZE=2][COLOR=#0000ff]With [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cboCustomer[/SIZE]
[SIZE=2].DataSource = varDSCustomer.Customer.[/SIZE][SIZE=2]Select(expCustName, [/SIZE]

However, I want the 2nd combobox to only show Sites for the selected customer (on the press of a button). I've read that I need to set the display member and value member to that of my DataSet Relation, and I have done so using;

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2][COLOR=#000000].cboCustomerSite[/COLOR][/SIZE]
[SIZE=2].DataSource = varDSCustomer[/SIZE]
[SIZE=2].DisplayMember = [/SIZE][SIZE=2][COLOR=#a31515]"Customer.Customer_CustomerSite.CustomerSiteName"[/COLOR][/SIZE]
[SIZE=2].ValueMember = [/SIZE][SIZE=2][COLOR=#a31515]"Customer.Customer_CustomerSite.CustomerSiteID"[/COLOR][/SIZE]
[SIZE=2].Focus()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE]
[/COLOR][/SIZE]

I dont think you can do this, because relational data operations are usually performed by a binding source.

You'd have one combo bound through a BS to a table, fill the table with search results, then iterate it, and fill the related tables with info.. The other combo is bound through a BS to the relation exposure of the first BS

Or you can fill-only-relevant-children upon selection (better in this case I think)

Or is this not possible when using Global DataSets this way?

I wasnt even aware that people considered using datasets globally, for information that changes. Lookup table info that is static, sure, use a global DS, but not data like this...

 

[Arg81]

I wasnt even aware that people considered using datasets globally, for information that changes. Lookup table info that is static, sure, use a global DS, but not data like this...

That's the thing...it was originally static lookup data.

The reason I put it globally is because my app is actually split into 2 systems (same database) - both of these share the same customer resources.

The idea was load all of the customer information at start so it's there and it's done. No matter what part of the app you are accessing, either creating new records or browsing through historically, the customer info, sites and the contact names are already loaded and the app doesn't need to call back to the database each time for them.

However I've now made a customer admin form. Because this is bound to the dsCustomer dataSet, any changes now affect my varDsCustomer dataset.

The global customer dataset should show all - but that also includes any "new" rows added using the admin form. To do this I obviously need to update the dataSet and then call varDsCustomer = me.dsCustomer again....and then I lose all of the CustomerSites because to fill the customerSite combobox I was using a tableadapter and fillbyCustomerID.......

Think I need to replan the approach. Like I say, a user browses historically so the CustomerName, CustomerSite and a customercontact needs to display on each row. Historically the user cannot edit the data as there is no need to.
But then our Sales Team can open the "customer relations" module and mark a customer as inactive, a site as inactive and a contact as inactive. If any of these are inactive, the specific comboboxes when adding a new record to either of the systems should not display them, only active ones.
A Sales rep can also add new contacts and sites, and edit the current infomation accordingly.

^^ I've gone on a bit there but I need to be able to do all of this with as minimal database calls and with the highest performance.
(Hence why I thought long-run that global dataset would be better).

Oh, and when I use

.DataSource = varDSCustomer.Tables("Customer").Select(expCustName, sortCustName)

It's great for manipulating static data (I use alot on my Employee global dataset)

 

[cjard]

... are already loaded and the app doesn't need to call back to the database each time for them.

"only in america, do we leave cars worth thousands of dollars on the driveway, and have a garage full of worthless junk"

?

Database is like a hard disk. DataSet is like Ram. Why dont we see computers being sold with 512 gigbytes of ram and a 512mb hard disk (and never switch them off)?

Think I need to replan the approach.

yes, you see TableAdapters arent really designed for accessing the same data in two databases. You can give a table adapter its own connection string, so if your Customers are in db1, and your Orders are in db2, you can cope with it, but it is absolute nonsense to split your customer table across two database instances from a management point of view, and then try to manipulate them simultaneously

DataSets are flexible, but they dont stretch that far! If your co really must persist with this idea of splitting tables across database instances, then you need to, at least, have the database do the donkey work..
i.e one database hosts a bunch of linked tables: it looks like they are in db1 but each time it asks db2 to perform the select

^^ I've gone on a bit there but I need to be able to do all of this with as minimal database calls and with the highest performance.

Minimal database calls doesnt necessarily infer that performance will be better. Databases are very good at storing, finding, sorting and indexing data. Thats why microsoft spent $millions on SQL server and only $thousands on datasets.
DataSets are not a sqlserver replacement!

Keep your database calls like you eat; a little and often. Eat too much, infrequently, and you get fat (and slow). Keep your queries lightweight and transfer only the data you need. When the manager asks to see all 500,000 records in a grid in the client, say "NO"

 

[Arg81]

no it's 1 database but 2 "systems"....when I first did this in VS2003 I actually had 2 apps, but now I've merged them together. The customer table, and the 2 "system" tables are all in the same SQL DB.

Today, the recoding starts

 

[cjard]

for such a task, I'd design a new structure, import the old data to develop with, develop, finish, stop the old, import the old, proceed with the new...

 

[Arg81]

exactly what I'm doing

Just some modifications such as the customer admin screen was never in the old app...

...Well I got it all sorted now, no more Customer global dataset, just calls as and when needed on the various forms and then a couple of tweaks so that comboboxes update themselves if someone has marked a site / contact as non-active..

Thanks for the feedback.