BulkCopy Error" `The given value of type String from the data source cannot be converted to type int of the specified target column.`

Question

I am trying to import data from CSV to DataBase usingBulkUpload, I am able to copy columns which are varchar but I am not able to import when I try to import integer column. Error:The given value of type String from the data source cannot be converted to type int of the specified target column.

UPDATE

 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim dt As New DataTable()
        Dim line As String = Nothing
        Dim i As Integer = 0

        Using sr As StreamReader = File.OpenText(txtfileName.Text)
            line = sr.ReadLine()
            Do While line IsNot Nothing
                Dim data() As String = line.Split(","c)
                If data.Length > 0 Then
                    If i = 0 Then
                        If i > 4 Then
                            Dim column As DataColumn = New DataColumn
                            If i = 6 Then
                                column.DataType = System.Type.GetType("System.Decimal")
                                column.AllowDBNull = False
                                column.Caption = "Price"
                                column.ColumnName = "Price"
                                column.DefaultValue = 0
                            End If
                            dt.Columns.Add(column)
                        Else
                            For Each item In data
                                dt.Columns.Add(New DataColumn())

                            Next item
                            i += 1
                        End If
                    End If
                    Dim row As DataRow = dt.NewRow()
                    row.ItemArray = data
                    dt.Rows.Add(row)
                End If
                line = sr.ReadLine()
            Loop
        End Using


        Using cn As New SqlConnection("Data Source=xx.xxx.in;Initial catalog=xxx;User Id=xxx;Password=xx@xx;")
            cn.Open()
            Using copy As New SqlBulkCopy(cn)
                copy.ColumnMappings.Add(0, 0)
                copy.ColumnMappings.Add(1, 1)
                copy.ColumnMappings.Add(2, 2)
                copy.ColumnMappings.Add(3, 3)
                copy.ColumnMappings.Add(4, 4)
                copy.ColumnMappings.Add(5, 5)
                copy.ColumnMappings.Add(6, 6)

                copy.DestinationTableName = "tbl_Bonds"
                'dt.Columns(5).DataType = GetType(Decimal)
                'dt.Columns(6).DataType = GetType(Decimal)
                copy.WriteToServer(dt)
            End Using
        End Using

    End Sub

the data-type of 6th column is `System.Decimal` or integer? try `System.Int32` instead. — ssett, Apr 09 '14 at 06:15
Please remove the whole data table building from your loop. It's wrong and you make it worse with every iteration of your code. Build your data table. Once. Before the loop. Scrap the useless `i` variable. — nvoigt, Apr 09 '14 at 06:38

nvoigt · Answer 1 · 2014-04-09T06:11:03.820

Your DataColumns are default columns. You never set the name or type.

Have a look at the example in the MSDN on how to create a DataColumn that matches your database column. At least the type should be the same.

You need to understand the language and the problem first, before pasting the example. You need to set the column your are inserting into the data table, not some random column you are not using afterwards:

Dim column As DataColumn = New DataColumn
  If i = 6 Then
    column.DataType = System.Type.GetType("System.Decimal")
    column.AllowDBNull = False
    column.Caption = "Price"
    column.ColumnName = "Price"
    column.DefaultValue = 0
  End If
dt.Columns.Add(column)

Why are you doing this in the loop anyway? Do it before reading data.

BulkCopy Error" `The given value of type String from the data source cannot be converted to type int of the specified target column.`

1 Answers1