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_TY PE". I need to loop
through all the rows that are retrievd calculate the Cost column depending
upon what's in "TRANSACTION_TY PE".

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).Ite m("TRANSACTION_ TYPE").IsNull Then
dt3.Rows(i).Ite m("Cost") = 0
ElseIf dt3.Rows(i).Ite m("TRANSACTION_ TYPE") = 1 Then
dt3.Rows(i).Ite m("Cost") = 1
Else
dt3.Rows(i).Ite m("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_TY PE" as NULL - the above is one effort which fails with
various different messages such as:

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

OR

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

I have tried using:
If dt3.Rows(i).Ite m("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.InvalidC astException: 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.V alue instead. This will then compare it to the
null value returned by the database.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

"Stuart" <St****@discuss ions.microsoft. com> wrote in message
news:7C******** *************** ***********@mic rosoft.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_TY PE". I need to loop
through all the rows that are retrievd calculate the Cost column depending
upon what's in "TRANSACTION_TY PE".

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).Ite m("TRANSACTION_ TYPE").IsNull Then
dt3.Rows(i).Ite m("Cost") = 0
ElseIf dt3.Rows(i).Ite m("TRANSACTION_ TYPE") = 1 Then
dt3.Rows(i).Ite m("Cost") = 1
Else
dt3.Rows(i).Ite m("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_TY PE" as NULL - the above is one effort which fails with
various different messages such as:

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

OR

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

I have tried using:
If dt3.Rows(i).Ite m("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.InvalidC astException: 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.V alue instead. This will then compare it to the
null value returned by the database.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

"Stuart" <St****@discuss ions.microsoft. com> wrote in message
news:7C******** *************** ***********@mic rosoft.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_TY PE". I need to loop
through all the rows that are retrievd calculate the Cost column depending
upon what's in "TRANSACTION_TY PE".

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).Ite m("TRANSACTION_ TYPE").IsNull Then
dt3.Rows(i).Ite m("Cost") = 0
ElseIf dt3.Rows(i).Ite m("TRANSACTION_ TYPE") = 1 Then
dt3.Rows(i).Ite m("Cost") = 1
Else
dt3.Rows(i).Ite m("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_TY PE" as NULL - the above is one effort which fails with
various different messages such as:

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

OR

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

I have tried using:
If dt3.Rows(i).Ite m("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.InvalidC astException: 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