Better Exception Handler

[VBAHole22]

I am querying an Oracle 9i db from within a vb.aspx page with the following code:

 

strStat1 = "SELECT SUM(C_POINT + C_POLYGON + C_POLYLINE + C_ANNO) FROM DRAW WHERE DPATH IN (" & strPathList & ")"
cmdStat.CommandText = strStat1
Total = CType(cmdStat.ExecuteScalar, Integer)

 

It works fine when the answer is some number. But when it comes back as zero I get a weird error:

 

OCI-22060: argument [2] is an invalid or uninitialized number

 

This is all in a try catch block so it doesn't just die but I would like to be able to trap it so that I can assign zero to the answer gracefully. How can I go about doing that?

[Robby]

I think that it's Oracle complaining about one of the fields being null, this is not a complaint from .NET. The reason your getting a Zero is that Total is initialized as zero, it's not coming from the executeScalar.

 

Handling this depends on what you want to do in case one of the columns is null, do you still want to SUM the remaining columns?

[VBAHole22]

I would like to sum the remaining columns in the event of a NULL.

 

I'm not sure about it being an Oracle gripe though, because the same query (when the variables are filled in) does execute in TOAD against Oracle. I believe you can Sum even when a field is NULL.

 

In the even all fields are NULL I would like to report a zero.

 

Thanks for the help, I appreciate it.

[Robby]

I'll test it out in TOAD tomorrow and let you know.

[Nerseus]

I believe the standard behavior for "SUM" and addition is that if any column of any row is NULL, then the entire result is NULL.

 

My guess would be that the SUM (or the addition - doesn't really matter) is returning NULL. ExecuteScaler should be able to return System.DBNull.Value, but you're casting it directly as Integer which will definitely blow.

 

If you want 0, you could handle it a number of ways. If Oracle supports it (don't have my reference handy), try something like this:

SELECT IsNull(SUM(C_POINT + C_POLYGON + C_POLYLINE + C_ANNO), 0) FROM DRAW 

(I added the IsNull function). If that doesn't work, look at Coalesce (or Coallesce).

 

If all else fails, you can do this in VB with something like:

cmdStat.CommandText = strStat1
Dim o As Object = cmdStat.ExecuteScalar
If o = System.DBNull.Value Then
   Total = 0
Else
   Total = System.Convert.ToInt32(o)
End If

 

-ner

[VBAHole22]

Figured it out. Just took a little more SQL code

 

SELECT SUM(NVL(C_POINT,0) + NVL(C_POLYGON,0) + NVL(C_POLYLINE,0) + NVL(C_ANNO,0)) FROM DRAW

[Robby]

D'OH! I just used NVL( ) a few weeks ago.