Using Arrays to Calculate - old post revisited

[Arg81]

Using Arrays to Calculate - RESOLVED

In my .net 1.1 App, jmcilhinney helped me put together a routine that allowed me to display 2 "time" calculations on my form.

The original post can be found here:
http://www.vbdotnetforums.com/showthread.php?t=4579

Here is the code I'm using

[code]
Private Sub CalculateTime()
 
        Dim requestTimes As New SortedList
        Dim requestProperties As ArrayList
        Dim revisionTimes As SortedList
 
        For Each row As DsDevelopment.DWR_WorkDataRow In Me.DsDevelopment.DWR_Work.Rows
            If requestTimes.ContainsKey(row.DWRNumber) Then
                'Get the existing entry for this request
                requestProperties = DirectCast(requestTimes(row("DWRNumber")), ArrayList)
            Else
                'add a new entry for this request
                requestProperties = New ArrayList(New Object() {0D, New SortedList})
                requestTimes.Add(row("DWRNumber"), requestProperties)
            End If
 
            'add the time spent to the running total of the request
            requestProperties(0) = CDec(requestProperties(0)) + CDec(row("TimeSpent"))
 
            'get the list of times for each revision for the current request
            revisionTimes = DirectCast(requestProperties(1), SortedList)
 
            If revisionTimes.ContainsKey(row("RevisionNumber")) Then
                'add the time spent to the running total for the revision
                revisionTimes(row("RevisionNumber")) = CDec(revisionTimes(row("RevisionNumber"))) + CDec(row("TimeSpent"))
            Else
                'add a new entry for this revision
                revisionTimes.Add(row("RevisionNumber"), CDec(row("TimeSpent")))
            End If
        Next row
 
 
        If Me.DsDevelopment.DWR_Work.Rows.Count = 0 Then
            Me.lblTSTimeDWR.Text = 0
        Else
            If Me.lblTSDWR.Text <> String.Empty AndAlso Me.lblTSRevNo.Text <> String.Empty Then
                Me.lblTSTimeDWR.Text = DirectCast(requestTimes(CInt(Me.lblTSDWR.Text)), ArrayList)(0).ToString()
            End If
        End If
 
        If grdEmployeeHours.Columns("TimeSpent").Text = "" Then
            Me.lblTSRevTime.Text = 0
        Else
            If Me.lblTSDWR.Text <> String.Empty AndAlso Me.lblTSRevNo.Text <> String.Empty Then
                Me.lblTSRevTime.Text = DirectCast(DirectCast(requestTimes(CInt(Me.lblTSDWR.Text)), ArrayList)(1), SortedList)(CInt(Me.lblTSRevNo.Text)).ToString()
            End If
        End If
 
    End Sub

However, now that I'm redeveloping my app in .net 2.0, this routine no longer works, and I get the error;

