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
For more details, visit http://vijirajkumar.blogspot.com/2009/08/sql-bulk-copy-error-invalid-attempt-to.html