SqlBulkCopy class doesn't like nulls

[Chris]

I have created a datatable from a csv file. One of the columns is an integer
which sometimes can be blank. When it is blank I add a dbnull.value to the
column when I add to the datatable. This doesn't throw an error but when I
do my bulk insert I get a 'data input' error. When I replace the nulls with
a value it works. What am I doing wrong. Sometime the integer field in the
database is null. Regards, Chris.

[Alexey Smirnov]

Re: SqlBulkCopy class doesn't like nulls

On Mar 10, 2:18 pm, "Chris" <nos...@nospam. comwrote:

I have created a datatable from a csv file. One of the columns is an integer
which sometimes can be blank. When it is blank I add adbnull.value to the
column when I add to the datatable. This doesn't throw an error but when I
do my bulk insert I get a 'data input' error. When I replace the nulls with
a value it works. What am I doing wrong. Sometime the integer field in the
database is null. Regards, Chris.

hmmm... does the destination column of the db-table allow null values?

[Chris]

Re: SqlBulkCopy class doesn't like nulls

Yes it does. The was the first thing I checked. Regards, Chris.

"Alexey Smirnov" <alexey.smirnov @gmail.comwrote in message
news:1173544660 .504347.318010@ s48g2000cws.goo glegroups.com.. .

On Mar 10, 2:18 pm, "Chris" <nos...@nospam. comwrote:

>I have created a datatable from a csv file. One of the columns is an
>integer
>which sometimes can be blank. When it is blank I add adbnull.value to the
>column when I add to the datatable. This doesn't throw an error but when
>I
>do my bulk insert I get a 'data input' error. When I replace the nulls
>with
>a value it works. What am I doing wrong. Sometime the integer field in
>the
>database is null. Regards, Chris.

>
hmmm... does the destination column of the db-table allow null values?
>

[Alexey Smirnov]

Re: SqlBulkCopy class doesn't like nulls

On Mar 10, 6:12 pm, "Chris" <nos...@nospam. comwrote:

Yes it does. The was the first thing I checked. Regards, Chris.
>

Let's try it...

I've created a table

CREATE TABLE [dbo].[test] (
[col1] [int] NULL ,
[col2] [int] NULL ,
[col3] [varchar] (50) NULL
) ON [PRIMARY]
GO

and wrote some quick code (VB version here)

Dim dt As DataTable = New DataTable()

dt.Columns.Add( New DataColumn())
dt.Columns.Add( New DataColumn())
dt.Columns.Add( New DataColumn())

Dim row As DataRow = dt.NewRow()

row(0) = 1
row(1) = DBNull.Value
row(2) = "test"
dt.Rows.Add(row )

Dim cn As SqlConnection = New SqlConnection(" .......")
Dim bc As System.Data.Sql Client.SqlBulkC opy = New
System.Data.Sql Client.SqlBulkC opy(cn, SqlBulkCopyOpti ons.TableLock,
Nothing)

bc.BatchSize = dt.Rows.Count
cn.Open()
bc.DestinationT ableName = "test"
bc.WriteToServe r(dt)
cn.Close()
bc.Close()

Once it's executed, I see the a new row in the test table. The second
column has NULL value in it.

Are you sure that this does not work for you?

[Chris]

Re: SqlBulkCopy class doesn't like nulls

I don't have time to try it until tomorrow but this looks like i'm doing
something wrong. I will post when I find out what it is. :)

"Alexey Smirnov" <alexey.smirnov @gmail.comwrote in message
news:1173553504 .417863.301780@ t69g2000cwt.goo glegroups.com.. .

On Mar 10, 6:12 pm, "Chris" <nos...@nospam. comwrote:

>Yes it does. The was the first thing I checked. Regards, Chris.
>>

>
Let's try it...
>
I've created a table
>
CREATE TABLE [dbo].[test] (
[col1] [int] NULL ,
[col2] [int] NULL ,
[col3] [varchar] (50) NULL
) ON [PRIMARY]
GO
>
and wrote some quick code (VB version here)
>
Dim dt As DataTable = New DataTable()
>
dt.Columns.Add( New DataColumn())
dt.Columns.Add( New DataColumn())
dt.Columns.Add( New DataColumn())
>
Dim row As DataRow = dt.NewRow()
>
row(0) = 1
row(1) = DBNull.Value
row(2) = "test"
dt.Rows.Add(row )
>
Dim cn As SqlConnection = New SqlConnection(" .......")
Dim bc As System.Data.Sql Client.SqlBulkC opy = New
System.Data.Sql Client.SqlBulkC opy(cn, SqlBulkCopyOpti ons.TableLock,
Nothing)
>
bc.BatchSize = dt.Rows.Count
cn.Open()
bc.DestinationT ableName = "test"
bc.WriteToServe r(dt)
cn.Close()
bc.Close()
>
Once it's executed, I see the a new row in the test table. The second
column has NULL value in it.
>
Are you sure that this does not work for you?
>