> Been wondering about this one for a while so I thought I'd see what other
> people do about it.
>
> When I read numeric data from a database into a class object and then
> display the data on a Form what are the different ways out there for dealing
> with values that are NULL in the database? Let me explain.
>
> I may have a number of numeric values in my database that are currently NULL
> - i.e. not every value has been supplied by the user. When they are read into
> my application the associated Form fields should then display nothing  rather
> than zeroes. However when these values are read into a class object what
> should they be stored as? They can't be stored as DBNulls assuming I am using
> numeric class property values. In the past where a value in the database is
> NULL I have set the associated property value to -1. I can then set Form
> fields associated with property values that are -1 to blank. Alternatively I
> can use Object property values which means I can set them to DBNull for
> associated database values that are NULL. Neither of these are particularly
> satisfactory.
>
> What are my other options here?

"David Kerber" wrote:

> In article <49CE30C7-D97D-4C3E-A9E4-61FA6D844***@microsoft.com>,
> YaHo***@discussions.microsoft.com says...
> > Been wondering about this one for a while so I thought I'd see what other
> > people do about it.
> >
> > When I read numeric data from a database into a class object and then
> > display the data on a Form what are the different ways out there for dealing
> > with values that are NULL in the database? Let me explain.
> >
> > I may have a number of numeric values in my database that are currently NULL
> > - i.e. not every value has been supplied by the user. When they are read into
> > my application the associated Form fields should then display nothing  rather
> > than zeroes. However when these values are read into a class object what
> > should they be stored as? They can't be stored as DBNulls assuming I am using
> > numeric class property values. In the past where a value in the database is
> > NULL I have set the associated property value to -1. I can then set Form
> > fields associated with property values that are -1 to blank. Alternatively I
> > can use Object property values which means I can set them to DBNull for
> > associated database values that are NULL. Neither of these are particularly
> > satisfactory.
> >
> > What are my other options here?
>
> I normally do as you mention and set them to some value that is invalid
> for normal use (remember that for some cases, -1 may be a valid value). 
> -9999999 works for me in many situations.
>
> --
> /~\ The ASCII
> \ / Ribbon Campaign
>  X  Against HTML
> / \ Email!
>
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
>
>

"Jeff Johnson" wrote:

> "YaHozna" <YaHo***@discussions.microsoft.com> wrote in message
> news:49CE30C7-D97D-4C3E-A9E4-61FA6D844682@microsoft.com...
>
> >They can't be stored as DBNulls assuming I am using
> > numeric class property values.
>
> Because the only place I have ever seen the term "DBNull" is in .NET
> programming, I'm going to throw out my canned response. Sorry if it's not
> relevant.
>
> [Canned response]
>
> This is a VB "classic" newsgroup. Questions about VB.NET (including VB
> 2005/2008 and VB Express, which have dropped .NET from their names) are
> off-topic here.
>
> Please ask .NET questions in newsgroups with "dotnet" in their names. The
> *.vb.* groups are for VB6 and earlier. If you don't see the *.dotnet.*
> groups on your news server, connect directly to the Microsoft server:
> msnews.microsoft.com.
>
> For questions specific to the VB.NET language, use this group:
>
> microsoft.public.dotnet.languages.vb
>
> Please note that things like controls and data access, which have their own
> subgroups in the Classic VB hierarchy, are not language-specific in .NET, so
> you should look for groups like these:
>
> microsoft.public.dotnet.framework.windowsforms.controls
> microsoft.public.dotnet.framework.adonet
>
> (Note that "vb" is not present in the group name.)
>
>
>

"David Kerber" <ns_dkerber@ns_wraenviro.com> wrote in message
news:MPG.240721e736636bf29896bc@news.east.cox.net...
> In article <49CE30C7-D97D-4C3E-A9E4-61FA6D844***@microsoft.com>,
> YaHo***@discussions.microsoft.com says...
>> Been wondering about this one for a while so I thought I'd see what other
>> people do about it.
>>
>> When I read numeric data from a database into a class object and then
>> display the data on a Form what are the different ways out there for
>> dealing
>> with values that are NULL in the database? Let me explain.
>>
>> I may have a number of numeric values in my database that are currently
>> NULL
>> - i.e. not every value has been supplied by the user. When they are read
>> into
>> my application the associated Form fields should then display nothing
>> rather
>> than zeroes. However when these values are read into a class object what
>> should they be stored as? They can't be stored as DBNulls assuming I am
>> using
>> numeric class property values. In the past where a value in the database
>> is
>> NULL I have set the associated property value to -1. I can then set Form
>> fields associated with property values that are -1 to blank.
>> Alternatively I
>> can use Object property values which means I can set them to DBNull for
>> associated database values that are NULL. Neither of these are
>> particularly
>> satisfactory.
>>
>> What are my other options here?
>
> I normally do as you mention and set them to some value that is invalid
> for normal use (remember that for some cases, -1 may be a valid value).
> -9999999 works for me in many situations.
>
> --
> /~\ The ASCII
> \ / Ribbon Campaign
> X  Against HTML
> / \ Email!
>
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
>

