SQL BULK COPY error: invalid attempt to call fieldcount when reader is closed Error

Problem Description:

The error " invalid attempt to call fieldcount when reader is closed" can occur when bulk copying data to Sql server table using SqlBulkCopy.

Consider the following example:

The following example throws the error "Invalid attempt to call fieldcount when reader is closed"

Example:

' Retrieval of the data from the source ODBC connection table

Dim sODBCCmd As New OdbcCommand(strODBC, sODBCConn)

sODBCConn.Open()

OdbcDr = sODBCCmd.ExecuteReader()

sODBCConn.Close() ' We are Closing the connection

' SQL Bulk Copy

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[" & sTableName & "]"

Try

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message) ' Error Invalid attempt to call fieldcount when reader is closed

End Try

End Using

Possible Reasons:

The data reader is closed.

sODBCConn.Close() (which closes the connection) after ExecuteReader() is called and thus closing the
datareader (the datareader is tied to the connection).

Remarks: The connection.Close() statement closes the data reader as well.

Solution:

We must either keep the connection open and close it and the reader when you are done with it, or make use of a dataset.

sODBCConn.Open()

OdbcDr = sODBCCmd.ExecuteReader()

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[" & sTableName & "]"

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message) ' Error Throws Here

Finally

OdbcDr.Close()

sODBCConn.Close() ' Should be closed Here to avoid the error

End Try

End Using