Iterating through rows - Need Urgent Help!

[Arg81]

OK, as said in previous posts on here I iterate through rows to then load data from other tables so that only the data that is needed is required.

However...I've found either a stumbling block or a bug! I really need some help on this, as it didn't show in testing stage but now I've copied all data over, it's affecting live (which starts Monday...)

To load the customer sites and customer contacts, both are related via CustomerID.

To load the sites, I look at all the parent rows that have been loaded, then load the customer sites based on the customersiteid column

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] LoadCustomerSiteData([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] ClearFirst [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] ClearFirst = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSite.Clear()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSite.BeginLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] dsDevelopment.DWRRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsDevelopment.DWR[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]IsNot[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].CustomerSiteTableAdapter.FillByCustomerSiteID([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSite, r.CustomerSiteID)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSite.EndLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Exception[/SIZE]
[SIZE=2]MessageBox.Show(ex.Message)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

Now that the sites have been loaded, I look at the CustomerID column of the rows and load the contacts based on that ID

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2][COLOR=#000000] LoadCustomerContactData([/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2][COLOR=#000000] clearfirst [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Boolean[/COLOR][/SIZE][SIZE=2][COLOR=#000000])[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] clearfirst = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerContact.Clear()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerContact.BeginLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] dsCustomerDetails.CustomerSiteRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerSite[/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] r [/SIZE][SIZE=2][COLOR=#0000ff]IsNot[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].CustomerContactTableAdapter.FillByCustomerID([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerContact, r.CustomerID)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DsCustomerDetails.CustomerContact.EndLoadData()[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Exception[/SIZE]
[SIZE=2]MessageBox.Show(ex.Message)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[/COLOR][/SIZE]

Once loaded, I'm getting a "constraints" error, and at first thought the problem was the contacts not matching the customerID correctly.

Using the dataSet visualiser, I get the following output:

Customer ID
87
228
238
237
225
81
188
245
235
81
172
234


^^ 81 appears twice, because 2 sites were loaded for the same customer (a customer can have many sites, a customer can have many contacts)

Now, when it gets to loading the contacts, it iterates the rows above, loads all of the contacts to 235, then bombs on 81. No other row after that has any contacts loaded.

QUESTION:
I thought the customerID can exist more than once, and when iterating it will just reload the same contacts (thus overwriting the ones from previous), but obviously not.

(a) How can I get it so it ignores if the ID exists twice or
(b) Make it overwrite if the ID exists again?

Any help would be greatly appriciated!!!

EDIT: To show what I mean I've attached a couple of screenshots. thankfully the customer database level isn't confidential, although I'll remove the screenshots if I get this fixed

CustomerSite.jpg - you can see that CustomerID 81 gets loaded twice (but two different CustomerSiteID and CustomerSiteName) - these all load OK.

customerContact.jpg - you can see that all the customerContacts get loaded OK until it reaches the CustomerID before the 2nd 81. This is where it's bombing out.

Testing this, it all works OK if the rows loaded don't have sites in common. If I load 10 rows, each with a different CustomerID then it's all OK. Therefore I know it's something to do when it finds the same customerID....

 

[cjard]

This post is confusing

WHen loading related data, you have to load it in order of relation, parent to child


You havent posted the code that loads the DWR rows.

You dont say where that list of customer IDs comes from..

I've tried to make sense of it, but I cant, sorry..

Are you saying you:

Load DWR
Iterate DWR, loading CustomerSite
Iterate CustomerSite, loading CustomerContact

Relationships are supposed to be 1:M, not M:N

If A -> B via customer ID and B->C via customer ID, and Customer ID is only a PK of A, then it should be:
A->B
A->C

If CustomerID is a pk of other tables too, then they should be the same table as A!
i.e. if A's PK = custID and b's PK = custID, then A:B is 1:1 and all the columns of B should be appended to B, and B dropped

-

a quick hack around this is to, instead of:
For Each row in DWR
siteTA.Fill(siteDT, r.ID)

Do this:
For Each row in DWR
siteDT.Merge(siteTA.GetData(r.ID))

Understand that it will be much slower.. You really need to sort out your relationships because many-many shouldnt exist!

 

[cjard]

ps, looking at the screenshots, yes you have 81 in there twice but they are different sites! hopefully the siteID is part PK of that table too, because if customer site's pk is just customer ID then you got a broken database.

Now, because customer site PK is compound, it should ONLY be used to load data for other tables where they also have a site ID+cust ID that isnt all of the PK.. Hopefully this is making sense!

Tables who share a PK should be as one
Tables who are filled from related parent data should not have all their PK columns used in that search (Cust SIte should have custID and siteID as pk, but only custID is used, from iteration of dwr, to fill the data)
Tables who have a PK that extends beyond the search parameters should not have parts of their PK used for fills( cust site has a PK of custID, siteID, you should NOT iterate it on just custID and use that custID to fill another table, because it is NOT guaranteed UNIQUE. as you see you have 2 sites with same cust, but diff site. You are using DWR to fill Site, so why would you want to use Site to fill Contact? Use the original one you filled site with! i.e. DWR)

 

[Arg81]

Yes your right...DWR table is (short version) DWRNumber (PK) , CustomerSiteID, CustomerContactID , blah , blah-blah , blah-blah-blah....

Obviously I can load the CustomerContacts with;

[COLOR=#0000ff]For[SIZE=2]Each[/SIZE][SIZE=2] r [/SIZE][SIZE=2]As[/SIZE][SIZE=2] dsDevelopment.DWRRow [/SIZE][SIZE=2]In [/SIZE][/COLOR][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][COLOR=#0000ff][SIZE=2].DsDevelopment.DWR[/SIZE][/COLOR]
[COLOR=#0000ff]
[SIZE=2]If[/SIZE][SIZE=2] r [/SIZE][SIZE=2]IsNot[/SIZE][/COLOR][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[COLOR=#0000ff][/COLOR]
[COLOR=#0000ff][SIZE=2]Me[/SIZE][SIZE=2].CustomerContactTableAdapter.FillByCustomerContactID([/SIZE][SIZE=2]Me[/SIZE][SIZE=2].DsCustomerDetails.CustomerContact, r.CustomerContactID)[/SIZE]
[SIZE=2]End[/SIZE][/COLOR][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[COLOR=#0000ff][SIZE=2]Next[/SIZE][/COLOR]
[COLOR=#0000ff]

[/COLOR]
Don't know why I never thought of that yesterday, probably cause I was panicing...

This "iterating" is all new to me. I like it, but obviously for some reason misunderstood one of your previous posts about it and was loading the contacts from the site, as both have CustomerID as an FK.

FYI, to answer another one of your questions:

Customer Top Level (1) --------- (many) Customer Sites

Customer Top Level (1) --------- (many) Customer Contacts

The contacts aren't directly related to the sites, as a contact "hops" around. CustomerID is PK in Customer, but FK in CustomerSite and CustomerContact. CustomerSite PK = CustomerSiteID and CustomerContact PK = CustomerContactID.

Thank's for pointing out the problem....hopefully going to be a very stress free day, I've still to import my second app into all of this

Regards,

 

[Arg81]

argh!?!?!?!?!

I'm now getting the "failed to enable constraints. One or more rows contain values......." error!
It's popping up when the parent table loads (DWR) but doesn't happen on every account manager...only some.

However, previewing the data returns all the rows correctly....

 

[cjard]

Hangon while a rip out some of my code to a DLL for you...

 

[Arg81]

sorted!!

Apparently it's a dumb VS2005 error (so I say!)

Even though I had re-created the DWR dataTable, and recreated all the queries, it was still keeping one column "CreatedBy" as maxlength 30. However, this was set to 50 in my SQL table.

Changed the dataTable value, and the error has gone.

Moral of the story is;

(a) always say no to management when pressured to create a new version
(b) remind Microsoft to change their stupid error coding

 

[cjard]

More like a dumb user error, that change the lenght of the SQL column and didnt refresh the dataset properly hahememehemhemcoughsplutter


Anyway, i have made a DLL for you. Reference it, and then when your app crashes, write this in the immediate window:

Nitpick.DataSets.WhatsWrong(YOUR_DATASET_INSTANCE_VARIABLE)


It will print out to the Output window, stuff like:
"In DWR table, column XYZ has a m,ax lenght of 30. Value abceflfjdksjdfgkjsdfhgkjlsdhfhsdsfasdfasdfsadf' violates this limit

 

[Arg81]

awesome. Thanks for that.

But as I say....I deleted the DWR dataTable (and it's queries) and then recreated it. How come it didn't then refresh the dataset size of the newly created one to match that of the SQL? Is there a hidden file somewhere?!?

 

[cjard]

Dont think so.. normally that would be sufficient.. I'd have deleted the table, saved the dataset and re-run the custom tool, then added it back, to make sure!

 

[pachjo]

cjard

this is what I get when I try to run nitpick?

Command "Nitpick.DataSets.WhatsWrong(ds,s)" is not valid

can you advise please?

Thanks

 

[cjard]

It's not an error message that I recognise, i'm afraid. Can you take a screenshot?

 

[pachjo]

Hi it might be that I was using the command window and not the immediate window!

But this is what I type in the immediate window now:

nitpick.DataSets.WhatsWrong(ds,s)

And I get this:

'nitpick.DataSets.WhatsWrong' is not declared or the module containing it is not loaded in the debugging session.

I do not have s delared anywhere and not sure really what I am doing as this is the first time I have used this window?

 

[pachjo]

I found that I needed to put > in front of the command but now I am back to the original error, see the image attached:

 

[cjard]

ok

you can only use in the immediate window, something that looks like a line of code that would work in the source at that point!!


So, write Nitpick.DataSets.WhatsWrong(ds,s) into the code under the yello line

and look at the wiggles


s is a stringbuilder. You will have to declare one. ds is the dataset, i dont know what you called yours. References must be added to Nitpick.
I dont really want to get into a big discussion teaching you how to use the debugger, partly because it's so simple, partly because someone else must have written one. You never used the immediate window? That's amazing

try this:

?1+2

?myStringVariable

?myDataSet.Tables.Count