CODEWITHSUNDEEP
c#sqlvb.netdatatablesqlbulkcopy
ZL1Corvette
ZL1Corvette

Reputation: 415

SqlBulkCopy not working, no errors

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

Upvotes: 3

Views: 9770

Answers (1)

Sarvesh Mishra
Sarvesh Mishra

Reputation: 2072

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

Upvotes: 6

Related Questions