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]

dont but > in.. that makes it a command

commands are for the IDE, stuff like:

>Debug.SetRadix(16)


Will make all numbers in tooltips etc appear in hex

 

[pachjo]

Not getting anywhere with this I'm afraid.

I have always debugged by either using messagebox to display bits, or a break point to check values or remove error trap block to check state of play, so up until this I have not needed the immediate window (well did not know of it!)

I know you all this this odd, but hey that's me! I heard the other day that there is a box you can put food in and it uses rays or something to cook the food! .... a microwave I think they called it !

Anyway back to nitpick as I can see this could be of great help!

Here is the code:

dim s as system.text.stringbuilder

da.fill(ds.poimports)

at da.fill line I get the error System.Data.OleDb.OleDbException "no value given for one or more parameters" was unhandled and stops with line highlighted in yellow.

I want to see what the problem is as I don't have aclue.

So in the immediate window I type Nitpick.DataSets.WhatsWrong(ds, s) and hit return and the yellow line goes blue and says "Object reference not set to an instance of an object."

???

Is nitpick supposed to do something?

 

[Arg81]

Something that you are selecting on your Fill SQL has a name that SQL uses...this means that it won't run and you need to either
(a) edit the code so the column that is having the problem is in [] , i.e. [name]
(b) edit the DB so you change the column name at that level, and then refresh your tableAdapter by configuring the SQL to take out the old invalid name and add the new column....

thats what that error usually means anyway..

 

[cjard]

Is nitpick supposed to do something?

Yeah

"Object ref not set" is an elementary error message, meaning you attempted to use a variable that was null. Point to s - what does the tooltip say. Point to ds - what does the tooltip say?

i.e. if you wrote this line of code:

Nitpick.DataSets.WhatsWrong(ds, s)


something is null.. It's not Nitpick. nor is it DataSets. so it must be ds or s

 

[pachjo]

Arg81, thanks that solved the problem

The numpties had changed a column name in the source spreadsheet and that was what was causing the error, so [name] fixed it

cjard, I think I will leave that as all I seem to get from nippick is errors which I am then trying to trace, when I actually wanted to trace the original error in my app.

I appreciate nitpick works, alas for whatever reason not for me, I'm still going round in circles trying to get it to work!

 

[cjard]

cjard, I think I will leave that as all I seem to get from nippick is errors which I am then trying to trace, when I actually wanted to trace the original error in my app.

I appreciate nitpick works, alas for whatever reason not for me,

It's a GIGO problem; garbage in, garbage out. You pass a null StringBuilder to Nitpick. Sorry it didnt work out for you, but it really was a criminally simple omission in your code..

 

[pachjo]

True, but even after I had rectified this I still got nothing out.

nuff said

 

[cjard]

Yeah, you kinda need to investigate the contents of the StringBuilder using the debugger, cause it doesnt jsut flash stuff up on screen - developer tool, not an end user one

 

[pachjo]

Here I am again with another possible use for nitpick and I just cannot let it go!

So, if I may pick your brains cjard?

I have a method which I call that belongs to a third party piece of software which is causing me grief.

Basically there is an object which has a databindingsource set as its datasource and the method in question has a number of tableadapters used a parameters in its call.

The method writes the contents of all the tableadapters back to the database.

All thru my code this method works without fault until a certain situation occurs.

When I call this method I get the horrid error 'object not set to.....'

Now I run the demo that came with the product and my app in parallel debugging and see no difference as to what is causing the problem.

So here I am 2:00am on Friday morning after spending the last god knows how many days banging my head on the desk and thought.....nitpick!

Questions are as follows:

Will nitpick work if the error is generated by a line like this:

object.savedata(ta,ta,ta,ta,ta)

where object has a datasetbindingsource as its datasource and ta are diferent tableadapters.

If yes then here is where I get to:

nitpick is referenced in my project

This is the code

dim s as system.text.stringbuilder = new system.text.stringbuilder

object.savedata(ta,ta,ta,ta,ta)

And the crash occurs on the object.savedata line with a highlight in yellow.

I open the immediate window and type this:

?s.tostring which show me nothing as I would expect

I then type

Nitpick.DataSets.WhatsWrong(myDataSet, s)

and get

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

Well I have it loaded in the project and I get intelli type when entering it in the immediate window?

