asked on

Checking a Dataset for DBNull whiclst using the Compute SUM function

Hi All
I am using a Dataset's Compute function but get the error 'Object cannot be cast from DBNull '. Obviously there is no data being returned with the TYPE_ID 1.Please could you let me know how I could check the the returning SUM for DBNull...

ds.Tables("reporttbl").Compute("SUM(TOTAL_MONTH_WEIGHT)", "TYPE_ID=1")

Many thanks

Rit

samtran0331

have you tried:
ds.Tables("reporttbl").Compute("SUM(ISNULL(TOTAL_MONTH_WEIGHT,0))", "TYPE_ID=1")

rito1

ASKER

Hi
Good idea though i get the following error based on the syntax above..

Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier

Rit

Sammy

why not check your row for dbNull value before you compute it?
if(myfield = System.DBNull.Value)
{
//Do something like giving a default value
}
else
{
//Compute
}

rito1

ASKER

Hi

I am not sure this will help as its actually the criteria TYPE_ID=1 that is causing the issue as in there are rows within the datatable but non with the TYPE_ID 1 on this occasion.

Rit

rito1

ASKER

This may help.. The aggregate from the compute function is being assigned to a Decimal variable like so...

Dim conftotal As Decimal = 0.0
conftotal = Convert.ToDecimal(ds.Tables("reporttbl").Compute("SUM(TOTAL_MONTH_WEIGHT)", "TYPE_ID=1"))

Rit

rito1

ASKER

How about this for a workaround

'HERE IS MY INITIAL DATASET
Dim ds As DataSet = Snapshotdata.RetrieveData()

'I THEN CREATE A DATAVIEW AND USE THE COUNT FUNCTION TO TEST WHETHER THERE ARE ANY RECORDS BASED ON THE TYPE_ID CREITERIA.

Dim dvConf As New DataView(ds.Tables("reporttbl"))
dvConf.RowFilter = "[TYPE_ID] = '1'"

If dvConf.Count = 0 Then
conftotal = 0.0
Else
conftotal = Convert.ToDecimal(ds.Tables("reporttbl").Compute("SUM(TOTAL_MONTH_WEIGHT)", "TYPE_ID=1"))
End If

Rit

samtran0331

try putting the isnull check on the outside...
ds.Tables("reporttbl").Compute("ISNULL(SUM(TOTAL_MONTH_WEIGHT),0)", "TYPE_ID=1")

it works in SQL...not sure about when using ds.table.compute....
against AdventureWorks...
SELECT ISNULL(SUM(UnitPrice), 0) AS SumPrice
FROM SalesOrderDetail
WHERE (SpecialOfferID = 66)

will return zero in Enterprise Manager
it returns ".0000" in Query Analyzer

SELECT SUM(ISNULL(UnitPrice,0)) AS SumPrice
FROM SalesOrderDetail
WHERE (SpecialOfferID = 66)
--returns nothing

samtran0331

ps...in my example above, there is no SpecialOfferID in the table

samtran0331

lol...i mean
ps...in my example above, there is no SpecialOfferID=66 in the table

rito1

ASKER

Hi samtran0331

I see your logic but it doesn't like it. I get the following error:

Please note. Cannot evaluate. Expression 'System.Data.FunctionNode' is not an aggregate.

I can handle using my Dataview solution, its just you would have thought there would be a more streamlined approach woudn't you.

Rit

samtran0331

lol...yeah...wasn't sure if ".Compute" was going to like that "isnull" or not....
are you using the dataset for other purposes?

rito1

ASKER

Yep, 3 .Computes of similar nature. Doh!
Rit

ASKER CERTIFIED SOLUTION

samtran0331

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ntiva

VB used to have a function Nz. I recreated it and use it to filter out nulls and nothing and replace with a default value.

Dim confTotal as Decimal = Nz(ds.Tables("reporttbl").Compute("SUM(TOTAL_MONTH_WEIGHT)", "TYPE_ID=1"),0)
 
 
'Some where else in your class or use Public Shared Function in another class
 
    Private Function Nz(ByVal value As Object, ByVal replaceValue As Object)
        If value Is Nothing Then
            Return replaceValue
        ElseIf value.Equals(DBNull.Value) Then
            Return replaceValue
        ElseIf value.GetType.ToString = "System.DateTime" And value = Nothing Then
            Return replaceValue
        Else
            Return value
        End If
    End Function

Open in new window