import text getting null

[Guest]

hey all,

i have a winapp that imports a text file into a typed dataset using the data
adapter. some of the values are null. is there a way to remove the null
values and put an empty string for character fields and 0 for numeric fields?

is there a possible expression i can use in the typed dataset properties for
each field to rid the nulls?

thanks,
rodchar

 

[Jerry H.]

For each Datatable, you should be able to set the Default value of your
fields, and also set a value that is returned if the field is Null.
Have you tried using those properties.?

 

[Guest]

For Each _dr In oDsTXLIBR.TXFP3003
Console.Write(_dr.TF_EXEMPT)
Next

*** Command-Immediate Window ***
?oDsTXLIBR.Tables("TXFP3003").Rows(0)("TF_EXEMPT")
{System.DBNull}
[System.DBNull]: {System.DBNull}
?ROW.TF_EXEMPT
"TEST"


Why are there 2 different values? I put "TEST" in the NullValue property and
it worked like it should but why are there 2 different values where it looks
both commands should be pointed to the same location?

 

[Guest]

FYI - i am aware of the following syntax differences:
_dr.TF_EXEMPT is suppose to be the same as ROW.TF_EXEMPT
i was doing some testing and forgot to change back

For Each _dr In oDsTXLIBR.TXFP3003
Console.Write(_dr.TF_EXEMPT)
Next

*** Command-Immediate Window ***
?oDsTXLIBR.Tables("TXFP3003").Rows(0)("TF_EXEMPT")
{System.DBNull}
[System.DBNull]: {System.DBNull}
?ROW.TF_EXEMPT
"TEST"


Why are there 2 different values? I put "TEST" in the NullValue property and
it worked like it should but why are there 2 different values where it looks
both commands should be pointed to the same location?

For each Datatable, you should be able to set the Default value of your
fields, and also set a value that is returned if the field is Null.
Have you tried using those properties.?

 

[Guest]

ok, disregard original post. However, in debug mode i can see the default
value for the field is there like it should be but when it goes to insert
that record in the database i get an oledb exception on that particular field
and record saying "trying to insert a null" when to me it looks like my
default value is there according to debug mode.

why does it show my default value in debug but still thinks it's null when
inserting into a database?

FYI - i am aware of the following syntax differences:
_dr.TF_EXEMPT is suppose to be the same as ROW.TF_EXEMPT
i was doing some testing and forgot to change back

For Each _dr In oDsTXLIBR.TXFP3003
Console.Write(_dr.TF_EXEMPT)
Next

*** Command-Immediate Window ***
?oDsTXLIBR.Tables("TXFP3003").Rows(0)("TF_EXEMPT")
{System.DBNull}
[System.DBNull]: {System.DBNull}
?ROW.TF_EXEMPT
"TEST"


Why are there 2 different values? I put "TEST" in the NullValue property and
it worked like it should but why are there 2 different values where it looks
both commands should be pointed to the same location?

For each Datatable, you should be able to set the Default value of your
fields, and also set a value that is returned if the field is Null.
Have you tried using those properties.?

 

[Guest]

here's more info:

?dr.TF_EXEMPT
"TEST"

?dr.ItemArray
{Length=9}
(0): "T"
(1): 116754D
(2): 160000D
(3): 0D
(4): 0D
(5): 0D
(6): 160000D
(7): 160D
(8): {System.DBNull}

i would think element 8 would have test in it?

 

[Jay B. Harlow [MVP - Outlook]]

Rodchar,The Default value is used when you are adding a field with no value (with
Nothing). I suspect the driver you are using is adding the row with a value
of DBNull, hence your problem.

The "easiest" way may be to write a function that looks at each row & column
and assign the Default value to that column if the value is currently
DBNull.

Something like (untested):

Dim table As DataTable

For Each row As DataRow In table.Rows
For Each column As DataColumn in table.Columns
If row(column) Is DbNull.Value Then
row(column) = column.DefaultValue
End If
Next
Next

Alternatively you may be able to use a DataView to limit yourself to only
rows that need to be changed, especially if you are only concerned with one
or two columns...

Hope this helps
Jay

 

[Jay B. Harlow [MVP - Outlook]]

Doh!

You can use DataRow.IsNull to compare to DBNull, which may be cleaner...

For Each row As DataRow In table.Rows
For Each column As DataColumn in table.Columns
If row.IsNull(column) Then
row(column) = column.DefaultValue
End If
Next
Next

Jay

 

[Guest]

ok i tried the code snippet you provided and my results are the same. I don't
understand what i could be doing wrong.

For Each dr As DsTXLIBR.TXFP3003Row In oDsTXLIBR.TXFP3003.Rows
For Each column As DataColumn In oDsTXLIBR.TXFP3003.Columns
If dr.IsNull(column) Then
dr(column) = column.DefaultValue
End If
Next
Next


?oDsTXLIBR.TXFP3003(0).TF_EXEMPT
"TEST"
?oDsTXLIBR.TXFP3003(0).ItemArray
{Length=9}
(0): "T"
(1): 116754D
(2): 160000D
(3): 0D
(4): 0D
(5): 0D
(6): 160000D
(7): 160D
(8): {System.DBNull}

please advise.

 

[Jay B. Harlow [MVP - Outlook]]

rodchar,
You do have the DefaultValue for each column set to a value? The "default"
DefaultValue is DBNull... Your earlier discussion suggested that you have
the property set appropriately...

Hope this helps
Jay

 

[james]

You have to set your "Default Value" to something. Or else set it to DbNull.Value (actual setting , does not mean
DbNull.somevalue) Like this:
If myvalue Is DBNull.Value Then

myvalue = DBNull.Value

End If

What you end up with in the empty column (by default without setting TableStyles) will be: <NULL> instead of a blank
column(field).

james

 

[Guest]

Awwesome, that worked!!

thanks for the great help

 

[Guest]

please allow me to make sure i understand all this correctly.

i have a text file that i'm importing into a dataset.

the resulting dataset contains nulls that need to be replaced with zeros for
numeric values and empty stings for characters.

if i go into the dataset designer into each of the element's Null Value and
Default Value properties and modify them as needed, will i still need to loop
thru the dataset in my code to make these assignments?

 

[james]

Glad it worked.
james

Awwesome, that worked!!

thanks for the great help

 

[Paul Clement]

¤ hey all,
¤
¤ i have a winapp that imports a text file into a typed dataset using the data
¤ adapter. some of the values are null. is there a way to remove the null
¤ values and put an empty string for character fields and 0 for numeric fields?
¤
¤ is there a possible expression i can use in the typed dataset properties for
¤ each field to rid the nulls?
¤

You can use the IIf function with the IsNull function in your SQL statement to return a value other
than Null.

IIf(IsNull([SomeField]),"") As ReturnVal


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)