C# - Syntax error in INSERT INTO statement (An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll)

Question

    System.Data.OleDb.OleDbCommandBuilder cb;   //command builder
    cb = new System.Data.OleDb.OleDbCommandBuilder(boosterbaseDa); //re-establish temporary connection 

    DataRow dRow = boosterbaseDs.Tables[drpCardSet.Text].NewRow();  //create new row in set

    dRow[0] = drpCardSet.Text + "-EN" + txtCardSetNo.Text; //grab the data
    dRow[1] = txtCardName.Text;
    dRow[2] = drpCardRarity.Text;
    dRow[3] = drpCardCatagory.Text;
    dRow[4] = updCardInStock.Value;

    MessageBox.Show(dRow[0] + ", " + dRow[1] + ", " + dRow[2] + ", " + dRow[3] + ", " + dRow[4]);

    boosterbaseDs.Tables[drpCardSet.Text].Rows.Add(dRow);   //add row

    boosterbaseMaxRows = boosterbaseMaxRows + 1;

    string cardTable = drpCardSet.Text;

    boosterbaseDa.Update(boosterbaseDs, drpCardSet.Text);

I seem to be getting stuck on the last line, with an error of

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll, Syntax error in INSERT INTO statement

I realize there are similar questions but I am seriously stuck with this.

The table does exist and I can make a connection to the database.

Examples of the data going in (into the table "LOB") would be LOB-EN001, Bob, Common, Monster, 1.

Thank you in advance - Sam

What is the select command you initialized the DA with? Are you sure you are assigning the right values for that new row? There is usually a bit more info if you View Detail — Ňɏssa Pøngjǣrdenlarp, Apr 17 '16 at 15:29
This is where it was initliaized: string sql = "SELECT * From " + drpCardSet.Text; boosterbaseDa = new System.Data.OleDb.OleDbDataAdapter(sql, boosterbaseCon); Update requires a valid InsertCommand when passed DataRow collection with new rows. is the further detail, and yes I am sure they must be the right values — user3258453, Apr 17 '16 at 15:31
Since the table is a variable - from a textbox no less - there is every chance you are trying to update a table that doesnt exist or is different than what the DA is built for, — Ňɏssa Pøngjǣrdenlarp, Apr 17 '16 at 15:33
I have tried replacing it with the exact table name, LOB and it comes up with the same error — user3258453, Apr 17 '16 at 15:34

score 0 · Answer 1 · edited May 23 '17 at 12:16

This worked as one solution.

string connetionString = null;
OleDbConnection connection;
OleDbDataAdapter oledbAdapter = new OleDbDataAdapter();
string sql = null;
connetionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=../boosterbase.mdb";
connection = new OleDbConnection(connetionString);
sql = "insert into " + drpCardSet.Text + " values('" + drpCardSet.Text + "-EN" + txtCardSetNo.Text + "','" + txtCardName.Text + "','" + drpCardRarity.Text + "', '" + drpCardCatagory.Text + "', '" + updCardInStock.Text + "')";
try
{
    connection.Open();
    oledbAdapter.InsertCommand = new OleDbCommand(sql, connection);
    oledbAdapter.InsertCommand.ExecuteNonQuery();
    MessageBox.Show("Row(s) Inserted !! ");
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}

From http://csharp.net-informations.com/dataadapter/insertcommand-oledb.htm

Via Update requires a valid InsertCommand when passed DataRow collection with new rows

C# - Syntax error in INSERT INTO statement (An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll)

1 Answers1