"Tony Proctor" wrote:

> Assuming this is a VB6 question...   :-)
>
> I wouldn't recommend using -1, or -9999999, or 1e38, or any real value.
> There's always a chance you'll come unstuck - either when the value has to
> be used legally, or when someone attempts arithmetic on it, or due to some
> other assumption.
>
> If you store the DB value in a Variant rather than a Single/Double then it
> can correctly represent Null, i.e. Null is a special state of a Variant and
> can be used to signify a special value for any data type, including text,
> dates, etc. The Recordset.Fields represents your data in a similar way
>
> If you're using numeric values only and you don't want to use Variants then
> there is another way - use the IEEE canonical NaN. I think I posted some VB6
> support for NaNs/Infinities a while back. The "canonical NaN" is the special
> 'quite NaN' that corresponds to 0/0, or sqrt(-1), etc. Intel architecture
> manuals call it "indefinite" but it's the same thing. Anyway, that won't
> clash with any other real value.
>
>     Tony Proctor
>
> "David Kerber" <ns_dkerber@ns_wraenviro.com> wrote in message
> news:MPG.240721e736636bf29896bc@news.east.cox.net...
> > In article <49CE30C7-D97D-4C3E-A9E4-61FA6D844***@microsoft.com>,
> > YaHo***@discussions.microsoft.com says...
> >> Been wondering about this one for a while so I thought I'd see what other
> >> people do about it.
> >>
> >> When I read numeric data from a database into a class object and then
> >> display the data on a Form what are the different ways out there for
> >> dealing
> >> with values that are NULL in the database? Let me explain.
> >>
> >> I may have a number of numeric values in my database that are currently
> >> NULL
> >> - i.e. not every value has been supplied by the user. When they are read
> >> into
> >> my application the associated Form fields should then display nothing
> >> rather
> >> than zeroes. However when these values are read into a class object what
> >> should they be stored as? They can't be stored as DBNulls assuming I am
> >> using
> >> numeric class property values. In the past where a value in the database
> >> is
> >> NULL I have set the associated property value to -1. I can then set Form
> >> fields associated with property values that are -1 to blank.
> >> Alternatively I
> >> can use Object property values which means I can set them to DBNull for
> >> associated database values that are NULL. Neither of these are
> >> particularly
> >> satisfactory.
> >>
> >> What are my other options here?
> >
> > I normally do as you mention and set them to some value that is invalid
> > for normal use (remember that for some cases, -1 may be a valid value).
> > -9999999 works for me in many situations.
> >
> > --
> > /~\ The ASCII
> > \ / Ribbon Campaign
> > X  Against HTML
> > / \ Email!
> >
> > Remove the ns_ from if replying by e-mail (but keep posts in the
> > newsgroups if possible).
> >
>
>
>

"YaHozna" <YaHo***@discussions.microsoft.com> wrote in message
news:49CE30C7-D97D-4C3E-A9E4-61FA6D844682@microsoft.com...
> Been wondering about this one for a while so I thought I'd see what other
> people do about it.
>
> When I read numeric data from a database into a class object and then
> display the data on a Form what are the different ways out there for
> dealing
> with values that are NULL in the database? Let me explain.
>
> I may have a number of numeric values in my database that are currently
> NULL
> - i.e. not every value has been supplied by the user. When they are read
> into
> my application the associated Form fields should then display nothing
> rather
> than zeroes. However when these values are read into a class object what
> should they be stored as? They can't be stored as DBNulls assuming I am
> using
> numeric class property values. In the past where a value in the database
> is
> NULL I have set the associated property value to -1. I can then set Form
> fields associated with property values that are -1 to blank. Alternatively
> I
> can use Object property values which means I can set them to DBNull for
> associated database values that are NULL. Neither of these are
> particularly
> satisfactory.
>
> What are my other options here?
>
> Regards,
>
> Gordon.

"Bill McCarthy" wrote:

