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, Jul 14 '16 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, Jul 14 '16 at 14:20
Solved. I had to wrap each column name in brackets as well as the table name. Thanks guys! — testing123, Jul 14 '16 at 15:16

score 0 · Answer 1 · answered Jul 15 '16 at 02:48

0

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

answered Jul 15 '16 at 02:48

Seun Kehinde

1
1

OleDbException (x80040E14): Syntax error in Insert into statement

1 Answers1