Public Sub SQLBulkCopyCode()
        Dim tempReader As SqlDataReader
        Dim BufferRowCount As UInt16

        Try
            If inSQLBulkCopyCode Then Exit Sub
            inSQLBulkCopyCode = True
            While SQLBufferConn.State <> Data.ConnectionState.Open
                PostMessageToStatusTab(vbNewLine & "Lost Connection to SQLBufferConn")
                If ConnectToSQL(2) Then
                    PostMessageToStatusTab(vbNewLine & "Restablished Connection to SQLBufferConn")
                Else
                    PostMessageToStatusTab(vbNewLine & "Unable to Restablish Connection to SQLBufferConn")
                End If
                System.Threading.Thread.Sleep(500)
            End While


            While SQLDataConn.State <> Data.ConnectionState.Open
                PostMessageToStatusTab(vbNewLine & "Lost Connection to SQLDataConn")
                If ConnectToSQL(1) Then
                    PostMessageToStatusTab(vbNewLine & "Restablished Connection to SQLDataConn")
                Else
                    PostMessageToStatusTab(vbNewLine & "Unable to Restablish Connection to SQLDataConn")
                End If
                System.Threading.Thread.Sleep(500)
            End While

            ' Get the number of rows in the buffer table
            SQLBufferReadCmd = New SqlCommand("SELECT COUNT(*) FROM " & TestCell, SQLBufferConn)
            BufferRowCount = SQLBufferReadCmd.ExecuteScalar

            ' Get all rows from the buffer (should be 100)
            SQLBufferReadCmd = New SqlCommand("SELECT * FROM " & TestCell, SQLBufferConn)
            tempReader = SQLBufferReadCmd.ExecuteReader

            If Not SQLBulkCopyFirstRun Then
                Dim TempCount As UInt16
                ' Create bulk copier
                SQLBulkCopier = New SqlBulkCopy(SQLDataConn)
                SQLBulkCopier.DestinationTableName = TestCell

                ' Map column headers properly
                For TempCount = 0 To tempReader.FieldCount - 1
                    SQLBulkCopier.ColumnMappings.Add(tempReader.GetName(TempCount), tempReader.GetName(TempCount))
                    'PostMessageToStatusTab(tempReader.GetName(TempCount))
                Next
                SQLBulkCopyFirstRun = True
            End If

            ' Start the bulk copy
            SQLBulkCopier.BatchSize = BufferRowCount
            SQLBulkCopier.WriteToServer(tempReader)


            ' Delete all data from the buffer table
            SQLBufferReadCmd = New SqlCommand("DELETE FROM " & TestCell, SQLBufferConn)
            SQLBufferReadCmd.ExecuteNonQuery()

            ' Reset the buffer size counter
            SQLBufferSize = 0
        Catch ex As Exception
            SQLBulkCopyError = True
            PostMessageToStatusTab("Error in SQL bulk Copy!" & vbNewLine & vbNewLine & ex.ToString)
            TextMessage("Error in SQL bulk Copy!" & vbNewLine & vbNewLine & ex.ToString)
        Finally
            inSQLBulkCopyCode = False
            tempReader.Close()
        End Try
   End sub

quote:

---

Originally posted by sunitabeck

I could not 100% follow the logic you are trying to implement with the SQLBulkCopyFirstRun boolean. Is it a class variable that is initialized somewhere else? I suspect the error may have something to do with that variable.

What I would suggest is to start with the code from the Microsoft Example and work your way to implementing the SQLBulkCopyFirstRun logic.

---