Unable to read first row from Excel using SqlBulkCopy

.net asp.net c# sqlbulkcopy

Question

I am using following code to update an Excel file into SQL Server. Code is working but it is not able to insert first row into table.

OleDbConnection OleDb = new OleDbConnection(ConnectionString);
OleDbCommand OleDbCmm = new OleDbCommand(Query,OleDb);
OleDbDataReader OleDbdr;
OleDb.Open();

if (OleDb.State == ConnectionState.Open)
{
    OleDbdr = OleDbCmm.ExecuteReader();
    SqlBulkCopy BulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["connstring"].ToString());
    BulkCopy.DestinationTableName = "TempTable";

    if (OleDbdr.Read())
    {
       BulkCopy.WriteToServer(OleDbdr);
    }
 }

 OleDb.Close();

Popular Answer

Even I was facing same problem this is because I was using Read() Method like below.

           while (dr.Read())
            {
                bulkcopy.WriteToServer(dr);
            }

Solution to above problem is remove dr.Read() method and while loop use bulkcopy.WriteToServer(dr) without any condition and Read() Method.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why