Reading, Querying, then Summing a Text Data File

[Sarah_123]

Apologizing in advance for my lack of knowledge and thanking you in advance for your help.

My task is to read in a large file of text (fixed-width), and sum an amount in each record (filtered by the user using a form). Example, the user checks the "Male" checkbox and the "NewYork" checkbox, and the program adds the "Amount" for all males in New York and pops out that number.

So far, my program reads the text file in and stores it into a datatable which is defined in the code, but after that I'm pretty lost. I have tried researching it, but it seems like most references are connecting to Access or Oracle and using a OleDBDataAdaptor.

I am using 2005 Express Edition, if this helps.

I haven't given enough information, please let me know.

 

[cjard]

You can add another two columns to your datatable and set their expressions to:


<filterCol>.Expression = string.Format("Iif(sex = '{0}' AND city = '{1}', amount, 0)", txtGenderToSearch.Text, txtCityToSearch.Text)
<sumCol>.Expression = "SUM(name_of_filterCol)"

I dont kno if you can combine and nest these.. try it:
oneCol.Expression = string.Format("SUM(Iif(sex = '{0}' AND city = '{1}', amount, 0))", txtGenderToSearch.Text, txtCityToSearch.Text)


or you can do the sums yourself:

DIm sum as Integer = 0 '(heh.. dim sum, yum yum)
foreach(d as DataRow in myDataTable.Select( string.Format("sex = '{0}' AND city = '{1}' ", txtGenderToSearch.Text, txtCityToSearch.Text) )
  sum += Convert.ToInt32(d("amount")) 'sum the amount column

More info l;ook up DataColumn.Expression property in MSDN

 

[Sarah_123]

Thank you!

 

[Sarah_123]

For Each dat As DataRow In dt.Select(String.Format(QueryString))
SumExpoAmt += Convert.ToDecimal(dat("ExposureAmount"))
SumExpoNum += Convert.ToDecimal(dat("ExposureNumber"))
Next

I am throwing a NullReferenceException on the "For Each..." line.
Does this just mean that my query string isn't finding any matches?
Because even when I set it to where I know it should find something, it doesn't.

So is there an error in the above code.... or in the way I'm building the string?

 

[cjard]

dt is Nothing

 

[cjard]

ps; please take a read of:

http://www.vbdotnetforums.com/showpost.php?p=80102&postcount=12

I might eventually get round to writing a "how to sue the debugger" tutorial but for now, please also read:

http://www.startvbdotnet.com/forms/debug1.aspx
http://www.murach.com/books/bvbn/ch7ex1.htm
http://visualbasic.about.com/library/courses/blecvbsbs0902.htm
http://www.developerfusion.co.uk/show/4692/

There are a raft of simple errors that you ought to be able to identify yourself, much like looking a word up in the dictionary rather than asking us all the time for the definition. using the debugger is that process of looking up, rather than asking us

 

[Sarah_123]

I know how to use the debugger.
(I'm sure there are things I do not know, but I know the basics.)

But when I'm in the debugger, and I hover, it does not show me that dt = nothing.
(nowhere that dt is referenced)
It shows nothing but the exception.
And when I try to step through, it does not show me the value then either.

That's why I was I was confused on whether it was dt or the collection "dt.Select..."

Sorry

 

[JohnH]

At least if you're using Express version you have to find where the object variable is declared (or last assigned) and hover that, which could be a local Dim, loop iterator, the method parameter or a private class variable. If there is a chance in code an object could logically happen to null ref at different times you should add a check for this before using it, or perhaps Try-Catch some.

 

[Sarah_123]

When I try-catch, everything was null, which didn't help.

The problem is fixed.

The problem was... when I passed the reference from one sub to another, I had a typo ("dy" for "dt")

This was the problem =

ByRef dy As DataTable

So when I hovered, the object wasn't a null ref. , but it didn't successfully pass my object to the other sub...

oops.

Anyway, thanks for the help.... but I promise I know how to hover! Just not type, evidentally.

 

[cjard]

OK, few points though...

You have to select then hover in VB.. otherwise it will try and evaluate the whole expression youre hovering.. in your case "dt.Select(String.Format(QueryString))"

It sounds like you should program with Option Strict and Option Explicit turned ON - one of the biggest problems with VB and the root causes of it being viewed as a dummy language for noobs and low grade programmers is because it is far too slack about variable/type defintions by default. Turning these on helps prevent errors where you pass the wrong types, and fail to declare variable names before you use them

You seem to have passed your DataTable ByRef.. can you tell me why you did this?

 

[tinboen]

Use this link http://filehelpers.sourceforge.net/ to read the fixed-width records.