SqlBulkCopy not working, no errors

c# datatable sql sqlbulkcopy vb.net

Question

I'm reading data from an Excel file or CSV. I take that data and create a datatable. I then merge that datatable with the datatable created from the original database. The merge works and I have already sorted out all of the datatypes and column names. I have a ton of links, but most of them boil down to datatype and column name/column text case.

No errors. Everything runs smoothly. The datatable I'm trying to bulk copy is correct in the VS table viewer. When I check in SQLExpress no changes have been made. I am using the same connection string that I have been for the rest of the project which works (row delete, add, edit, etc).

dt.Merge(dtnew)

    Using destinationConnection As SqlConnection = _
                   New SqlConnection(sConnectionString)
        destinationConnection.Open()

        ' Set up the bulk copy object.  
        ' The column positions in the source data reader  
        ' match the column positions in the destination table,  
        ' so there is no need to map columns. 

        Using bulkCopy As SqlBulkCopy = _
          New SqlBulkCopy(destinationConnection)
            bulkCopy.DestinationTableName = _
            "dbo.TableName"

            Try
                ' Write from the source to the destination.
                bulkCopy.WriteToServer(dt)

            Catch ex As Exception
                Console.WriteLine(ex.Message)

            Finally
                ' Close the SqlDataReader. The SqlBulkCopy 
                ' object is automatically closed at the end 
                ' of the Using block.
            End Try
        End Using

    End Using
End Sub
1
2
2/4/2014 2:24:06 PM

Accepted Answer

Do the column mapping also..

bulkCopy.ColumnMappings.Add("source column name,"destination column name" )

or if you have same column name in dt and dbo.Tablename then you can use following code

For Each clmn As DataColumn In dt.Columns
     bulkCopy.ColumnMappings.Add(clmn.ColumnName, clmn.ColumnName)
Next
4
2/5/2014 5:53:25 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