Handling DBNull from databases

[Charlie Brown]

When checking for NULL values from a database, normally I would use

If Not row("NetSales") Is DBNull.Value Then

NetSales = row("NetSales")

End If

However, if I use a typed dataset then I can't check for NULL the same
way

If Not row.NetSales Is DBNull.Value Then

NetSales = row.NetSales

End If

Is there a better way to check for Null values when using typed
datasets?

[Cor Ligthert [MVP]]

Charlie,

AFAIK is the typed dataset from version 1.1 replacing the dbnull values for
the normal zero value of the type of value. I don't know it in version 2.0
therefore what version are you using.

Cor

"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@f6g2000cwb.googlegro ups.com...

When checking for NULL values from a database, normally I would use

If Not row("NetSales") Is DBNull.Value Then

NetSales = row("NetSales")

End If

However, if I use a typed dataset then I can't check for NULL the same
way

If Not row.NetSales Is DBNull.Value Then

NetSales = row.NetSales

End If

Is there a better way to check for Null values when using typed
datasets?

[Charlie Brown]

I am using version 1.1

Regardless of datatype I need to check for NULL values before
attempting to assign them to a something else. In a typed dataset
values can be NULL when pulled from a database, I would like to
leverage benefits of typed datasets in my code.

If I use a typed dataset and assign an object a value from that dataset
that is null it will throw an exception

Dim sngSales as Single = row.Sales

I need to check for a NULL value in the Sales column. I can do this by
referecing the column like this just fine.

If Not row("Sales") is dbNull.Value Then
Dim snSales as Single = row.Sales
End If

But it seems to defeat the benefit of a typed dataset in code. You
can't have values in a dataset default to their 'normal' zero values,
since 0 and NULL in a financial application are two different things.
Cor Ligthert [MVP] wrote:

Charlie,

AFAIK is the typed dataset from version 1.1 replacing the dbnull values for
the normal zero value of the type of value. I don't know it in version 2.0
therefore what version are you using.

Cor

"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@f6g2000cwb.googlegro ups.com...

When checking for NULL values from a database, normally I would use

If Not row("NetSales") Is DBNull.Value Then

NetSales = row("NetSales")

End If

However, if I use a typed dataset then I can't check for NULL the same
way

If Not row.NetSales Is DBNull.Value Then

NetSales = row.NetSales

End If

Is there a better way to check for Null values when using typed
datasets?

[Cor Ligthert [MVP]]

Charlie,

I can do it for you but better is to do it yourself.

If you go to the solution explorer. Click on the button in top show all
files, than you can expand the dataset and see the vb code.

Just searching on things as dbnull in that will show you how it is handled.

Cor

"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...

I am using version 1.1

Regardless of datatype I need to check for NULL values before
attempting to assign them to a something else. In a typed dataset
values can be NULL when pulled from a database, I would like to
leverage benefits of typed datasets in my code.

If I use a typed dataset and assign an object a value from that dataset
that is null it will throw an exception

Dim sngSales as Single = row.Sales

I need to check for a NULL value in the Sales column. I can do this by
referecing the column like this just fine.

If Not row("Sales") is dbNull.Value Then
Dim snSales as Single = row.Sales
End If

But it seems to defeat the benefit of a typed dataset in code. You
can't have values in a dataset default to their 'normal' zero values,
since 0 and NULL in a financial application are two different things.
Cor Ligthert [MVP] wrote:

Charlie,

AFAIK is the typed dataset from version 1.1 replacing the dbnull values
for
the normal zero value of the type of value. I don't know it in version
2.0
therefore what version are you using.

Cor

"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@f6g2000cwb.googlegro ups.com...

> When checking for NULL values from a database, normally I would use
>
> If Not row("NetSales") Is DBNull.Value Then
>
> NetSales = row("NetSales")
>
> End If
>
> However, if I use a typed dataset then I can't check for NULL the same
> way
>
> If Not row.NetSales Is DBNull.Value Then
>
> NetSales = row.NetSales
>
> End If
>
> Is there a better way to check for Null values when using typed
> datasets?
>

[Charlie Brown]

Although I appreciate the advice on how to use my designer, I have
found the answer on my own. For anyone looking, when using typed
datasets and checking for DBNULL's the proper way to do so is this...

Dim row as TypedDataset.TypedDatasetRow =
TypedDataset1.Tables(0).Row(0)

Referencing the dataset row this way will allow you to leverage type
checking at design time and will create functions belonging to the row
object that return a boolean if the data in a column is null.

If you have a column named NetSales in your dataset, then the dataset
will create an IsNetSalesNull function that returns a boolean value.
So the proper way to check for Null values would be the following

If Not row.IsNetSalesNull Then
Dim sngNetSales as Single = row.NetSales
End If

Cor Ligthert [MVP] wrote:

Charlie,

