The given ColumnMapping does not match up with any column in the source or destination for huge data

Question

I am getting error on bind grid

string path = string.Concat(Server.MapPath("~/UploadFile/" + FileUpload1.FileName));
FileUpload1.SaveAs(path); 
string excelCS = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

using (OleDbConnection con = new OleDbConnection(excelCS))
{
    OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);
    con.Open();
    DataTable dt = new DataTable();
    dt.Load(cmd.ExecuteReader());
    // Create DbDataReader to Data Worksheet
    DbDataReader dr = cmd.ExecuteReader();
    // SQL Server Connection String
    string CS = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
    SqlBulkCopy bulkInsert = new SqlBulkCopy(CS);

    bulkInsert.DestinationTableName = "tbl_data";
    for (int i = 0; i < dt.Columns.Count; i++)
    {
         bulkInsert.ColumnMappings.Add(i, i);
    }
    bulkInsert.WriteToServer(dr);
    BindGridView();
    con.Close();
    lblerror.Text = "Your file uploaded successfull"

EDIT:

private void BindGridView() 
{ 
    string conString = 
    ConfigurationManager.ConnectionStrings["con"].ConnectionString; 
    SqlConnection con = new SqlConnection(conString); 
    SqlCommand cmd = new SqlCommand("select * from tbl_data", con); 
    con.Open(); 
    gvUpload.DataSource = cmd.ExecuteReader(); 
    gvUpload.DataBind(); 
    con.Close(); 
}

Could you add `Console.WriteLine(bulkInsert.ColumnMappings[i].Name)` in the for loop and then, in your db IDE, sp_columns 'tbl_data', or it's equivalent. Then, copy both sets of columns, ordered, and compare them. The exception is telling you what the issue is, you now need to debug it. It would be easy to fix if you did not do `select * from ...`, use a column list, then you won't get this error. — Simon Wilson, Sep 02 '19 at 20:12
As a side note, why wrap the connection in a `using()` block and not the `cmd`, `dt`, `dr` and `bulkInsert`...and name the variables so others who have to read it can also "get it"? — Simon Wilson, Sep 02 '19 at 20:15
@SimonWilson where i should add Console.WriteLine(bulkInsert.ColumnMappings[i].Name) i am not getting — Nits Patel, Sep 02 '19 at 20:15
cmd = command , dt = data table, dr = data reder, bulkinsert= insert with oeled data insertion — Nits Patel, Sep 02 '19 at 20:18
Yeah I "get it", but I have coded for 17 years, what about the poor noob straight out of school? He will think this is the norm and start to do it too, therefore spreading crap coding conventions. Just my opinion like. — Simon Wilson, Sep 02 '19 at 20:20
Anyway, let's get to the bottom of your error. First, did you do a count of the source columns versus the destination? — Simon Wilson, Sep 02 '19 at 20:20
Basically, `bulkInsert.ColumnMappings.Add(i, i);` is saying that colA in Sheet1 should map to column0 in the table tbl_data as it is ordinal based. Is that valid in this case? Sheet1 has the same number of columns as tbl_data? (Make sure you check for "blank" columns at the end of your sheet.) — Simon Wilson, Sep 02 '19 at 20:22
@SimonWilson next time i will mention each and every thing properly — Nits Patel, Sep 02 '19 at 20:27
@sheet has no any blank data as i am getting all value properly in data reader (dr) but its not showing in grid dont know why ?? please help me sir — Nits Patel, Sep 02 '19 at 20:29
in data table visualiser i am getting one extra column as "F3" but its not present in my sql table as well as excel sheet — Nits Patel, Sep 02 '19 at 20:42
If they have the same columns please try this `var colName = dt.Columns[i].Name; bulkInsert.ColumnMappings.Add(colName, colName);` Wrap the bulk call in a try/catch(exception e). Place a breakpoint within the catch block. When the exception occurs examine `e`, including InnerException. There must be something identifying the column mapping that is incorrect — Simon Wilson, Sep 02 '19 at 20:43
Another pointer. Why do you load a DataTable then use it for column names, then load a DataReader? The DataReader has a `reader.GetName(i)` method. Please don't load your data twice if you don't need it. — Simon Wilson, Sep 02 '19 at 20:45
Ahhhhh. You say the reader has the data but not the grid...bloody hell...please post the code for `BindGridView();` — Simon Wilson, Sep 02 '19 at 20:47
but its showing i does not exist in current context should i define i as a string or variable ???? — Nits Patel, Sep 02 '19 at 20:48
private void BindGridView() { string conString = ConfigurationManager.ConnectionStrings["con"].ConnectionString; SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand("select * from tbl_data", con); con.Open(); gvUpload.DataSource = cmd.ExecuteReader(); gvUpload.DataBind(); con.Close(); } — Nits Patel, Sep 02 '19 at 20:49
Oh boy. Try this, `Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList()` Then base your for loop on that, you don't need to fill a data table is all. — Simon Wilson, Sep 02 '19 at 20:49
Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList() where should i add i am newbies in c# so unable to find it i am getting error on reder — Nits Patel, Sep 02 '19 at 20:55
This is getting too long, I will code an answer to help, and we can keep updating that until we get you sorted. — Simon Wilson, Sep 02 '19 at 20:56

