none
VB.net 2005 SQl Bulk Copy - Does not copy all rows. RRS feed

  • Question

  • Dim sql As String = "SELECT " & Store & " As store, * from ideal_usage where cycle_date = '" & CycleDate & "'"
            Dim cnSource As New SqlConnection(CSX)
            Dim cnDest As New SqlConnection(CSXCorp)
            Dim cmdSource As New SqlCommand(sql, cnSource)
            Dim cmdDest As New SqlBulkCopy(cnDest)
            cmdDest.DestinationTableName = "master_ideal_usage"
            cmdSource.CommandType = CommandType.Text
            cnSource.Open()
            cnDest.Open()
            Dim DataReader As SqlDataReader = cmdSource.ExecuteReader(CommandBehavior.CloseConnection)
            DataReader.Read()
            cmdDest.WriteToServer(DataReader,
            cnSource.Close()
            cnDest.Close()

    The preceeding code works as expected except that the first record in the data reader does not get copied to the destination.  No exception is thrown, however the record is ignored.  The first record has no constraints that it will no allow it to be copied to destionation. 

    Am I missing somehtng here, does anyone have any suggestions.

    Thanks,
    Brock
    Tuesday, September 18, 2007 7:29 PM

Answers

  • I think your DataReader.Read() command is the issue.

    When you issue DataReader.Read(), it is pulling the first row out and leaving the SqlBulkCopy to start with the second row.

     

    This is what the reference says:

    The copy operation starts at the next available row in the reader. Most of the time, the reader was just returned by ExecuteReader or a similar call, so the next available row is the first row.

    Wednesday, September 19, 2007 1:39 PM

All replies

  • I think your DataReader.Read() command is the issue.

    When you issue DataReader.Read(), it is pulling the first row out and leaving the SqlBulkCopy to start with the second row.

     

    This is what the reference says:

    The copy operation starts at the next available row in the reader. Most of the time, the reader was just returned by ExecuteReader or a similar call, so the next available row is the first row.

    Wednesday, September 19, 2007 1:39 PM
  • Any thoughts on how to get it to start copying from the first record?
    Wednesday, September 19, 2007 3:57 PM
  • Like I tried to say in the previous post, remove DataReader.Read() from your code.

    Wednesday, September 19, 2007 4:45 PM
  • Sorry,

    I misuderstood you.  That was my problem, and makes sense to why that works that way.


    Thanks for your help!
    Wednesday, September 19, 2007 6:43 PM