I can do it for you but better is to do it yourself.

If you go to the solution explorer. Click on the button in top show all
files, than you can expand the dataset and see the vb code.

Just searching on things as dbnull in that will show you how it is handled.

Cor

"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...

I am using version 1.1

Regardless of datatype I need to check for NULL values before
attempting to assign them to a something else. In a typed dataset
values can be NULL when pulled from a database, I would like to
leverage benefits of typed datasets in my code.

If I use a typed dataset and assign an object a value from that dataset
that is null it will throw an exception

Dim sngSales as Single = row.Sales

I need to check for a NULL value in the Sales column. I can do this by
referecing the column like this just fine.

If Not row("Sales") is dbNull.Value Then
Dim snSales as Single = row.Sales
End If

But it seems to defeat the benefit of a typed dataset in code. You
can't have values in a dataset default to their 'normal' zero values,
since 0 and NULL in a financial application are two different things.
Cor Ligthert [MVP] wrote:

Charlie,

AFAIK is the typed dataset from version 1.1 replacing the dbnull values
for
the normal zero value of the type of value. I don't know it in version
2.0
therefore what version are you using.

Cor

"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@f6g2000cwb.googlegro ups.com...
> When checking for NULL values from a database, normally I would use
>
> If Not row("NetSales") Is DBNull.Value Then
>
> NetSales = row("NetSales")
>
> End If
>
> However, if I use a typed dataset then I can't check for NULL the same
> way
>
> If Not row.NetSales Is DBNull.Value Then
>
> NetSales = row.NetSales
>
> End If
>
> Is there a better way to check for Null values when using typed
> datasets?
>

[Cor Ligthert [MVP]]

Charlie,

