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.

 

[Guest]

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]

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

 

[Guest]

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.SqlClient.SqlBulkCopy = New
System.Data.SqlClient.SqlBulkCopy(cn, SqlBulkCopyOptions.TableLock,
Nothing)

bc.BatchSize = dt.Rows.Count
cn.Open()
bc.DestinationTableName = "test"
bc.WriteToServer(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]

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.

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.SqlClient.SqlBulkCopy = New
System.Data.SqlClient.SqlBulkCopy(cn, SqlBulkCopyOptions.TableLock,
Nothing)

bc.BatchSize = dt.Rows.Count
cn.Open()
bc.DestinationTableName = "test"
bc.WriteToServer(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?