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....

 

[pachjo]

erm .... lost for words!

Yes I recall having the problem with the imediate window and tried the same approach but did not get as far as customizing the menus.

Anyway I still have no idea why the have gone as I installed VS.NET PRo and started using it?

My main method to debug is to put a break in the code or message boxes so have never used the immediate window, exceptions etc before these latest posts.

hangs his head in shame ....

 

[cjard]

My main method to debug is to put ... message boxes ...

Oh, dude.. That is SO '90s!


I think you need to format your machine and start again with a clean install of VS.. Next you'll be telling us there is no "Save" option on the "File" menu.. Your machine seems a bit wacky!

 

[pachjo]

Now now don't take the p!

My setup was a fresh install so why bits are missing who knows!

Maybe it is so 90's but has done me good for most developments.

Anyway with the latest config it stopped at the error and allowed me to select editing to which I received a really helpful message the "blah blah no stack etc etc not available here.....have you tried using the messagebox approach?"

 

[cjard]

It's not always possible to unwind the call stack to allow the debugger access to a particular frame. Put a breakpoint instead, and make sure you do NOT have "debug just my code" ticked on

 

[pachjo]

Not that I am over sensitive, but it occurs to me that perhaps you think I only use messagebox to debug my apps?

Probably because that is what I said which is true in part, but not complete!

I do use breakpoints and I do step thru code and query the values of objects and variables etc but I also use messagebox when the need arises.

What I have never done is use the rewind or immediate window. It is this section that is new to me.

There I feel so much better, now you dont think I'm such a pillock after all, well not a complete one anyway!

 

[cjard]

Once upon a time, when I was coding java in a text editor, console writes were all I had, to perform debug... I remember that pain, though it did make me a better coder because I thought more about the logic of my program. I still do a lot of this with PLSQL because there is no edit and continue..

Now that I'm used to visual environments and stateful inspection, I dont think I've ever used them since.. THere is a huge amount that the debugger can do; keep scratching at it! Conditional breakpoints, watches, early exception catching.. It's all in there