Simon Wilson · Accepted Answer · 2019-09-02T22:34:08.633

Okay, Nits. This is an example, using your codebase, of something you could be doing. Please try it, after adapting to your specific scenario. You have checked this question out right?

static void Main(string[] args)
{
    string path = string.Concat(Server.MapPath("~/UploadFile/" + FileUpload1.FileName));
    FileUpload1.SaveAs(path);

    string excelCS = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

    using (OleDbConnection con = new OleDbConnection(excelCS))
    {
        DataTable excelData = new DataTable();

        using (OleDbCommand excelSource = new OleDbCommand("select * from [Sheet1$]", con))
        {
            con.Open();
            excelData.Load(excelSource.ExecuteReader());
        }

        // SQL Server Connection String
        string CS = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        SqlBulkCopy bulkInsert = new SqlBulkCopy(CS);
        bulkInsert.DestinationTableName = "tbl_data";   
        bulkInsert.ColumnMappings.Add(0,0);
        bulkInsert.ColumnMappings.Add(1,1);     

        try
        {
            bulkInsert.WriteToServer(excelData);
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }

        con.Close();
        lblerror.Text = "Your file uploaded successfully";
    }
    BindGridView();
}

private static void BindGridView()
{
    string conString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("select ID, Mail_ID from tbl_data", con))
        {
            con.Open();
            var sqlReader = cmd.ExecuteReader();
            Console.WriteLine("START: Sql Columns");
            foreach (var sqlColumnName in Enumerable.Range(0, sqlReader.FieldCount).Select(sqlReader.GetName))
            {
                Console.WriteLine(sqlColumnName);
            }
            Console.WriteLine("END: Sql Columns");
            gvUpload.DataSource = sqlReader;
            gvUpload.DataBind();
        }
    }
}

its showing same error as previous {"The given ColumnMapping does not match up with any column in the source or destination."} — Nits Patel, Sep 02 '19 at 21:23
Where? What line? `gvUpload.DataSource = cmd.ExecuteReader(); gvUpload.DataBind();`? Or `bulkInsert.WriteToServer(excelData);` — Simon Wilson, Sep 02 '19 at 21:35
System.InvalidOperationException: 'The given ColumnMapping does not match up with any column in the source or destination.' — Nits Patel, Sep 02 '19 at 21:41
ID Mail_ID additional 1 585321@HZL01.vedantaresource.local 2 9115@SIILCU.vedantaresource.local 3 00050678@vedantaresource.local my sheet values and header text — Nits Patel, Sep 02 '19 at 21:43
so after examining `e` there are no props on it that indicate which column? — Simon Wilson, Sep 02 '19 at 21:44
Please check my last update. It will print out both the Excel and the sql columns. Please check the Output window after the run and send me the contents. — Simon Wilson, Sep 02 '19 at 21:47
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/198846/discussion-between-simon-wilson-and-nits-patel). — Simon Wilson, Sep 02 '19 at 21:49
siron stack overflow i am new so i unable to search last updated code please guide me where i have to search i know i am irritating you sorry for that — Nits Patel, Sep 02 '19 at 21:50

The given ColumnMapping does not match up with any column in the source or destination for huge data

1 Answers1