Re: Strange Wierdness with SQLServer Bit fields and SQLProvider

How are you getting the value from the datareader? With reader.GetValue() ?

        FB

> 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 DevelopMentorR  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