I don't think that this is a good advice. If you had followed what I told
you had find something as this in one minute.
\\\
Public Property City As String
Get
Try
Return CType(Me(Me.tableEmployees.CityColumn),String)
Catch e As InvalidCastException
Throw New StrongTypingException("Cannot get value
because it is DBNull.", e)
End Try
End Get
Set
Me(Me.tableEmployees.CityColumn) = value
End Set
End Property
///

With otherwords catching this exception in a try block when you use this
property will give you the needed result in a strongly typed way.

I am at the moment only active with version 2005, and that while as most of
the regular contributers to dotnet newsgroups do I not like the strongly
dataset versions from before 2005. I knew that it was something like this
but did not know anymore exactly how.

However, because I don't want that other have the wrong answer as the
propper way, have I taken some time for this. But that was more easy to do
for your if you had taken my advice, I had no project to test this.

As I see it well, than you are now just going around the strongly typed
dataset by using the non typed part of that.

Cor
"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...

Although I appreciate the advice on how to use my designer, I have
found the answer on my own. For anyone looking, when using typed
datasets and checking for DBNULL's the proper way to do so is this...

Dim row as TypedDataset.TypedDatasetRow =
TypedDataset1.Tables(0).Row(0)

Referencing the dataset row this way will allow you to leverage type
checking at design time and will create functions belonging to the row
object that return a boolean if the data in a column is null.

If you have a column named NetSales in your dataset, then the dataset
will create an IsNetSalesNull function that returns a boolean value.
So the proper way to check for Null values would be the following

If Not row.IsNetSalesNull Then
Dim sngNetSales as Single = row.NetSales
End If

Cor Ligthert [MVP] wrote:

Charlie,

I can do it for you but better is to do it yourself.

If you go to the solution explorer. Click on the button in top show all
files, than you can expand the dataset and see the vb code.

Just searching on things as dbnull in that will show you how it is
handled.

Cor

"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...

>I am using version 1.1
>
> Regardless of datatype I need to check for NULL values before
> attempting to assign them to a something else. In a typed dataset
> values can be NULL when pulled from a database, I would like to
> leverage benefits of typed datasets in my code.
>
> If I use a typed dataset and assign an object a value from that dataset
> that is null it will throw an exception
>
> Dim sngSales as Single = row.Sales
>
> I need to check for a NULL value in the Sales column. I can do this by
> referecing the column like this just fine.
>
> If Not row("Sales") is dbNull.Value Then
> Dim snSales as Single = row.Sales
> End If
>
> But it seems to defeat the benefit of a typed dataset in code. You
> can't have values in a dataset default to their 'normal' zero values,
> since 0 and NULL in a financial application are two different things.
>
>
> Cor Ligthert [MVP] wrote:
>> Charlie,
>>
>> AFAIK is the typed dataset from version 1.1 replacing the dbnull
>> values
>> for
>> the normal zero value of the type of value. I don't know it in version
>> 2.0
>> therefore what version are you using.
>>
>> Cor
>>
>> "Charlie Brown" <cb****@duclaw.com> schreef in bericht
>> news:11**********************@f6g2000cwb.googlegro ups.com...
>> > When checking for NULL values from a database, normally I would use
>> >
>> > If Not row("NetSales") Is DBNull.Value Then
>> >
>> > NetSales = row("NetSales")
>> >
>> > End If
>> >
>> > However, if I use a typed dataset then I can't check for NULL the
>> > same
>> > way
>> >
>> > If Not row.NetSales Is DBNull.Value Then
>> >
>> > NetSales = row.NetSales
>> >
>> > End If
>> >
>> > Is there a better way to check for Null values when using typed
>> > datasets?
>> >
>

[Charlie Brown]

I am not going around the strongly typed dataset at all. .net creates
those functions to test for NULL values by itself whenever you create a
strong dataset. The reason for using strong datasets is compile time
type checking, intellisense, and compact code. I can use the Try catch
method as well, but why not check for NULL myself when I know it will
be there. I am using the strong type dataset to its full potential. It
PROVIDES a way to check for NULL values for a reason. If it didn't
provide a way, then a Try Catch block would be more correct.
Cor Ligthert [MVP] wrote:

Charlie,

I don't think that this is a good advice. If you had followed what I told
you had find something as this in one minute.
\\\
Public Property City As String
Get
Try
Return CType(Me(Me.tableEmployees.CityColumn),String)
Catch e As InvalidCastException
Throw New StrongTypingException("Cannot get value
because it is DBNull.", e)
End Try
End Get
Set
Me(Me.tableEmployees.CityColumn) = value
End Set
End Property
///

With otherwords catching this exception in a try block when you use this
property will give you the needed result in a strongly typed way.

I am at the moment only active with version 2005, and that while as most of
the regular contributers to dotnet newsgroups do I not like the strongly
dataset versions from before 2005. I knew that it was something like this
but did not know anymore exactly how.

However, because I don't want that other have the wrong answer as the
propper way, have I taken some time for this. But that was more easy to do
for your if you had taken my advice, I had no project to test this.

As I see it well, than you are now just going around the strongly typed
dataset by using the non typed part of that.

Cor
"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...

Although I appreciate the advice on how to use my designer, I have
found the answer on my own. For anyone looking, when using typed
datasets and checking for DBNULL's the proper way to do so is this...

Dim row as TypedDataset.TypedDatasetRow =
TypedDataset1.Tables(0).Row(0)

Referencing the dataset row this way will allow you to leverage type
checking at design time and will create functions belonging to the row
object that return a boolean if the data in a column is null.

If you have a column named NetSales in your dataset, then the dataset
will create an IsNetSalesNull function that returns a boolean value.
So the proper way to check for Null values would be the following

If Not row.IsNetSalesNull Then
Dim sngNetSales as Single = row.NetSales
End If

Cor Ligthert [MVP] wrote:

Charlie,

I can do it for you but better is to do it yourself.

If you go to the solution explorer. Click on the button in top show all
files, than you can expand the dataset and see the vb code.

Just searching on things as dbnull in that will show you how it is
handled.

Cor

"Charlie Brown" <cb****@duclaw.com> schreef in bericht
news:11**********************@g10g2000cwb.googlegr oups.com...
>I am using version 1.1
>
> Regardless of datatype I need to check for NULL values before
> attempting to assign them to a something else. In a typed dataset
> values can be NULL when pulled from a database, I would like to
> leverage benefits of typed datasets in my code.
>
> If I use a typed dataset and assign an object a value from that dataset
> that is null it will throw an exception
>
> Dim sngSales as Single = row.Sales
>
> I need to check for a NULL value in the Sales column. I can do this by
> referecing the column like this just fine.
>
> If Not row("Sales") is dbNull.Value Then
> Dim snSales as Single = row.Sales
> End If
>
> But it seems to defeat the benefit of a typed dataset in code. You
> can't have values in a dataset default to their 'normal' zero values,
> since 0 and NULL in a financial application are two different things.
>
>
> Cor Ligthert [MVP] wrote:
>> Charlie,
>>
>> AFAIK is the typed dataset from version 1.1 replacing the dbnull
>> values
>> for
>> the normal zero value of the type of value. I don't know it in version
>> 2.0
>> therefore what version are you using.
>>
>> Cor
>>
>> "Charlie Brown" <cb****@duclaw.com> schreef in bericht
>> news:11**********************@f6g2000cwb.googlegro ups.com...
>> > When checking for NULL values from a database, normally I would use
>> >
>> > If Not row("NetSales") Is DBNull.Value Then
>> >
>> > NetSales = row("NetSales")
>> >
>> > End If
>> >
>> > However, if I use a typed dataset then I can't check for NULL the
>> > same
>> > way
>> >
>> > If Not row.NetSales Is DBNull.Value Then
>> >
>> > NetSales = row.NetSales
>> >
>> > End If
>> >
>> > Is there a better way to check for Null values when using typed
>> > datasets?
>> >
>