Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

Thanks for the suggestion ,

the bit value is still DBNull if I use a dataReader.

I've changed the stored procedure to return an Int and not a Bit field, and
it works as expected. It looks like the only solution to fix this, the
intern is back from exams next week, I think I've got a nice christmas
present for him ]:>


unfortunatly I can't change the properties from non null to nullable, I came
into the project with only 2 months left before delivery, to try and fix
certain issues.

I feel the generic method is big improvement on the inline if's that are
still dotted around the code
  this.Code = (dr["ref_code"] == DBNull.Value || dr["ref_code"] == null ) ?
"" : (string) dr["ref_code"] ;
  is more readable than
  this.Code = Coalesce<string>(dr["ref_code"],string.Empty);  ?

Thanks for the help,

Davy


On 11/27/07, Peter Obiefuna <[email protected]> wrote:
>
> 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(R)  http://www.develop.com
> >
> > View archives and manage your subscription(s) at
> > http://discuss.develop.com
> >
>
> ===================================
> This list is hosted by DevelopMentor(R)   http://www.develop.com
>
> View archives and manage your subscription(s) at
> http://discuss.develop.com
>



--
Dave Jones
[email protected]
[email protected]

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

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