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]

An aside:

the line:
For Each row As DataRow In Me.DsDevelopment.DWR_Work.Rows


This implies that a typed dataset is in use, so the signature should be:

For Each row As DsDevelopment.DWR_WorkDataRow In Me.DsDevelopment.DWR_Work.Rows



This then allows:
If requestTimes.ContainsKey(row.DWRNumber) Then


i.e. typed vs untyped row addressing



-
Now, can you repost the code using the basic editor? Click this button:

Wait for the reply box to turn white. then paste the code between code tags. I'd prefer black and white indented code, to coloured left-slammed code

 

[cjard]

ps.. typically we use the database to calculate toals, sums, averages etc

but you can bully a DataTable into showing it:
http://support.microsoft.com/kb/325685

 

[cjard]

sorry, try:

DsDevelopment.DWR_WorkRow


Havbe a look in the intellisense, for whatever is the type sepcific row for the DWR_Work datatable

 

[cjard]

note that when you go type specific, all the horrible stuff like this:

revisionTimes(row("RevisionNumber")) = CDec(revisionTimes(row("RevisionNumber"))) + CDec(row("TimeSpent"))

can(read: should) become:
revisionTimes(row.RevisionNumber) += row.TimeSpent


manually, of course

 

[cjard]

now that we have generics in .net 2 we can do:

            Dim requestTimes as New Dictionary(Of Integer, Decimal)
            Dim totalTime As Integer
 
...
 
            If requestTimes.ContainsKey(row.DWRNumber) Then
                'just add the time stright into the dictionary
                requestTimes(row.DWRNumber) += row.RequestTime
            Else
                'add a new entry for this request
                requestTimes(row.DWRNumber) = row.RequestTime
            End If

I couldnt see the need for having sorted lsits of arraylists of values that would be added up.. Simply put in your dictionary, the Key as what you want to group by, and the Value is a summation of the group

If you want to sum all values, then you can:

For Each d as Decimal in requestTimes.Values
tempD += d
...

 

[Arg81]

The reason none of the sums are done in the database is because as the user clicks through the revisions in the grid, the label changes value to that of the time spent on that revision (the total of the time in the smaller grid on the screenshot).

I did assume it was DsDevelopment.DWR_WorkRow and adjusted as necessary.


You have to bear in mind that knowledge of things like this are very low with me. I comfortably understand how to connect to a DB and add, edit, delete etc, but when running arrays and stuff like that I thank god I'm not a full time programmer!!
On that note, I don't really understand your last post

 

[cjard]

On that note, I don't really understand your last post

Which bits?

 

[Arg81]

  Dim requestTimes as New Dictionary(Of Integer, Decimal)
            Dim totalTime As Integer
 
...
 
            If requestTimes.ContainsKey(row.DWRNumber) Then
                'just add the time stright into the dictionary
                requestTimes(row.DWRNumber) += row.RequestTime
            Else
                'add a new entry for this request
                requestTimes(row.DWRNumber) = row.RequestTime
            End If

Where would this go in relation to the existing code? Or does these few lines replace the existing code?

You probably can see or already know what I'm trying to do...Each Project has Revisions. I need (Label A) to total the time spend column for all the rows in the DWR_Work table that group together for the given DWRNumber (ID)
I then need (Label B) to total the time spent column for all the rows in the DWR_Work table that group together for the given RevisionNumber for the given DWRNumber....
Then when the user browses through the data (navigational buttons) or looks through the revisions (by clicking rows on the grid), the labels update because the button.click and grid.rowchange events call the CalculateTime sub

I just assumed I couldn't do this at database level....

 

[cjard]

  Dim requestTimes as New Dictionary(Of Integer, Decimal)
            Dim totalTime As Integer
 
...
 
            If requestTimes.ContainsKey(row.DWRNumber) Then
                'just add the time stright into the dictionary
                requestTimes(row.DWRNumber) += row.RequestTime
            Else
                'add a new entry for this request
                requestTimes(row.DWRNumber) = row.RequestTime
            End If

Where would this go in relation to the existing code? Or does these few lines replace the existing code?

It replaces it - if you understand how dictionary works, then you can read this snippet and see where it would go

A Dictionary(Of x,y) is a lookup, where X is used to look up Y
If you had strings for X, and numbers for Y, then you can say things like:

dict("Number1") = 1
dict("two") = 2

dict("two") resolves to an integer without needing to cast it and hence it can be added to, divided etc:

