Excel error The INSERT INTO statement contains the following unknown field name: '23#143214'

Hi,

I have an excel file that I can succesfully write to with the ado jet driver in c#. The problem seems to occur when I write doubles (or mixing and matching possibly):

	insertAt(2, 2, "test",myConnection);

        insertAt(3, 2, "test2", myConnection);
        insertAt(4, 2, "23", myConnection);
        insertAt(5, 2, "23.143214", myConnection);
        insertAt(6, 2, "276.0133", myConnection);

This results in following SQL command (on the line it crashes):

"INSERT INTO [pFACTData$B6:B6] Values ('276.0133')"

The error message is talking about hashes though for some reason

The INSERT INTO statement contains the following unknown field name: '23#143214'. Make sure you have typed the name correctly, and try the operation again.

It's like the command is corrupted somehow after it is sent. Anyone know why this doesn't work?

   string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + str_excelFileOnLocalMachineInReports + ";";
        connectionString += "Extended Properties=Excel 8.0;";

        OleDbConnection myConnection = new OleDbConnection(connectionString);

        OleDbCommand myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_grade] Values ('23')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_point] Values ('23')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_category] Values ('276')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_basicpay] Values ('256')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_ni] Values ('2236')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

So the above works but if you make any of them into a fraction e.g.

        string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + str_excelFileOnLocalMachineInReports + ";";
        connectionString += "Extended Properties=Excel 8.0;";

        OleDbConnection myConnection = new OleDbConnection(connectionString);

        OleDbCommand myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_grade] Values ('23.123')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_point] Values ('23')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_category] Values ('276')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_basicpay] Values ('256')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_ni] Values ('2236')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

Then it crashes with the unknown field name error

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

worldisflat16y ago
   string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + str_excelFileOnLocalMachineInReports + ";";
        connectionString += "Extended Properties=Excel 8.0;";

        OleDbConnection myConnection = new OleDbConnection(connectionString);

        OleDbCommand myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_grade] Values ('23')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_point] Values ('23')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_category] Values ('276')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_basicpay] Values ('256')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_ni] Values ('2236')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

So the above works but if you make any of them into a fraction e.g.

        string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + str_excelFileOnLocalMachineInReports + ";";
        connectionString += "Extended Properties=Excel 8.0;";

        OleDbConnection myConnection = new OleDbConnection(connectionString);

        OleDbCommand myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_grade] Values ('23.123')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_point] Values ('23')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_category] Values ('276')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_basicpay] Values ('256')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

        myInsertCommand = new OleDbCommand("INSERT INTO [dastaff1_ni] Values ('2236')");
        myInsertCommand.Connection = myConnection;
        myInsertCommand.ExecuteNonQuery();

Then it crashes with the unknown field name error
0
Suthish Nair16y ago
can you post the sample code, so we can have a look.
0