Problem: Trying to update a single table in my database and when using the dataAdapter Update method it is throwing exception "
Syntax error in INSERT INTO statement."
Here is my call:
Code:
DataTable changes = ds.Tables["orders"].GetChanges();
db.updateOrders(ref changes);
Code:
Code:
public void updateOrders(ref DataTable changes)
{
string selectSQL = "SELECT * FROM orders";
try
{
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = new OleDbCommand(selectSQL, conn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
cb.SetAllValues = false;
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.InsertCommand = cb.GetInsertCommand();
da.Update(changes);
}
catch (Exception ex)
{
System.Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}
}
The following has already been executed:
Code:
private DataTable createOrders()
{
DataTable dt = new DataTable("orders");
DataColumn[] pk = new DataColumn[1];
pk[0] = new DataColumn("idOrder", typeof(int));
dt.Columns.Add(pk[0]);
dt.PrimaryKey = pk;
dt.Columns.Add(new DataColumn("fkVendor", typeof(int)));
dt.Columns.Add(new DataColumn("fkDept", typeof(string)));
dt.Columns.Add(new DataColumn("date", typeof(DateTime)));
dt.Columns.Add(new DataColumn("recDate", typeof(DateTime)));
dt.Columns.Add(new DataColumn("requester", typeof(string)));
dt.Columns.Add(new DataColumn("accountCode", typeof(string)));
dt.Columns.Add(new DataColumn("fkBlanketPO", typeof(string)));
dt.Columns.Add(new DataColumn("notes", typeof(string)));
string sql = "SELECT * FROM orders ORDER BY orders.idOrder DESC";
execute(sql, ref dt, true);
return dt;
}
The table is populated correctly and the new row is marked as "Added" state but I am not sure on why I am receiving SQL errors. I am accessing an Access database and I am not sure how the dataadapter fills in the appropriate parameters. Does it include quotes, how does it handle dates, etc...
The following is the command generated by the OleDbCommandBuilder :
INSERT INTO orders (idOrder, fkVendor, fkDept, date, recDate, requester, accountCode, fkBlanketPO, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hopefully i didnt leave out any required information.
Thanks ahead of time
OleDbDataAdapter Help