[FONT=Arial][COLOR=#0000ff]at System.Int16.CompareTo(Object value)[/COLOR][/FONT]
[FONT=Arial][COLOR=#0000ff]at System.Collections.Comparer.Compare(Object a, Object b)[/COLOR][/FONT]
[FONT=Arial][COLOR=#0000ff]at System.Array.BinarySearch(Array array, Int32 index, Int32 length, Object value, IComparer comparer)</ExceptionString></InnerException></Exception></TraceRecord>[/COLOR][/FONT]

"Failed to compare two elements in the array."

Can anyone please offer some advice on this?

Regards,


PS - there may be another way in .net 2.0 , I've attached a screenshot to show exactly what I am trying to acomplish.

 

[cjard]

(btw, you seem to have a fair idea of what youre doing with the relations so i do have great faith that youll sort it out one way or another.. i just want you to know the easy way!)

 

[Arg81]

sigh....I was having one of those days...

That's how I thought it would be, expand a datatable and if any relations it will show the related datatable in there to expand and use.

Somehow I was missing that earlier, I look now and I see it.

Either a) I'm tired or b) Completely stupid.

At the moment I'm going with the latter I knew I bound the label wrong, for some reason I couldn't make myself believe it was wrong?!?

 

[cjard]

sometimes i spend a day answering other peoples questions and the answer to my own hits me (its a brain-rest thing) - usually a good thing because when i get stuck, and i cant find the answer on google...

...well, there's only JohnH can help me

 

[Arg81]

I'm so going to cry.....

the binding is fine. If I load the 2 dataTables at form_load then the relationship sorts out the labels and they show the correct values. Therefore I know the SQL queries and the dataTable relationships are OK.

However, I thought I'd try to be clever and load the dataTables with parameters using iteration (like you showed me with a previous post). this just doesn't want to work!!!!

So search form, loads DWRs with @DWRNumber.
Then, as you showed me, with iteration load Revisions for all the loaded DWRs and then let the dataRelations sort out the data.

For Each r As dsDevelopment.DWRRow In Me.DsDevelopment.DWR
                If r IsNot Nothing Then
                    Me.DWR_RevisionTableAdapter.FillByDWRNumber(Me.DsDevelopment.DWR_Revision, r.DWRNumber)
                End If
            Next

Then I iterate all work rows for revisions (Work rows are the time rows)

For Each r As dsDevelopment.DWR_RevisionRow In Me.DsDevelopment.DWR_Revision
                If r IsNot Nothing Then
                    Me.DWR_WorkTableAdapter.FillByDWRNumber(Me.DsDevelopment.DWR_Work, r.DWRNumber)
                End If
            Next

The above all works fine. It's great - one connection to the DB and all the data is loaded. The user can browse through all the rows and I'm safe in the knowledge that they aren't connecting to the DB everytime they change a parent row to get the child data.

So I thought I'd be clever.....I assumed that the TSDWR and TSRev tables would be loaded after the Work - because once this has been filled it contains all the rows I need to know, for example;

DWRNumber ---- RevisionNumber ---- EmployeeID ---- TimeSpent
30000------------1---------------------15-------------1
30000------------1---------------------2--------------3
30000------------2---------------------18-------------2.5
30000------------2---------------------10-------------4
30000------------1---------------------9--------------5

So......To load the total time of the DWR, I would use @DWRNumber. In the case of the example above it will iterate through the rows and the results would be;
30000 - 10.5
30001 - 5
using the query

SELECT DWRNumber, sum(TimeSpent) as TSDWR
FROM DWR_Work
WHERE DWRNumber = @DWRNumber
GROUP BY DWRNumber

Then to work out the total time of each revision for each DWR iterated, I would use @DWRNumber and @RevisionNumber. In the case of the example above the results would be;
30000 - 1 - 4
30000 - 2 - 6.5
30001 - 1 - 5
using the query

SELECT DWRNumber, RevisionNumber, sum(TimeSpent) AS TSRev
FROM DWR_Work
WHERE (DWRNumber = @DWRNumber) AND (RevisionNumber = @RevisionNumber)
GROUP BY DWRNumber, RevisionNumber

Previewing Data on the dataTables comes back with the correct values I'm expecting.

I'm trying to fill these tables using the data from the DWR_Work table once that has been filled, using the code;

For Each r As dsDevelopment.DWR_WorkRow In Me.DsDevelopment.DWR_Work
                If r IsNot Nothing Then
                    Me.TSDWRTableAdapter.FillTSDWR(Me.DsDevelopment.TSDWR, r.DWRNumber)
                    Me.TSREVTableAdapter.FillTSREV(Me.DsDevelopment.TSREV, r.DWRNumber, r.RevisionNumber)
                End If
            Next

This looks fine to me!! It should load all the correct values into the dataTables because the rows exist in the DWR_Work table already.
BUT - the labels are not showing these correct values when doing it this way. So the problem is with loading the data, and I can't see what I've done wrong.

As I say, I've got around it and tested it by loading ALL data into the 2 time dataTables at form_load and it works as expected.
I'd rather the data be loaded like the rest of the tables for obvious reasons.

/cry!!

 

[cjard]

just, out of curiosity, have you looked at the contents of the datatable?

you do this:

For Each r As dsDevelopment.DWR_WorkRow In Me.DsDevelopment.DWR_Work
                If r IsNot Nothing Then
                    Me.TSDWRTableAdapter.FillTSDWR(Me.DsDevelopment.TSDWR, r.DWRNumber)
                    Me.TSREVTableAdapter.FillTSREV(Me.DsDevelopment.TSREV, r.DWRNumber, r.RevisionNumber)
                End If
            Next

To load the summary data.

Well, put a breakpoint on after this block (if there is nothing then just do a useless line of code like Dim s as string = "") and then point to your dataset variable Me.DsDevelopment

A tooltip appears and there is a tiny magnifying glass icon. Click it and use the visualizer to see your datatable data.. Does it all "look" correct?


I get the feeling there *may* be repeated rows in one of the tables, but i'm not sure, or if this would cause a problem..

however..

Look at the data like this, and then look at it how it loads if you fill all upon form load.. Can you work out theerror in the way the data is loaded from looking at what is loaded?

 

[Arg81]

I get the feeling there *may* be repeated rows in one of the tables, but i'm not sure, or if this would cause a problem..

Yeah you are right there - DWR_Work.

Example from the beginning load;

load DWR
DWRNumber : 30000

load Revisions
DWR Number: 30000 RevisionNumber: 1
DWR Number: 30000 RevisionNumber: 2

load Work
WorkID: 1 DWR Number: 30000 Revision Number: 1 Employee: 1 TimeSpent: 1.5
WorkID: 2 DWR Number: 30000 Revision Number: 1 Employee: 2 TimeSpent: 2

^^ You can see here, in DWR_Work, 2 rows exist for DWR 30000, Revision 1, it's only the employee and the time spent that are different between them.

- So is it because it's seeing multiple DWR+Rev rows in the DWR_Work table that the For....Next loop is failing?

EDIT - I've played about with the data in the SQL table, and I can see where it's going wrong on the iteration.

If the DWR_Work table has 3 rows;

DWRNumber RevisionNumber EmployeeID TimeSpent
30000--------------1--------------1------------1.5
30000--------------1--------------2------------1
30000--------------1--------------3------------2.5

Then the dataTable loads fine and the only row to appear is

DWRNumber RevisionNumber TimeSpent
30000--------------1------------5


HOWEVER....if the DWR_Work Table has DWR's with more than 1 revision, these do not appear at all in the dataTable. For instance;

DWRNumber RevisionNumber EmployeeID TimeSpent
30000--------------1--------------1------------1.5
30000--------------1--------------2------------1
30000--------------1--------------3------------2.5
30001--------------1--------------1------------3
30001--------------2--------------1------------2

The the dataTable will still only show DWRNumber 30000 in it, there will not be any rows for 30001.

That answers my previous question of

for some reason only 1 row appears in both tables, both with the same DWR number.
Even if 3 parent rows are loaded (3 DWR's), only this 1 row appears, even if all 3 DWRs have rows in the DWR_Work table....

So....something here doesn't like the iteration For....Next loop to fill these dataTables correctly.

 

[Arg81]

shoot me now!!

TableAdapter...clear before fill....

enough said

The row that was always loading was the last DWR to be loaded, i.e. if 30000, 30004, 30007 were loaded, the dataTable would only show the row for 30007. I just assumed the issue was with the other DWRs because they had more than 1 Revision

Oh well, we live and learn so they say....

 

[cjard]

Your new table adapters, did you remember to clearbeforefill = false them too?

 

[cjard]

oh.. i see you have the solution.. I didnt realise this thread had gone onto two pages..

 

[Arg81]

me neither, I kind of wrote a lot of rubbish, I'm sure I can clean some of the posts up

Your new table adapters, did you remember to clearbeforefill = false them too?

...It was because I deleted and regenerated them, completely forgot to change to FALSE.

 

[Arg81]

OK, bringing this one back from the dead.

When I add a new row to the time grid (so I add a name and a number to the 2 columns), the values displayed for the time are not updated until I reload the data from the database.

So I added the sub to reload my data (everything gets loaded under Private Sub LoadData).

The only problem with this is that if from my parent table I'm on row 30/40, reloading takes me back to 1.

Is there a way that either;
(a) When I update the time grid the values from the dataTable update (I assume this can't be, and that's why in my old app I was using the array version as posted)
(b) Reload the data but stay on the same row. This actually does on my old app (VS2003) when calling "UpdateDataSet" then "LoadDataSet" that VS generated in the wizards...

Thanks

 

[cjard]

OK, bringing this one back from the dead.

When I add a new row to the time grid (so I add a name and a number to the 2 columns), the values displayed for the time are not updated until I reload the data from the database.

Of course not - the database does the summing and then you load this related data into a separate table. If you add a new row to the client the database doesnt know about it yet.

What you would do is update the database, and then reload the sums. BindingSource.ResetBindings(false) can be used if the stuff didnt update immediately.

everything gets loaded

yeah, dont reload everything because the client side details table is already up-to-date w.r.t the database.. only the sums need reloading. also if someone else has edited their details grid and it affects your sums, their actions will be reflected in your client, so reloading the sums is a good idea

when you have

a - related to -> b - related to -> c

and you change b, a doesnt need reloading



another way to look at it.. if you have people standing in a pyramid such that water can flow down the outside and make them wet, if you start water flowing out half way up, only the people from the bottom up to half way need their umbrellas - why would you make even those at the top get their umbrella out?

 

[Arg81]

nice anology

It was because I had all the loading code under one sub that I said it would load everything...however, I've now broken each section into it's own sub, and call the relevant sub from each one....so now once the grid has been updated, I can just call the "time" sub

cheers for your help (again)