> Hi Gordon,
>
> It sounds like you might be using .NET in which case you could use Nullable
> types.  If you are using .NET then best to ask in the dotnet newsgroups such
> as :
> microsoft.public.dotnet.languages.vb,
>
> Also search the web for ORM to see the different ways different
> object-relational mappers deal with database nullable fields when mapped to
> an object model.
>
>
>
> "YaHozna" <YaHo***@discussions.microsoft.com> wrote in message
> news:49CE30C7-D97D-4C3E-A9E4-61FA6D844682@microsoft.com...
> > Been wondering about this one for a while so I thought I'd see what other
> > people do about it.
> >
> > When I read numeric data from a database into a class object and then
> > display the data on a Form what are the different ways out there for
> > dealing
> > with values that are NULL in the database? Let me explain.
> >
> > I may have a number of numeric values in my database that are currently
> > NULL
> > - i.e. not every value has been supplied by the user. When they are read
> > into
> > my application the associated Form fields should then display nothing
> > rather
> > than zeroes. However when these values are read into a class object what
> > should they be stored as? They can't be stored as DBNulls assuming I am
> > using
> > numeric class property values. In the past where a value in the database
> > is
> > NULL I have set the associated property value to -1. I can then set Form
> > fields associated with property values that are -1 to blank. Alternatively
> > I
> > can use Object property values which means I can set them to DBNull for
> > associated database values that are NULL. Neither of these are
> > particularly
> > satisfactory.
> >
> > What are my other options here?
> >
> > Regards,
> >
> > Gordon.
>
>

"YaHozna" <YaHo***@discussions.microsoft.com> wrote in message
news:49CE30C7-D97D-4C3E-A9E4-61FA6D844682@microsoft.com...
> Been wondering about this one for a while so I thought I'd see what other
> people do about it.
>
> When I read numeric data from a database into a class object and then
> display the data on a Form what are the different ways out there for
> dealing
> with values that are NULL in the database? Let me explain.
>
> I may have a number of numeric values in my database that are currently
> NULL
> - i.e. not every value has been supplied by the user. When they are read
> into
> my application the associated Form fields should then display nothing
> rather
> than zeroes. However when these values are read into a class object what
> should they be stored as? They can't be stored as DBNulls assuming I am
> using
> numeric class property values. In the past where a value in the database
> is
> NULL I have set the associated property value to -1. I can then set Form
> fields associated with property values that are -1 to blank. Alternatively
> I
> can use Object property values which means I can set them to DBNull for
> associated database values that are NULL. Neither of these are
> particularly
> satisfactory.
>
> What are my other options here?
>
> Regards,
>
> Gordon.

"Cor Ligthert[MVP]" wrote:

> YaHozna,
>
> In my idea is this question valid for every way of programming. It is in
> fact a question how to handle the can be zero Null addition to a column type
> in SQL.
>
> It has been forever confusing. A Null in SQL means in fact unused. It is up
> to you what you make from that. Mostly is that for a numeric field simple
> zeros.
>
> However in most cases a simple null wil work the most simple.
>
> In VB8 you can use nullable fields, but for me is this a kind of creating it
> confirm C type languages. A blob field makes probably sense to avoid that
> filled with rubish in a database. A null value field is in my perception a
> kind of micro optimizing from 1970 or maybe 1980 but not from this
> millenium.
>
> This is a longtime problem and longwhile users have asked to do something
> about that problem. In SQL Server  2008 is beside the possibilitie to
> declare a column as can be Null column, the possibility to declare a column
> as a can be "Empty" column.
>
> Cor
>
> "YaHozna" <YaHo***@discussions.microsoft.com> wrote in message
> news:49CE30C7-D97D-4C3E-A9E4-61FA6D844682@microsoft.com...
> > Been wondering about this one for a while so I thought I'd see what other
> > people do about it.
> >
> > When I read numeric data from a database into a class object and then
> > display the data on a Form what are the different ways out there for
> > dealing
> > with values that are NULL in the database? Let me explain.
> >
> > I may have a number of numeric values in my database that are currently
> > NULL
> > - i.e. not every value has been supplied by the user. When they are read
> > into
> > my application the associated Form fields should then display nothing
> > rather
> > than zeroes. However when these values are read into a class object what
> > should they be stored as? They can't be stored as DBNulls assuming I am
> > using
> > numeric class property values. In the past where a value in the database
> > is
> > NULL I have set the associated property value to -1. I can then set Form
> > fields associated with property values that are -1 to blank. Alternatively
> > I
> > can use Object property values which means I can set them to DBNull for
> > associated database values that are NULL. Neither of these are
> > particularly
> > satisfactory.
> >
> > What are my other options here?
> >
> > Regards,
> >
> > Gordon.
>
>