"steven scaife" <stevensca***@discussions.microsoft.com> schreef in bericht
news:AC1304B8-B937-4EF8-873A-9FD9E9002233@microsoft.com...
>I am building a system that pulls a large dataset from the sql backend. I
>am
> then applying a row filter to the dataview, i have 5 filters, day, week,
> month, quarter and year.
>
> However i need to total up 2 / 3 of the columns depending on the dataset
> and
> i am unsure of how to do this.  I have tried adding a datacolumn to the
> dataview and adding an expression but i get an error
> Message "Object reference not set to an instance of an object." String.
>
> I am also thinking about doing a loop through the dataview and adding up
> the
> values in a loop.  But for some reason i don't think this is a good idea,
> maybe in Classic ASP, but dot net as i am led to believe has the power to
> do
> all this builtin.
>
> if anyone could offer any suggestions or places to look that has examples
> of
> what i want to achieve i would be very grateful.
>
> I am doing this in a loop a sample of the code is below
>
> Dim TotalAppsWrit As DataColumn = New DataColumn
>
>        With TotalAppsWrit
>            .DataType = System.Type.GetType("System.Decimal")
>            .ColumnName = "TotalApps"
>        End With
>
>        cmd.CommandTimeout = 1000
>        cmd.Connection = conn
>
>        Try
>
>            'there are currently 8 views that we pull information from so
> we
> need to do a main loop for these
>            For n = 0 To 7
>
>                'here we fill the dataset with the information for the view
> we are working on
>                cmd.CommandText = AllTables(n, 1)
>                da.Fill(ds, AllTables(n, 0))
>
>                'get the values into the variables
>
>                'we will do a loop through the seven tables using the
> upperbound function of the array of tables
>                'if any is added in the future then this wont need to be
> modified.
>
>                dt = ds.Tables(AllTables(n, 0)) 'create the datatable
>                dt.Columns.Add(TotalAppsWrit)
>                dv = New DataView(dt) 'create the dataview linked to the
> datatable
>                dv.Table.Columns("TotalApps").Expression = "count('" &
> CType(drv.Item("Total_Apps_Writ"), String) & "')"
>
>                'loop through the datatables and add each val to the
> collection ie. RetVol & "Day"
>                For i As Integer = 0 To 4
>                    Select Case i
>                        Case 0
>                            'this will do the days values
>                            'but we need to take todays date and subtract
> the period
>                            'date is >
> DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) and <
> DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period)
>                            dv.RowFilter = "Date_App_Written > '" &
> DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) & "' AND
> Date_App_Written < '" &
> DateTime.UtcNow.AddDays(-PD.item(TimeRangeText).Period) & "'"
>
>
>                            Select Case AllTables(n, 0)
>                                '0 is always the volume of apps, this is
> integer type
>                                '1 is always the total value of apps, this
> is double type
>                                '2 is always the total fees of the apps,
> this is double type
>                            Case "CC", "Sourcing"
>                                    drv = dv.Item(0)
>
>                                    AV.add("dy" & AllTables(n, 0) &
> "AppsWrit", New IntegerVariables(CType(drv.Item(0), Integer)))
>                                    AV.add("dy" & AllTables(n, 0) &
> "AppsVal", New DoubleVariables(CType(drv.Item(1), Double)))
>
>                                Case Else
>                                    drv = dv.Item(0)
>                                    AV.add("dy" & AllTables(n, 0) &
> "AppsWrit", New IntegerVariables(CType(drv.Item(0), Integer)))
>                                    AV.add("dy" & AllTables(n, 0) &
> "AppsVal", New DoubleVariables(CType(drv.Item(1), Double)))
>                                    AV.add("dy" & AllTables(n, 0) & "Fees",
> New DoubleVariables(CType(drv.Item(2), Double)))
>                            End Select
>