C# import excel filesheet to sql database error

Question

I have met the error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. I'm using SQL Server 2005 and Visual Studio 2005.

I met this error during the import of an excel filesheet to a table in my sql table. Below is my code:

#region Using directives
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
#endregion Using directives

namespace CSASPNETExcelImportExport
{
public partial class ExcelImport : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    // Get the row counts in SQL Server table. 
    protected int GetRowCounts()
    {
        int iRowCount = 0;

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=<IP>;Initial Catalog=SOD;Persist Security Info=True;User ID=<username>;Password=<password>"].ToString()))
        {
            SqlCommand cmd = new SqlCommand("select count(*) from <database>", conn);
            conn.Open();

            // Execute the SqlCommand and get the row counts. 
            iRowCount = (int)cmd.ExecuteScalar();
        }

        return iRowCount;
    }

    // Retrieve data from the Excel spreadsheet. 
    protected DataTable RetrieveData(string strConn)
    {
        DataTable dtExcel = new DataTable();

        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            // Initialize an OleDbDataAdapter object. 
            OleDbDataAdapter da = new OleDbDataAdapter("select * from <database>", conn);

            // Fill the DataTable with data from the Excel spreadsheet. 
            da.Fill(dtExcel);
        }

        return dtExcel;
    }

    // Import the data from DataTable to SQL Server via SqlBulkCopy 
    protected void SqlBulkCopyImport(DataTable dtExcel)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Data Source=<ip>;Initial Catalog=SOD;Persist Security Info=True;User ID=<username>;Password=<password>"].ToString()))
        {
            // Open the connection. 
            conn.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
            {
                // Specify the destination table name. 
                bulkCopy.DestinationTableName = "<database>";

                foreach (DataColumn dc in dtExcel.Columns)
                {
                    // Because the number of the test Excel columns is not  
                    // equal to the number of table columns, we need to map  
                    // columns. 
                    bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                }

                // Write from the source to the destination. 
                bulkCopy.WriteToServer(dtExcel);
            }
        }
    }

    protected void btnImport_Click(object sender, EventArgs e)
    {
        // Before attempting to import the file, verify 
        // that the FileUpload control contains a file. 
        if (fupExcel.HasFile)
        {
            // Get the name of the Excel spreadsheet to upload. 
            string strFileName = Server.HtmlEncode(fupExcel.FileName);

            // Get the extension of the Excel spreadsheet. 
            string strExtension = Path.GetExtension(strFileName);

            // Validate the file extension. 
            if (strExtension != ".xls" && strExtension != ".xlsx")
            {
                Response.Write("<script>alert('Please select a Excel spreadsheet to import!');</script>");
                return;
            }

            // Generate the file name to save. 
            string strUploadFileName = "~/UploadFiles/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;

            // Save the Excel spreadsheet on server. 
            fupExcel.SaveAs(Server.MapPath(strUploadFileName));

            // Generate the connection string for Excel file. 
            string strExcelConn = "";

            // There is no column name In a Excel spreadsheet.  
            // So we specify "HDR=YES" in the connection string to use  
            // the values in the first row as column names.  
            if (strExtension == ".xls")
            {
                // Excel 97-2003 
                strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";

                //if the above doesn't work, you may need to prefix OLEDB; to the string, e.g.
                //strExcelConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<URL>" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";
            }
            else
            {
                // Excel 2007 
                strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<URL>" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
            }

            DataTable dtExcel = RetrieveData(strExcelConn);

            // Get the row counts before importing. 
            int iStartCount = GetRowCounts();

            // Import the data. 
            SqlBulkCopyImport(dtExcel);

            // Get the row counts after importing. 
            int iEndCount = GetRowCounts();

            // Display the number of imported rows.  
            lblMessages.Text = Convert.ToString(iEndCount - iStartCount) + " rows were imported into Person table";

            if (rblArchive.SelectedValue == "No")
            {
                // Remove the uploaded Excel spreadsheet from server. 
                File.Delete(Server.MapPath(strUploadFileName));
            }
        }
    }
}
}

Is it because my connectionstring error?

---

I have changed my connection strings as of what Nathan suggested. Now I met the following error:

http://i.stack.imgur.com/nWyte.png

What error is it this time? D:

Answer 1

It looks like you actually have several things wrong with your connection strings. For one thing, Excel connection strings should not include an "Initial Catalog", and they should include a Data Source referring to the file, not a server.

Try this instead:

        // There is no column name In a Excel spreadsheet.  
        // So we specify "HDR=YES" in the connection string to use  
        // the values in the first row as column names.  
        if (strExtension == ".xls")
        {
            // Excel 97-2003 
            strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";

            //if the above doesn't work, you may need to prefix OLEDB; to the string, e.g.
            //strExcelConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 8.0;HDR=Yes;\"";
        }
        else
        {
            // Excel 2007 
            strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strUploadFileName) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
        }

Fyi, http://connectionstrings.com is a good resource for these kind of things.

Answer 2

Try removing "Persist Security Info=True" from your connection strings.

http://support.microsoft.com/kb/269495