Saving to DataBase using OleDB

Question

Im having a lot of trouble understanding OLEDB, im particularly struggling to get information to save to the database this is my current code to attempt to save:

Note: i can load the information absolutely fine, and when i run this function my information appears in my listBox however upon closing the application it hasnt saved.

If you could help explain the problem that would be great

void Insert_New_Log(int startfloor, int endfloor, string currentAction)
    {
        OleDbConnection conn = new OleDbConnection(dbconnection);
        OleDbCommand comm = new OleDbCommand(dbcommand, conn);
        OleDbDataAdapter adapter = new OleDbDataAdapter(comm);
        OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
       // adapter.UpdateCommand = builder.GetUpdateCommand();

        conn.Open();


        adapter.Fill(ds, "ElevatorTable");

        conn.Close();



        DataRow newRow = ds.Tables[0].NewRow();
        newRow["ID"] = 0;
        newRow["Date1"] = dateAndTime;
        newRow["StartingFloor"] = startfloor;
        newRow["EndFloor"] = endfloor;
        newRow["Action"] = currentAction;

        ds.Tables[0].Rows.Add(newRow);

        DataSet dataSetChanges = ds.GetChanges();

        try
        {
            adapter.Update(dataSetChanges, "ElevatorTable");

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

        ds.AcceptChanges();



        //update Visible list

        dbListBox.Items.Clear();
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            dbListBox.Items.Add(row["ID"] + "\t" + row["Date1"] + "\t" + row["StartingFloor"] + "\t" + row["EndFloor"] + "\t" + " (" + row["Action"] + ")");
        }

    }

ive since put a write line in and found this exception. Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll System.Data.OleDb.OleDbException (0x80040E14): Syntax error in INSERT INTO statement. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at Elevator.Form1.Insert_New_Log(Int32 startfloor, Int32 endfloor, String currentAction) in C:\Users\Brads\Desktop\Elevator\Elevator\Form1.cs:line 197

Answer 1

Does the user you have setup in the adapter have write permissions in the SQL database? Also you closed your connection before the update statement that will need to be open in order to write back.

void Insert_New_Log(int startfloor, int endfloor, string currentAction)
{
    OleDbConnection conn = new OleDbConnection(dbconnection);
    OleDbCommand comm = new OleDbCommand(dbcommand, conn);
    OleDbDataAdapter adapter = new OleDbDataAdapter(comm);
    OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
   // adapter.UpdateCommand = builder.GetUpdateCommand();

    conn.Open();


    adapter.Fill(ds, "ElevatorTable");        

    DataRow newRow = ds.Tables[0].NewRow();
    newRow["ID"] = 0;
    newRow["Date1"] = dateAndTime;
    newRow["StartingFloor"] = startfloor;
    newRow["EndFloor"] = endfloor;
    newRow["Action"] = currentAction;

    ds.Tables[0].Rows.Add(newRow);

    DataSet dataSetChanges = ds.GetChanges();
    ds.AcceptChanges();

    try
    {
        adapter.Update(dataSetChanges, "ElevatorTable");

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally { conn.Close(); }

    //update Visible list

    dbListBox.Items.Clear();
    foreach (DataRow row in ds.Tables[0].Rows)
    {
        dbListBox.Items.Add(row["ID"] + "\t" + row["Date1"] + "\t" + row["StartingFloor"] + "\t" + row["EndFloor"] + "\t" + " (" + row["Action"] + ")");
    }

}

Answer 2

you have closed the connection after filling the adapter. You need to either leave the connection open or re-open it.

adapter.Fill(ds, "ElevatorTable"); conn.Close();

Follow this by conn.Open();