How Do I Solve The Error Column Mapping Not Matched

See more:

C#

ASP.NET

I am getting error called "The given ColumnMapping does not match up with any column in the source or destination." I am tryng to copy excel data into sql server2008 db table called excel.
My source code is below.
---------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{
    
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    
    protected void btnUpload_Click(object sender, EventArgs e)
    {

        //Get path from web.config file to upload            
        //string FilePath = ConfigurationManager.AppSettings["FilePath"].ToString();            
        string filename = string.Empty;
        //To check whether file is selected or not to upload            
        if (FileUploadToServer.HasFile)
        {
            try
            {
                string[] allowdFile = { ".xls", ".xlsx" };
                //Here we are allowing only excel file so verifying selected file pdf or not                    
                string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
                //Check whether selected file is valid extension or not                    
                bool isValidFile = allowdFile.Contains(FileExt);
                if (!isValidFile)
                {
                    lblMsg.ForeColor = System.Drawing.Color.Red;
                    lblMsg.Text = "Please upload only Excel";
                }
                else
                {
                     System.Threading.Thread.Sleep(8000);
                    //Get file path                            
                    string filePath = Server.MapPath("Files/" + FileUploadToServer.FileName);
                    
                    if (File.Exists(filePath))
                    {
                        File.Delete(filePath);
                    }
                    //Get file name of selected file                            
                    filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
                    //Save selected file into server location                            
                    FileUploadToServer.SaveAs(Server.MapPath("Files/") + filename);
                    
                    
                    //Open the connection with excel file based on excel version                            
                    OleDbConnection con = new OleDbConnection();
                    if (FileExt == ".xls")
                    {
                        HiddenField1.Value = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
                        con.ConnectionString = HiddenField1.Value;

                    }
                    else if (FileExt == ".xlsx")
                    {
                        HiddenField1.Value = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
                        con.ConnectionString = HiddenField1.Value;
                        
                    }
                    con.Open();
                    //Get the list of sheet available in excel sheet 
                    GetExcelSheets(filePath, FileExt);
                    //Get first sheet name                            
                    //string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
                    //Select rows from first sheet in excel sheet and fill into dataset                            
                    lblMsg.Text = "uploaded successfully";
                    Panel1.Enabled = false;
                    Panel2.Visible = true;
                   
                }
            }
            catch (Exception ex)
            {
                lblMsg.Text = "Error occurred while uploading a file: " + ex.Message;
            }
        }
        else
        {
            lblMsg.Text = "Please select a file to upload.";

        }

    }
   

    /*if (FileUpload1.HasFile)
    {
        string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        //string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        //string FilePath = Server.MapPath(FolderPath + FileName);
        string FilePath = Server.MapPath("Files/") + FileUpload1.FileName;
        FileUpload1.SaveAs(FilePath);
        
    }*/

    private void GetExcelSheets(string FilePath, string Extension)
    {
        string filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
        HiddenField1.Value = connection(Server.MapPath("Files/" + FileUploadToServer.FileName),Path.GetExtension(FileUploadToServer.FileName));
        OleDbConnection conStr = new OleDbConnection(HiddenField1.Value);
        
        //Get the Sheets in Excel WorkBook
        //conStr = String.Format(conStr, FilePath);
        //OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        cmdExcel.Connection = conStr;
        conStr.Open();

        //Bind the Sheets to DropDownList
        ddlCategories.Items.Clear();
        ddlCategories.Items.Add(new ListItem("--Select Sheet--", ""));
        //ddlCategories.DataSource = conStr.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        DataTable dtsheet = conStr.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        foreach (DataRow dr in dtsheet.Rows)
        {
            ddlCategories.Items.Add(new ListItem((Convert.ToString(dr["TABLE_NAME"]).Replace("$", "")), Convert.ToString(dr["TABLE_NAME"])));
        }
        //ddlCategories.DataTextField = "TABLE_NAME";
        //ddlCategories.DataValueField = "TABLE_NAME";
        ddlCategories.DataBind();
        conStr.Close();
        
    }

    public string connection(string path,string extension)
    {
        string connstr = "";
        if (extension == ".xls")
        {
            connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
        }
        else if (extension == ".xlsx")
        {
            connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
        }

        return connstr;
    }

   
   protected string getExcelSheet()
    {
        string sheetname = null;
        if (String.IsNullOrEmpty(ddlCategories.SelectedItem.Value))
        {
            Response.Write("<script>alert('Please select the Sheet to upload the Batch')</script>");
        }
        else
        {
            hdn_choosedsheetname.Value = ddlCategories.SelectedItem.Value;
            //sheetname = ddlCategories.SelectedItem.Value;
        }

        return hdn_choosedsheetname.Value;
    }
    /*protected string getExcelSheet()
    {
        string sheetname = ddlCategories.SelectedItem.Value;
        return sheetname;
    }*/

   protected void Button1_Click(object sender, EventArgs e)
   {
        // Create sql connection string
               
            SqlConnection sqlCon = new SqlConnection (ConfigurationManager.ConnectionStrings["conString"].ConnectionString );
            sqlCon.Open ();

            SqlDataAdapter da = new SqlDataAdapter("select * from excel where errormessage IS NOT NULL", sqlCon);
            System.Data. DataTable dtMainSQLData = new System.Data. DataTable ();
            da.Fill ( dtMainSQLData );
            DataColumnCollection dcCollection = dtMainSQLData.Columns ;
             // Export Data into Excel Sheet
            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass ();
             ExcelApp.Application.Workbooks.Add (Type.Missing);
             //ExcelApp.Cells.CopyFromRecordset (objRS);
            for ( int i = 1 ; i < dtMainSQLData.Rows.Count + 1;i++)
            {
                 for ( int j = 1 ; j < dtMainSQLData.Columns.Count + 1;j++)
                {
                    if ( i == 1 )
                        ExcelApp.Cells [i,j] = dcCollection[j - 1].ToString();
                    else
                        ExcelApp.Cells [ i , j ] = dtMainSQLData.Rows [ i - 1 ] [ j - 1 ].ToString();
                 }
             }
             ExcelApp.ActiveWorkbook.SaveCopyAs ( "C:\test.xls" );
             ExcelApp.ActiveWorkbook.Saved = true ;
             ExcelApp.Quit ();
             Response.Write( "Successfully Exported Data into Excel File" );
        }

   protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e)
   {
        OleDbConnection con = new OleDbConnection(HiddenField1.Value);
        //con.ConnectionString = HiddenField1.Value;
        string getExcelSheetName = getExcelSheet();

        OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con);
        //ddlCategories.SelectedItem.Value
        OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
        DataSet ExcelDataSet = new DataSet();
        ExcelAdapter.Fill(ExcelDataSet);
        DataTable csvData = ExcelDataSet.Tables[0];
        if (csvData.Columns.Count > 1)
        {
            String Createtablequery = "CREATE TABLE excel(RowId int Identity(1,1) not null,CName nvarchar(60) null,firstname nvarchar(30) null,lastname nvarchar(40) null,invadd nchar(10) null, Address1 nvarchar(40) null,Address2 nvarchar(40) null, Address3 nvarchar(40) null,Address4 nvarchar(40) null,isprimaryadd nchar(10) null,IsDeleted nchar(10) null,email nvarchar(255) null,DirectoryName nvarchar(40) null";
            int j = 1;
            for ( int i = 11; i < csvData.Columns.Count; i++)
            {
                Createtablequery += ",level" + j + " nvarchar(255) null";
                j++;
            }
            Createtablequery += ",errormessage nvarchar(max) null,rowinserted int null)";
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
            SqlCommand cmd = new SqlCommand("IF EXISTS(SELECT * FROM sys.tables WHERE name = N'excel' AND type = 'U') BEGIN  DROP TABLE excel END", connection);
            SqlCommand cmd1 = new SqlCommand(Createtablequery, connection);
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            cmd.ExecuteNonQuery();
            cmd1.ExecuteNonQuery();
            SqlBulkCopy sbpy = new SqlBulkCopy(connection);
            sbpy.DestinationTableName = "excel";
            foreach (DataColumn dc in csvData.Columns)
            {
                if (dc.Ordinal < 12)
                {
                    switch (dc.Ordinal)
                    {
                        case 0:
                            SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(dc.Ordinal, "CName");
                            sbpy.ColumnMappings.Add(map);
                            break;
                        case 1:
                            SqlBulkCopyColumnMapping map1 = new SqlBulkCopyColumnMapping(dc.Ordinal, "firstname");
                            sbpy.ColumnMappings.Add(map1);
                            break;
                        case 2:
                            SqlBulkCopyColumnMapping map2 = new SqlBulkCopyColumnMapping(dc.Ordinal, "lastname");
                            sbpy.ColumnMappings.Add(map2);
                            break;

                        case 3:
                            SqlBulkCopyColumnMapping map3 = new SqlBulkCopyColumnMapping(dc.Ordinal, "invadd");
                            sbpy.ColumnMappings.Add(map3);
                            break;
                        case 4:
                            SqlBulkCopyColumnMapping map4 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address1");
                            sbpy.ColumnMappings.Add(map4);
                            break;
                        case 5:
                            SqlBulkCopyColumnMapping map5 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address2");
                            sbpy.ColumnMappings.Add(map5);
                            break;
                        case 6:
                            SqlBulkCopyColumnMapping map6 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address3");
                            sbpy.ColumnMappings.Add(map6);
                            break;
                        case 7:
                            SqlBulkCopyColumnMapping map7 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address4");
                            sbpy.ColumnMappings.Add(map7);
                            break;
                        case 8:
                            SqlBulkCopyColumnMapping map8 = new SqlBulkCopyColumnMapping(dc.Ordinal, "isprimaryadd");
                            sbpy.ColumnMappings.Add(map8);
                            break;
                        case 9:
                            SqlBulkCopyColumnMapping map9 = new SqlBulkCopyColumnMapping(dc.Ordinal, "IsDeleted");
                            sbpy.ColumnMappings.Add(map9);
                            break;
                        case 10:
                            SqlBulkCopyColumnMapping map10 = new SqlBulkCopyColumnMapping(dc.Ordinal, "email");
                            sbpy.ColumnMappings.Add(map10);
                            break;
                        case 11:
                            SqlBulkCopyColumnMapping map11 = new SqlBulkCopyColumnMapping(dc.Ordinal, "DirectoryName");
                            sbpy.ColumnMappings.Add(map11);
                            break;
                        default:
                            string destcolumnname = "level" + dc.Ordinal;
                            SqlBulkCopyColumnMapping map12 = new SqlBulkCopyColumnMapping(dc.Ordinal, destcolumnname);
                            sbpy.ColumnMappings.Add(map12);
                            break;

                    }
                }
                else
                {
                    string destcolumnname = "level" + dc.Ordinal;
                    SqlBulkCopyColumnMapping map13 = new SqlBulkCopyColumnMapping(dc.Ordinal, destcolumnname);
                    sbpy.ColumnMappings.Add(map13);
                    

                }
                /*if (dc.Ordinal == 0)
                {
                    SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(dc.Ordinal, "CName");
                    sbpy.ColumnMappings.Add(map);
                }
                else
                {
                    
                }*/
            }
            try
            {
                sbpy.WriteToServer(csvData);
            }
            catch (Exception es)
            {
                Response.Write(es.Message);
                //WriteTextLogFilestatic(es.Message, System.Reflection.MethodBase.GetCurrentMethod().Name);
            }
            finally
            {
                connection.Close();
            }

            //Bind the dataset into gridview to display excel contents                            
            //GridView1.DataSource = ExcelDataSet;
            //GridView1.DataBind();     
            Panel2.Enabled = false;
            ddlCategories.Enabled = false;
            System.Threading.Thread.Sleep(3000);
            lblText.Text = "Data inserted sucessfully";                           
            }
   }
}

