473,326 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

import text getting null

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
Nov 21 '05 #1
14 1247
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.?

Nov 21 '05 #2
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?

"Jerry H." wrote:
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.?

Nov 21 '05 #3
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

"rodchar" wrote:
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?

"Jerry H." wrote:
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.?

Nov 21 '05 #4
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?
"rodchar" wrote:
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

"rodchar" wrote:
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?

"Jerry H." wrote:
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.?

Nov 21 '05 #5
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?

"Jerry H." wrote:
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.?

Nov 21 '05 #6
Rodchar,
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.?
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
"rodchar" <ro*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com... 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?

"Jerry H." wrote:
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.?

Nov 21 '05 #7
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:eE**************@tk2msftngp13.phx.gbl... Rodchar,
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.?

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
"rodchar" <ro*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
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?

"Jerry H." wrote:
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.?


Nov 21 '05 #8
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]" wrote:
Rodchar,
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.?

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
"rodchar" <ro*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
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?

"Jerry H." wrote:
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.?


Nov 21 '05 #9
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


"rodchar" <ro*****@discussions.microsoft.com> wrote in message
news:60**********************************@microsof t.com...
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]" wrote:
Rodchar,
>> 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.?

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
"rodchar" <ro*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
> 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?
>
>
>
> "Jerry H." wrote:
>
>> 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.?
>>
>>


Nov 21 '05 #10
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

"rodchar" <ro*****@discussions.microsoft.com> wrote in message news:60**********************************@microsof t.com...
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]" wrote:
Rodchar,
>> 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.?

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
"rodchar" <ro*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
> 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?
>
>
>
> "Jerry H." wrote:
>
>> 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.?
>>
>>


Nov 21 '05 #11
Awwesome, that worked!!

thanks for the great help :)

"james" wrote:
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

"rodchar" <ro*****@discussions.microsoft.com> wrote in message news:60**********************************@microsof t.com...
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]" wrote:
Rodchar,
>> 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.?
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
"rodchar" <ro*****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...
> 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?
>
>
>
> "Jerry H." wrote:
>
>> 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.?
>>
>>


Nov 21 '05 #12
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?


"rodchar" wrote:
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

Nov 21 '05 #13
Glad it worked.
james

"rodchar" <ro*****@discussions.microsoft.com> wrote in message news:65**********************************@microsof t.com...
Awwesome, that worked!!

thanks for the great help :)

"james" wrote:
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

Nov 21 '05 #14
On Thu, 24 Feb 2005 09:29:03 -0800, "rodchar" <ro*****@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Vidhya CS | last post by:
Hi , I am trying to export a database from one machine ie linux, and import the same database to another machine ie ,solaris . I exported the database using the following command . mysqldump...
3
by: ch424 | last post by:
Hi there, I'm using Python 2.4.1 on Ubuntu Linux, and I'm having problems extending python in C: The C code is below: #include <Python.h> #include "ni488.h"
1
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my...
20
by: Steve Jorgensen | last post by:
Hi all, I've just finished almost all of what has turned out to be a real bear of a project. It has to import data from a monthly spreadsheet export from another program, and convert that into...
4
by: Steve Jorgensen | last post by:
I'm restarting this thread with a different focus. The project I'm working on now id coming along and will be made to work, and it's too late to start over with a new strategy. Still, I'm not...
8
by: Roy | last post by:
Hello, As a part of a data dump from a source,I will be getting a text file which has to imported into Access table. But the file dump from Unix is kinda convoluted and inspite of using the...
3
by: bradjensmith | last post by:
I am importing a pipe delimited text file into MS Access 2002. The text file contains | | aka |space| The resulting table when imported converts the space to a null value. Any suggestions on...
6
by: Romulo NF | last post by:
Greetings again to everyone, Im back to show this grid componenet i´ve developed. With this grid you can show the data like a normal table, remove the rows that you need, add rows, import data,...
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.