So as it is obvious that when it comes to this and everyone is having a good ole laugh at my ability to suss this .....any chance you could throw me a bone?

Thanks ...... in hope!

 

[cjard]

That's very, very weird
. how about putting the nitpick into the actual program

try


  object.savedata(ta,ta,ta,ta,ta)

catch Exception
  dim s as system.text.stringbuilder = new system.text.stringbuilder

  Nitpick.DataSets.WhatsWrong(myDataSet, s) 
end try

set a breakpoint on the Dim s ... line


Also what you can try is go on the Debug menu, click Exceptions, and put a tick next to "CLR Exceptions" in the THROWN column..

that way, as soon as an error is thrown, not jsut in your code but anywhere.. you should see the exact line the error occurred on and you can find out which variable is null and trace back from there..
NitPick typically only is used for finding out what is wrong with data in a dataset,like when it says "one or more rows violate constraints" or soemthing like that..

Note you can also tick in the advanced options, the option "debug just my code" take that OFF, and you can step into all the designer generated code which can be a BIG help..

i.e. "hmm.. why is the third parameter of that tableadapter INSERT command, set to nothing? trace back.."

 

[pachjo]

Hi,

When I put nitpick in the catch block and then step thru s gets set to something.

I type ?s.tostring in the immediate window and see this:

"Beginning examination of myDataSet
DataSet has no errors
"
I am sure there is something different with my install as I don't see the options you refer to.

I have looked in Debug on the toolbar

I have looked in Debug on the project properties page

And I have looked under tools - options - debugging

In none of these do I see CLR Exceptions

Under the tools - options - debugging the closest I can find is "Enable Just My Code (managed only)?

After so many days of my searching my code etc and the support of the third party having a copy of my project they found I had a typo on one of the DataMember entries in design view.

I would never have found that in a month of Sundays but am sure there must be a way, almost certainly the one you describe that would have shown me this some time ago.

All I need to figure out is how to set this environment up to allow me to do it!!!

 

[cjard]

Debug onthe toolbar? You mean the Debug menu?

Well, my environment is set up for C# mode so the menu layouts MAY be different, but I used to use VB mode and for sure the Debug>>Exceptions WAS in there. in VB Express 2008 the shortcut is Ctrl+Alt+E; try it?


Seriously though.. have a look on the debug MENU, look at all the children options.. one of them Will be called "Exceptions"
If this fails, look through EVERY menu?

Maybe someone with your version of VS in VB mode can see it.

This is NO a toolbar button, it's a menu item. If you right click any toolbar and choose Customize, COmmands tab, Debug in th left pane, Exceptions IS in the right pane..you can drag it onto a toolbar

 

[pachjo]

Right the Debug menu definitely does not have Exceptions as an option and I cannot find this option anywhere.

Have looked thru all menus and no sign of it.

Ctrl+Alt+E brought it up, never seenthis dialog before?

But hey have now set the Throne for CLR but all that does is hightlght the problem line in blue rather than yellow lol

I then opened up the view toolbars and added the exceptions command to the Debug toolbar (the one with the play button on) so at least I can access the thing now even tho it did not do much for me!

 

[cjard]

OK.. well, I'm not really tech support for Visual Studio, all i'm saying is:

When "Thrown" is active, then AS SOON AS AN EXCEPTION OCCURS in code that you can read (i.e. not in the disaaembly of the windows api or external dlls) it is shown to you at the deepest level possible.

Without "Thrown" ticked, it is shown to you at the highest level possible, if there is no try/catch, or the code view jumps into the catch block if there IS a try/catch


I dont know what "highlight the line in blue" means; for me it highlights in green, and I can then choose to "Enable editing" (unwind the stack to this frame in C# terms) and edit the problematic code. I can also enable the debugger window called Call Stack and see all the methods that were called to reach this point..

The debugger really can help you here, but it does seem that you only really know how to use about 1% of it. I wish I knew a good tutorial for it all, but I have only my natural curiousity to thank for what I know If you do find a good tut, let us know?!

 

[JohnH]

pachjo knows how to get the "Exceptions..." item back to the Debug menu if he lost it: http://www.vbdotnetforums.com/showthread.php?t=24164
It's there by default in VB 2005 Express.