Dim i as integer = dict("Number1") + dict("two")
'i is now 3


if you can understand this:

Dim dict_Two as Integer
dict_Two = 2



then you can understand this:

Dim dict as Dictionary(Of String, Integer)
dict("two") = 2



After all, youve been doing it all this time with rows:

dataRow("ColumnName") = columnValue



-

I just assumed I couldn't do this at database level....

I would prefer it be done at the database level, actually..

I'd have another datatable in the set, who is generated by the query:

SELECT revision, sum(timeSPent) as sum_of_time FROM dwr_whatever GROUP BY revision

Fill this table, and ensure it is linked with a relation, to whichever of the others, it needs to be linked to..

(I know nothing about your database structure, i cannot write the quereies)

So basically, you get the database to fo the grouping and summing, selecting the sum results into a table that you associate with the real data.

Additionally, did you see the Expression property of a DataColumn? Its yet another way to skin this cat...

 

[Arg81]

OK I started the database approach last night....

I have created 2 datatables (1 for time DWR and 1 for time Revision), and my select queries are;

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

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


Now, previewing data and manually putting in the variables works perfectly.

I've added relations to from these 2 datatables back to my revision table, and I'm trying to load the correct data in via iteration like you showed me with loading revisions so when browsing through the rows the application doesn't have to keep connecting to the database.

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)
                    Me.TSDWRTableAdapter.FillTSDWR(Me.DsDevelopment.TSDWR, r.DWRNumber)
                    Me.TSREVTableAdapter.FillTSREV(Me.DsDevelopment.TSREV, r.DWRNumber, r.RevisionNumber)
                End If
            Next
            Me.DsDevelopment.DWR_Work.EndLoadData()

I've then databound the 2 labels to the correct fields in the 2 new datatables.

However these labels don't update when browing through (I thought they would what with the relation in place) and only show the total time for the first DWR loaded, and the revision time for the first revision loaded.....

me = confused a lot.

Sorry this is dragging on a bit....

 

[cjard]

Nono, its fine.. it simply sounds like the binding is wrong


When you dragged these labels onto the form, did you drag them from the main datasource entry or the nested datasource entry? Here is a pic to show you what i mean

 

[cjard]

basically you need:

timeEachRevisionLabel.DataSource = summedRevisionsBindingSource
summedRevisionBindingSource.DataSource = revisionBindingSource
summedRevisionBindingSource.DataMember = summedRev_to_rev_datarelation
revisionBindingSource.DataSource = workItemBindingSource
revisionBindingSource.DataMember = revision_to_work_datarelation
workItemBindingSource.DataSource = workItemsDataTable

you probably have:
timeEachRevisionLabel.DataSource = summedRevisionsBindingSource
summedRevisionBindingSource.DataSource = summedRevisionsDataTable
revisionBindingSource.DataSource = workItemBindingSource
revisionBindingSource.DataMember = revision_to_work_datarelation
workItemBindingSource.DataSource = workItemsDataTable

The italic items are in a relationship, but the relation is broken because the label binds stright to the datatable? (its what I suspect..)


Dragging the nested child label out of datasources sets up all this for you - much less headache.. If you drag the toplevel one, it doesnt set it up.. much more headache

 

[Arg81]

hmmm, I did drag the label from the datatable I created.

OK, first image shows my relations. The code I use to fill these datatables was in my last post.

The 2nd image shows the binding of the label.

I think you are right about the binding, it's not created a related binding as such, I can't see where this would be.

Sorry for the delay, had major network problems all day and we've only just found out the offending hardware...always a problem when you are the IT dogsbody

 

[cjard]

hmmm, I did drag the label from the datatable I created.

Erm.. Im confused - what you ahve written here indicates that you didnt drag it from the right one, yet I cannot understand how anyone could look at the annotated picture I drew and get it wrong

i.e, your post here seems to confirm that you did NOT drag the nested relation out, you dragged the toplevel one

Look at my picture really closely - you can see that Flags is the Key end, Users is the Infinity end of the relation
My datasources window contains Users at the top level and also another Users nested under Flags

This means that your datasources window must contain TSDWR in THREE places -
TopLevel
Nested under DWRRevision
Nested under (whatever DWRREvision is nested under)


If youre still confused, try this:

Expand EVERY NODE of the datasources window - and I mean Every node. Every plus sign in there, click it and look at it, study it.

Youll see the nested ones and the normal ones..