OleDbException (x80040E14): Syntax error in Insert into statement

Question

Can anyone help with this issue please? I've tried so many different things and I'm still getting this error. The error points to the line cmd.ExecuteNonQuery(). I've checked the Insert string a million times now and I don't see what's wrong with. Thanks!

OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + targetPath);                        
try
{
    for (int i = 0; i < listOfDBTables.Count; i++)
    {
        for (int j = 1; j < listOfDBTables[i].Rows.Count; j++)
        //Don't include the header
        {
            OleDbCommand cmd = new OleDbCommand("INSERT INTO POP(Global Customer, Sold To #, Material #, Material Description, Customer ID, Customer Name, City, State, Country, Market Code, Unit Cost, Invoice Date, [Month], Quantity, UoM, KG, [Year]) Values (@globCust,@soldToNum,@matNum,@matDesc,@custID,@custName,@city,@state,@country,@mktCode,@cost,@invDate,@month,@quantity,@uom,@kg,@year)", myConnection);

            cmd.Parameters.AddWithValue("@globCust", listOfDBTables[i].Rows[j][0].ToString());
            cmd.Parameters.AddWithValue("@soldToNum", listOfDBTables[i].Rows[j][1].ToString());
            cmd.Parameters.AddWithValue("@matNum", listOfDBTables[i].Rows[j][2].ToString());
            cmd.Parameters.AddWithValue("@matDesc", listOfDBTables[i].Rows[j][3].ToString());
            cmd.Parameters.AddWithValue("@custID", listOfDBTables[i].Rows[j][4].ToString());
            cmd.Parameters.AddWithValue("@custName", listOfDBTables[i].Rows[j][5].ToString());
            cmd.Parameters.AddWithValue("@city", listOfDBTables[i].Rows[j][6].ToString());
            cmd.Parameters.AddWithValue("@state", listOfDBTables[i].Rows[j][7].ToString());
            cmd.Parameters.AddWithValue("@country", listOfDBTables[i].Rows[j][8].ToString());
            cmd.Parameters.AddWithValue("@mktCode", listOfDBTables[i].Rows[j][9].ToString());
            cmd.Parameters.AddWithValue("@cost", listOfDBTables[i].Rows[j][10].ToString());
            cmd.Parameters.AddWithValue("@invDate", listOfDBTables[i].Rows[j][11].ToString());
            cmd.Parameters.AddWithValue("@month", listOfDBTables[i].Rows[j][12].ToString());
            cmd.Parameters.AddWithValue("@quantity", listOfDBTables[i].Rows[j][13].ToString());
            cmd.Parameters.AddWithValue("@uom", listOfDBTables[i].Rows[j][14].ToString());
            cmd.Parameters.AddWithValue("@kg", listOfDBTables[i].Rows[j][15].ToString());
            cmd.Parameters.AddWithValue("@year", listOfDBTables[i].Rows[j][16].ToString());

            myConnection.Open();
            cmd.ExecuteNonQuery();
        }
    }
}
catch(Exception ex)
{
    MessageBox.Show(ex.ToString());
}
finally
{
    myConnection.Close();
}

Your provider may only handle unnamed parameters (using ?) — SLaks
– SLaks, Commented Jul 14, 2016 at 14:11
I'm still getting some errors, but I seem to be making progress. Wrapping all of the column names in brackets may have done the trick. I will post again if I solve this. Thanks @MarcusH — testing123
– testing123, Commented Jul 14, 2016 at 14:20
Solved. I had to wrap each column name in brackets as well as the table name. Thanks guys! — testing123
– testing123, Commented Jul 14, 2016 at 15:16

Seun Kehinde · Accepted Answer · 2016-07-15 02:48:01Z

0

you should put your columns in [], e.g make it [Sold To #] instead of Sold To #

answered Jul 15, 2016 at 2:48

Seun Kehinde

11 bronze badge

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

Collectives™ on Stack Overflow

OleDbException (x80040E14): Syntax error in Insert into statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related