I get an eror in uploading excel db file to sql server in winforms C#

Question

I am trying to create an Excel data uploader to upload Excel files to SQL Server using Winforms in C#.

After bulkcopy.WriteToServer(dr); I get this error :

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
The Connection for viewing your linked Microsoft Excel worksheet was lost.

I got information about regedit here and tried to follow it but still the same error:

class Pass
{
    static string _excelfilepath;

    public static string excelfilepath { get { return _excelfilepath; } set { _excelfilepath = value; } }

    public void importdatafromexcel()
    {
        //declare variables - edit these based on your particular situation
        string ssqltable = "tStudent";
        // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
        string myexceldataquery = "select idnum,fname,gname,mname,coacro,year,yrstat,sex,stat,telno,addr1,addr2,addr3,dbirth,mothname,fathname,civstat,religion,hssch from [masterlist$]";
        try
        {
            //create our connection strings
            string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + _excelfilepath +
            ";extended properties=" + "\"excel 8.0;hdr=yes;\"";
            string ssqlconnectionstring = @"Data Source=LYNDON-PC\LYNDON;Initial Catalog=trial;Persist Security Info=True;User ID=sa;Password=14323531";
            //execute a query to erase any previous data from our destination table
            string sclearsql = "delete from " + ssqltable;
            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
            SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();
            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
            bulkcopy.DestinationTableName = ssqltable;
            while (dr.Read())
            {
                bulkcopy.WriteToServer(dr);
            }

            oledbconn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

What does the error mean and get the code to upload my Excel file to my table in my database in SQL Server...

Answer 1

I think you should use

bulkcopy.WriteToServer(dr);

instead of using

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