Strange Wierdness with SQLServer Bit fields and SQLProvider

Hi all,

[summary]
I've got a bit field, that always retuns DBNULL, even though the value is
True / False (1/0) using SQLServer 2005 , Dataset , DataAdapter.

[detail]
  I've been stuck with this for a while now, looked everywhere I can and
can't seem to find someone who has the same problem, so I must be doing
something wrong.

In the database we have

  create table REF_STATE (
   ref_id int identity(1,1),
   ref_label varchar(25),
   ref_isvalid bit
)

the database uses ref_isvalid as a tristate variable, true, false , dbnull
(not used)

we have 3 rows in the table

insert into ref_state ( label, isvalid ) values ( 'active' , 1)
insert into ref_state ( label, isvalid ) values ( 'debug' , 0)
insert into ref_state ( label , isvalid) values ( 'disactive',1)

For this example ref_isvalid defines if we're in debug mode, ( there are
200+ ref tables like this , so changing NOT NULL is not possible.)

to get information from this we use a stored procedure

create procedure SP_GetState(@debug bit) as
begin
   select * from ref_state where ref_state = 1 or @debug = 1
end

Now the problem, standard SQLProvider code

Connection -> Command -> SQLDataAdaptor -> Dataset

when we loop over the dataset ,  we use this function

  public T DefaultValue<T>(object o , object defaultValue){
  if(o == null || o == DBNull.Value) return defaultValue;
  return (T)o;
 }

RefItem refItem = new RefItem;

 refItem.Id = DefaultValue<Int32>(dr["ref_id"],-1);
 refItem.Code = DefaultValue<String>(dr["ref_label"],"<unknown>");
 refItem.IsValid = DefaultValue<Boolean>(dr["ref_isValid"],false);

The problem is when we get to the
DefaultValue<Boolean>(dr["ref_isvalid"],false) , it ALWAYS falls on the o ==
DBNull.Value , the value returned from the stored procedure is allways
DBNull for this bit field ( ref_isvalid bit , )

Selects direct on the database return the correct data, Executing the
storedprocedure returns the correct value , Somewhere between the
DataAdapter.Fill(ds) and the   dr["ref_isvalid"]  it's either loosing the
value or not setting it.  Any Ideas how to debug this or references to an
example please?


Thanks

Davy J.

===================================
This list is hosted by DevelopMentor®  http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com