Expression columns and DevisionByZero

  • Thread starter Thread starter Piotr Zbyszynski
  • Start date Start date
P

Piotr Zbyszynski

Hi all,

I'm adding a DataColumn to my DataTable with a given expression:
"column1/column2". But sometimes it occurs that in some rows, value of
column2 is 0. Then while adding my expression column, program throws
DivisionByZeroException.

I would like to handle this exception in a way, that if division by 0
occurs, Iwould like to set expression columns to 0. How should I do it?



Any advice would by very appreciated.
 
If the IIF used by the expression evaluator is like the IIF used in VB.NET
itself, then the error will still occur. That is because in VB.NET, IIF is a
function - not a built in language construct. As a function, it evaluates
all its arguments prior to actually executing. That means it will still
evaluate the Column1/Column2, and still cause the divide by zero exception.

If IIF works differently in the expression evaluator, then it will be OK.
 
Piotr:

I've tried fooling it a couple of ways but I only get the infiinity issue.
This is driving me nuts and there has to be a way. There aren't any
datacolumn events so there's no way to trick it there. I tried trapping the
ColumnChangnig and RowChanging events, didn't figure they'd do anything in
this regard but figured it was worth a try. Tried trapping the dividebyZero
exception but then the column doesn't get added. I tried playing with the
exprression every way I can think of.

The bad news I think is that you are going to have to loop through the
datatable and set those values manually, or use the computation on the
server side. The server side approach is really ugly b/cthings won't update
and the problem with the other approach is that you'll need to fire the
routine to recalc every time a value changes. This is a lot of work. Maybe
you can just calculate them all on the first pass, then just trap
rowChanging to caculate one row whenever things change.

Sorry about that...and I'm still looking, it's driving me nuts. If I can
find something, I'll post back.

Cheers,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
 
What about the following expression, it's kind of convoluted, but I think it
might work:

(Column1 / IIF(Column2 = 0, 1, Column2) ) * IIF (Column2 = 0, 0, 1)

Basically, it does the division as usual if Column 2 is not zero - otherwise
it divides by 1 (i.e, you just get Column1). Then it multiplies by 0 if
Column2 is zero (i.e., it makes the result 0 - which is what you want when
Column2 is zero), or it multiples it by 1 if Column2 is not zero (i.e. it
does not change the result of the division, so you are left with
Column1/Column2).

I haven't tested it yet, but I think it should work.
 
Marina said:
What about the following expression, it's kind of convoluted, but I think it
might work:

(Column1 / IIF(Column2 = 0, 1, Column2) ) * IIF (Column2 = 0, 0, 1)

Basically, it does the division as usual if Column 2 is not zero - otherwise
it divides by 1 (i.e, you just get Column1). Then it multiplies by 0 if
Column2 is zero (i.e., it makes the result 0 - which is what you want when
Column2 is zero), or it multiples it by 1 if Column2 is not zero (i.e. it
does not change the result of the division, so you are left with
Column1/Column2).

I haven't tested it yet, but I think it should work.
Click to expand...

Any reason not to do:

IIF(Column2=0, 0, Column1/Column2)

?

It really depends on whether or not the third expression is evaluated
even if the first one evaluates to true...
 
IIF is a function - so it evaluates all its arguments before running. If it
evaluated the true/false arguments only depending on the result of the
condition - then the original idea woudl work as well, but it didn't for the
same reason.

I tried out your suggestion - and it does indeed cause the same exception.

I then decided to run the expression I suggested, and this worked and gave
the desired result.
 
Jon:

I tried that too but it still blows up. One of those things that looks like
it should work but doesn't and if frustrating as heck.

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
Marina said:
IIF is a function - so it evaluates all its arguments before running. If it
evaluated the true/false arguments only depending on the result of the
condition - then the original idea woudl work as well, but it didn't for the
same reason.

I tried out your suggestion - and it does indeed cause the same exception.

I then decided to run the expression I suggested, and this worked and gave
the desired result.
Click to expand...
 
Hi,

I believe you should switch True and False parts of the condition and it
should work

IIF(Column2 = 0, 0, Column1/Column2)
 
Hello,



thanks for help. The (Column1 / IIF(Column2 = 0, 1, Column2) ) * IIF
(Column2 = 0, 0, 1) expression
seems to work just fine. The problem is that I'm building the expression
dynamically, based on user input, so I was hoping that this problem can by
solved using some event. Now I'll have to do some parsing, to ensure that
there is division in my expression and that I'm dividing by some column (not
by some constant for example) and then replace division with this tricky
expression.



Thanks for help once again.



Greetings.
 
Back
Top