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

.net sqlbulkcopy vb.net

Question

I'm attempting to use bulk upload to import data from a CSV file into a database; although I can copy varchar columns, I can't import integer fields. 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
1
0
6/11/2015 8:31:39 AM

Popular Answer

The columns in your data are the default ones. The name or type are never determined by you.

Check out the MSDN example to see how to create a DataColumn that matches your database field. The kind should be the same at the very least.

Before copying the example, you must first comprehend the issue and the language. Set the column you are adding to the data table, not any arbitrary column you may not need later.

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, then, are you doing this in the loop? Perform it before to data reading.

1
4/9/2014 6:11:03 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow