It's not null, but an empty string.

The normal way to check for null is something along the lines of:

rdr(i) is DBNull.Value '(Using VB .NET ODBCDataReader where "i" is the index of the column)

The problem is that I get the error in question at that very line in the code - I can't even determine if it's NULL.  I know that it's not because if I check the value of the data using the explicit conversion functions included with ODBCDataReader, I get data back.

I've come across NULL field in these tables and testing them the normal way works just fine.  This field isn't NULL, it's string data in a BCD field.

Perhaps the problem then is with the ODBC Driver not recognizing these as NULL values?  

How about just updating the table to have the NULL values be 0.0 and see fi all else works OK ?

backup you table (just in case)
in dbsys execute "update MTRegions4 set AdminExpPercentage=0.0 where AdminExpPercentage is NULL"

and see how the odbc parsing does after

Just an idea

Raul

As expected, works fine.  The problem is that this really isn't a solution, and not an option for me since it's not my table.  This table is from a 3rd party and I need to keep the original data intact.

This is sounding like a problem with the ODBC driver not recognizing NULL BCD fields.  Could it be a bug?

I have 2 suggestions.

1. email support direct : support@elevatesoft.com
Tim will be be able to provide an answer and assess if it's a bug. if this is the only field type where null checkign does not work

2. you could use a query to fix this on the fly so there is no soruce data impact. use something like
"select *,Coalesce(AdminExpPercentage,0.0) as NewAdminExpPercentage from mtregions4" and then use the NewAdminExpPercentage column instead of AdminExpPercentage.

Raul


<<
Mike Corrente wrote:

As expected, works fine.  The problem is that this really isn't a solution, and not an option for me since it's not my table.  This table is from a 3rd party and I need to keep the original data intact.

This is sounding like a problem with the ODBC driver not recognizing NULL BCD fields.  Could it be a bug?
>>

Great, Raul.  Thanks for your help.  I'll use that as a workaround if Tim determines it's a bug.

Mike,

As Raul, I don't use ODBC in my applications, but your table opened successfully using and ODBC connection from both LibreOffice Base and the old Borland SQL Explorer, and I could navigate across your table without any issues. As Raul said, you should report the issue to the support.

--
Fernando Dias
[Team Elevate]

Mike,

<< It's not null, but an empty string. >>

BCD (DECIMAL) columns do not use strings, period, so they can either have a
value or be NULL.

<< The normal way to check for null is something along the lines of:

rdr(i) is DBNull.Value '(Using VB .NET ODBCDataReader where "i" is the
index of the column)

The problem is that I get the error in question at that very line in the
code - I can't even determine if it's NULL.  I know that it's not because if
I check the value of the data using the explicit conversion functions
included with ODBCDataReader, I get data back. >>

I don't know what is going on with this.  I tried it here with VS2008/C# and
your table, and it is definitely throwing an error, but there aren't any
bugs in what the ODBC Driver is returning, which is simply just a flag in
the ODBC row buffer that indicates that the column is NULL (per the spec).
Just to make sure that the ODBC.NET data provider wasn't expecting some form
of formatted '0.00', I tried to just force the returned buffer to '0.00'
(the ODBC.NET data provider requests the DECIMAL values as character
strings) in addition to the setting of the NULL flag in the row buffer, and
that still didn't work.  So, something in the data provider code at the
managed code level is not liking the NULL values.

The only option at this point is to simply wrap the possible NULL columns in
a COALESCE() call to force them to zeros so that the ODBC.NET data provider
can handle them properly.


--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks, Tim.  I'll do that.

Mike,

Actually, scratch that - I figured out the bug today, per my email to you.
A fix will be in 4.31 B3.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com