Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

Debug with a DataReader so you can bypass the DataAdapter.Fill() blackbox.
All your rows will be naked to you as you iterate. And, please, simplify
your code. I see a bit of generics'mania' right there.
Try declaring a bool? or Nullable<bool> and just map your values directly
from the DataReader.

--------------------------------------------------
From: "Davy J" <[email protected]>
Sent: Tuesday, November 27, 2007 4:07 AM
To: <[email protected]>
Subject: [DOTNET-CLR] 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
>

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

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