Datatable retrieving Null values - I'm stuck !

[Stuart]

Hi there

I have a stored procedure on my SQL database that retrieves a wide range of
values from about 5 different tables. My end point is to calculate the cost
against each line of retrieved data. Depending upon the contents of a
particular field that cost calculation changes....

I retrieve the data in to a dataset and subsequently in to a datatable -
fine so far...

I then programatically add the "Cost" column and am trying to put data in to
it. The field that decides how I calculate the cost can have a value of NULL,
1, 2, 3 or 4 - this field is called "TRANSACTION_TYPE". I need to loop
through all the rows that are retrievd calculate the Cost column depending
upon what's in "TRANSACTION_TYPE".

I am trying to use an IF statement in the code as follows:

Dim i As Integer
For i = 0 To dt3.Rows.Count - 1
If dt3.Rows(i).Item("TRANSACTION_TYPE").IsNull Then
dt3.Rows(i).Item("Cost") = 0
ElseIf dt3.Rows(i).Item("TRANSACTION_TYPE") = 1 Then
dt3.Rows(i).Item("Cost") = 1
Else
dt3.Rows(i).Item("Cost") = 2
End If

Next

Obviously in the above the Cost calculation is omitted and replaced with a
simple integer for the purposes of testing...

My problem is the first IF statement - how do I identify the contents of the
"TRANSACTION_TYPE" as NULL - the above is one effort which fails with
various different messages such as:

Exception Details: System.MissingMemberException: Public member 'IsNull' on
type 'DBNull' not found.

OR

Exception Details: System.MissingMemberException: Public member 'IsNull' on
type 'Short' not found.

I have tried using:
If dt3.Rows(i).Item("TRANSACTION_TYPE") Is Nothing Then

but the two lots of data I have retrieved to gain the first two errors then
show the same error:

Exception Details: System.InvalidCastException: Operator is not valid for
type 'DBNull' and string "1".

It obviously skips the first "if... is nothing" statement and fails on the
if = 1 statement... I get the impression that my "Is nothing" statement is
not matching the NULL fields in the first place

Hope this makes sense - basically - how do I reference a NULL value in the
above scenario ???

Thanks for your help

Stuart

[Mark Fitzpatrick]

Stuart,
Your biggest problem is probably the fact that a null in a
programming language is not necessarily the same type of null used in a
database. Instead of testing for null as you are, see if the item in the row
is equal to System.DbNull.Value instead. This will then compare it to the
null value returned by the database.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

"Stuart" <St****@discussions.microsoft.com> wrote in message
news:7C**********************************@microsof t.com...

Hi there

I have a stored procedure on my SQL database that retrieves a wide range
of
values from about 5 different tables. My end point is to calculate the
cost
against each line of retrieved data. Depending upon the contents of a
particular field that cost calculation changes....

I retrieve the data in to a dataset and subsequently in to a datatable -
fine so far...

I then programatically add the "Cost" column and am trying to put data in
to
it. The field that decides how I calculate the cost can have a value of
NULL,
1, 2, 3 or 4 - this field is called "TRANSACTION_TYPE". I need to loop
through all the rows that are retrievd calculate the Cost column depending
upon what's in "TRANSACTION_TYPE".

I am trying to use an IF statement in the code as follows:

Dim i As Integer
For i = 0 To dt3.Rows.Count - 1
If dt3.Rows(i).Item("TRANSACTION_TYPE").IsNull Then
dt3.Rows(i).Item("Cost") = 0
ElseIf dt3.Rows(i).Item("TRANSACTION_TYPE") = 1 Then
dt3.Rows(i).Item("Cost") = 1
Else
dt3.Rows(i).Item("Cost") = 2
End If

Next

Obviously in the above the Cost calculation is omitted and replaced with a
simple integer for the purposes of testing...

My problem is the first IF statement - how do I identify the contents of
the
"TRANSACTION_TYPE" as NULL - the above is one effort which fails with
various different messages such as:

Exception Details: System.MissingMemberException: Public member 'IsNull'
on
type 'DBNull' not found.

OR

Exception Details: System.MissingMemberException: Public member 'IsNull'
on
type 'Short' not found.

I have tried using:
If dt3.Rows(i).Item("TRANSACTION_TYPE") Is Nothing Then

but the two lots of data I have retrieved to gain the first two errors
then
show the same error:

Exception Details: System.InvalidCastException: Operator is not valid for
type 'DBNull' and string "1".

It obviously skips the first "if... is nothing" statement and fails on the
if = 1 statement... I get the impression that my "Is nothing" statement is
not matching the NULL fields in the first place

Hope this makes sense - basically - how do I reference a NULL value in the
above scenario ???

Thanks for your help

Stuart

[Stuart]

Spot on !

Thanks very much for your help Mark

"Mark Fitzpatrick" wrote:

Stuart,
Your biggest problem is probably the fact that a null in a
programming language is not necessarily the same type of null used in a
database. Instead of testing for null as you are, see if the item in the row
is equal to System.DbNull.Value instead. This will then compare it to the
null value returned by the database.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

"Stuart" <St****@discussions.microsoft.com> wrote in message
news:7C**********************************@microsof t.com...

Hi there

I have a stored procedure on my SQL database that retrieves a wide range
of
values from about 5 different tables. My end point is to calculate the
cost
against each line of retrieved data. Depending upon the contents of a
particular field that cost calculation changes....

I retrieve the data in to a dataset and subsequently in to a datatable -
fine so far...

I then programatically add the "Cost" column and am trying to put data in
to
it. The field that decides how I calculate the cost can have a value of
NULL,
1, 2, 3 or 4 - this field is called "TRANSACTION_TYPE". I need to loop
through all the rows that are retrievd calculate the Cost column depending
upon what's in "TRANSACTION_TYPE".

I am trying to use an IF statement in the code as follows:

Dim i As Integer
For i = 0 To dt3.Rows.Count - 1
If dt3.Rows(i).Item("TRANSACTION_TYPE").IsNull Then
dt3.Rows(i).Item("Cost") = 0
ElseIf dt3.Rows(i).Item("TRANSACTION_TYPE") = 1 Then
dt3.Rows(i).Item("Cost") = 1
Else
dt3.Rows(i).Item("Cost") = 2
End If

Next

Obviously in the above the Cost calculation is omitted and replaced with a
simple integer for the purposes of testing...

My problem is the first IF statement - how do I identify the contents of
the
"TRANSACTION_TYPE" as NULL - the above is one effort which fails with
various different messages such as:

Exception Details: System.MissingMemberException: Public member 'IsNull'
on
type 'DBNull' not found.

OR

Exception Details: System.MissingMemberException: Public member 'IsNull'
on
type 'Short' not found.

I have tried using:
If dt3.Rows(i).Item("TRANSACTION_TYPE") Is Nothing Then

but the two lots of data I have retrieved to gain the first two errors
then
show the same error:

Exception Details: System.InvalidCastException: Operator is not valid for
type 'DBNull' and string "1".

It obviously skips the first "if... is nothing" statement and fails on the
if = 1 statement... I get the impression that my "Is nothing" statement is
not matching the NULL fields in the first place

Hope this makes sense - basically - how do I reference a NULL value in the
above scenario ???

Thanks for your help

Stuart