Excel to SQL Code: Cannot find Column 8 Error

Question

I have been over my code numerous times and hope someone can help me see something I don't. I'm trying to pull data from Excel into a SQL table with multiple columns in VS2010 but when I try uploading the data, I get the error "Cannot Find Column 8." I see nothing wrong with column 8 nor any of the other columns. Anyone else? Thanks!

protected void Button1_Click(object sender, EventArgs e)
{
    //make local copy
    string sheetname = "Sheet1";
    string path = Server.MapPath("~/Import.xls");
    //connect to local Excel
    try
    {

        FileUpload.SaveAs(path);
        System.Data.OleDb.OleDbConnection MyConnection;
        System.Data.DataSet DtSet;
        System.Data.OleDb.OleDbDataAdapter MyCommand;  //check sheet name
        MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
        MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "$]", MyConnection);
        MyCommand.TableMappings.Add("Table", "TestTable");
        DtSet = new System.Data.DataSet();
        MyCommand.Fill(DtSet);
        SqlConnection curConnection = new SqlConnection(@"Data Source=1tc-Techcomm2;Initial Catalog=EventManagement;Integrated Security=True");
        curConnection.Open();
        SqlCommand curCommand;
        SqlParameter param;
        string str;

        for (int i = 0; i < DtSet.Tables[0].Rows.Count; i++)
        {
            if (DtSet.Tables[0].Rows[i][1] == DBNull.Value)
                continue;
            curCommand = curConnection.CreateCommand();
            curCommand.CommandText = @"Insert into TestSP (SessionHead_Title, SessionHead_Type, SessionHead_Description, SessionHead_Confirmed, SessionHead_Presenter, SessionHead_Champion, SessionHead_Objective, SessionHead_Notes, SessionHead_Schedule, SessionHead_Equipment, SessionHead_Hardware, SessionHead_CDA) Values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l,@m)";
            for (int j = 0; j < 13; j++)
            {
                param = new SqlParameter();
                str = "@";
                str += (char)('a' + j);
                param.ParameterName = str;
                param.SqlDbType = SqlDbType.VarChar;
                param.Value = DtSet.Tables[0].Rows[i][j];//This is where it errors out at after 8 times through
                curCommand.Parameters.Add(param);

            }
            Label1.Text = "THE EXCEL DATE HAS SUCCESSFULLY BEEN SENT TO THE DATABASE"; 

            int Event_Id = curCommand.ExecuteNonQuery();

        }
        MyConnection.Close();
        curConnection.Close();
    }
    catch (Exception ex)
    {
        //Response.Write(ex.ToString());
        Label1.Text = ex.Message; 

    }
}

Is the data there in column 8 in the dataset? Have you checked by stepping through the debugger? — Nick Rolando
– Nick Rolando, Commented Apr 5, 2012 at 22:44
Yes, in one of the column 8 cells there is data and I have it set to accept nvarchar(MAX) in SQL. The debugger catches on the 8 time through the for (int j = 0, j<13; j++) section at param.Value = DtSet.Tables[0].Rows[i][j]; — Sarah
– Sarah, Commented Apr 9, 2012 at 15:38

Sarah · Accepted Answer · 2012-04-11 18:48:58Z

1

I believe the issue was that the spreadsheet column headers did not match the table headers. I read somewhere that the Column names in SQL Server table must be the same in the Excel file. I guess this solves it...thanks for the help!

answered Apr 11, 2012 at 18:48

Sarah

111 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jmyster · Accepted Answer · 2012-04-05 22:46:53Z

0

If you know the structure of your Table on the SQL Server, Try using the SqlBulkCopy. Map your columns and write it to the server. You already have the data in your DataSet.

SqlBulkCopy _bc = new SqlBulkCopy(curConnection);

_bc.DestinationTableName = "TestSP";

_bc.ColumnMappings.Add(new SqlBulkCopyColumnMapping("DataTableColumnName1", "SQLServerColumnName1");
_bc.ColumnMappings.Add(new SqlBulkCopyColumnMapping("DataTableColumnName2", "SQLServerColumnName2");
_bc.ColumnMappings.Add(new SqlBulkCopyColumnMapping("DataTableColumnName3", "SQLServerColumnName3");
_bc.ColumnMappings.Add(new SqlBulkCopyColumnMapping("DataTableColumnName4", "SQLServerColumnName4");

_bc.WriteToServer(DtSet.Tables[0]);

You can still use this process if you don't know the structure but you will have to get creative. I had to do that and it was pretty fun.

answered Apr 5, 2012 at 22:46

Jmyster

1,0032 gold badges10 silver badges28 bronze badges

2 Comments

Jmyster Over a year ago

On the SqlBulkCopyColumnMapping You can also use column indexs instead of the column names

Sarah Over a year ago

Thank you but I don't think I can bulk pull the data because I'll eventually need to be able to pull data from other SQL tables based on the data pulled as well as pull data into another system based on the information. Also, this one table will eventually be the holding spot for many different spreadsheets with the same headers.

Marc · Accepted Answer · 2020-06-25 18:37:55Z

0

The solution of user3611272 helped me:

... i tried to append some random table and went to "Advanced Options" at the bottom of the append pop up in this u will see some "Stored Column mappings" and some mappings in it, now delete ~~all of them~~ the relevant mapping and hit "Accept" now u can append the table

answered Jun 25, 2020 at 18:37

Marc

232 silver badges7 bronze badges

Collectives™ on Stack Overflow

Excel to SQL Code: Cannot find Column 8 Error

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related