-----------------------------------------------------------------------------
source code for aspx is:

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="CS.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Import Excel Data into Database</title>

     <script language="javascript" type="text/javascript">
         var size = 2;
         var id= 0;

         function ProgressBar() {
             if (document.getElementById('<%=FileUploadToServer.ClientID %>').value != "") {
                document.getElementById("divProgress").style.display = "block";
                document.getElementById("divUpload").style.display = "block";
                id = setInterval("progress()", 20);
                return true;
            }
            else {
                alert("Select a file to upload");
                return false;
            }

        }


function progress()
{
    size = size + 1;
    if(size > 299)
    {
        clearTimeout(id);
    }
    document.getElementById("divProgress").style.width =  size + "pt";
    document.getElementById("<%=lblPercentage.ClientID %>").firstChild.data = parseInt(size / 3) + "%";
    }
         </script>
</head>
<body>
    <form id="form1" runat="server">
        <%-- ajax progress bar --%>
        <div>

            <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>
        <script type="text/javascript">
            // Get the instance of PageRequestManager.
            var prm = Sys.WebForms.PageRequestManager.getInstance();
            // Add initializeRequest and endRequest
            prm.add_initializeRequest(prm_InitializeRequest);
            prm.add_endRequest(prm_EndRequest);

            // Called when async postback begins
            function prm_InitializeRequest(sender, args) {
                // get the divImage and set it to visible
                var panelProg = $get('divImage');
                panelProg.style.display = '';
                // reset label text
                var lbl = $get('<%= this.lblText.ClientID %>');
                 lbl.innerHTML = '';

                 // Disable button that caused a postback
                 $get(args._postBackElement.id).disabled = true;
             }

             // Called when async postback ends
             function prm_EndRequest(sender, args) {
                 // get the divImage and hide it again
                 var panelProg = $get('divImage');
                 panelProg.style.display = 'none';

                 // Enable button that caused a postback
                 $get(sender._postBackSettings.sourceElement.id).disabled = false;
             }
        </script>

    <div id="div1" runat="server">
    <asp:Panel ID="Panel1" runat="server">
    <asp:FileUpload ID="FileUploadToServer" Width="300px" runat="server" />

    <asp:Button ID="btnUpload" runat="server" Text="Upload File" OnClientClick="return ProgressBar()" OnClick="btnUpload_Click" style="width: 99px" />
      <div id="divUpload" style="display:none">
        <div  style="width:300pt; text-align:center;">Uploading...</div>
            <div style="width:300pt; height:20px; border:solid 1pt gray">
            <div id="divProgress" runat="server" style="width: 1pt; height: 20px; background-color:Gray;display:none">
            </div></div>
             <div  style="width:300pt; text-align:center;">
                 <asp:Label ID="lblPercentage" runat="server" Text="Label"></asp:Label></div>
            </div>
        <br />
    <asp:Label ID="lblMsg" runat="server" ForeColor="Green" Text=""></asp:Label> </asp:Panel></div>

        <br />
        <asp:Panel ID="Panel2" runat="server" Visible = "false">

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                         <br />
                <div id="dropdown" runat="server">
       <asp:DropDownList ID="ddlCategories" runat="server" AutoPostBack="true"  Height="24px" Width="162px"  OnSelectedIndexChanged="ddlCategories_SelectedIndexChanged">
        </asp:DropDownList></div>&nbsp;&nbsp;
                <div id="divImage" style="display: none">
                    <asp:Image ID="img1" runat="server" ImageUrl="~/images/progress.gif" />
                    Processing...
                </div>
                <br />
                <asp:Label ID="lblText" runat="server" Text="" ForeColor="Green"></asp:Label>
            </ContentTemplate>
        </asp:UpdatePanel>
            </asp:Panel></div><br /><br />
        &nbsp;<asp:Panel ID="Panel3" runat="server">
        <div runat="server" id="divbtn">
                <asp:Button ID="Button1" runat="server" Text="Download" Width="92px" />
            </div>
           </asp:Panel>
        <br />
       <asp:Panel ID="Panel4" runat="server" Visible="false">
    <asp:GridView ID="GridView1" runat="server" EmptyDataText="No record found!" Height="25px">
    <RowStyle Width="175px" />
    <EmptyDataRowStyle BackColor="Silver" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" ForeColor="#003300" />
    <HeaderStyle BackColor="#6699FF" BorderColor="#333333" BorderStyle="Solid" BorderWidth="1px" VerticalAlign="Top" Width="200px"  Wrap="True" /></asp:GridView>
           </asp:Panel>

        <asp:HiddenField ID="HiddenField1" runat="server" />
        <asp:HiddenField ID="hdn_choosedsheetname" runat="server" />

    </form>
</body>
</html>