Update SQL command C#

Question

I'm trying to update a database from my program in C#.

Here is my code which connects to the database and the attempts to update the date column in my RoomsTable table. It looks good to me but nothing happens in the database.

updateConnection = new System.Data.OleDb.OleDbConnection();
updateConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\spreston\documents\visual studio 2012\Projects\roomChecksProgram\roomChecksProgram\roomsBase.accdb";
updateConnection.Open();

MessageBox.Show("Connected");

string updateCommand = "UPDATE RoomsTable SET Date Checked='9/27/2012'";
updateAdapter = new OleDbDataAdapter(updateCommand, updateConnection);

updateConnection.Close();
updateConnection.Dispose();

I don't know why it isn't working. It looks to me like everything is there.

Answer 1

you have to write this:

updateConnection = new System.Data.OleDb.OleDbConnection();
updateConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\spreston\documents\visual studio 2012\Projects\roomChecksProgram\roomChecksProgram\roomsBase.accdb";

updateConnection.Open();

MessageBox.Show("Connected");

string updateCommand = "UPDATE RoomsTable SET Date Checked= '"+9/27/2012+"' ";

updateAdapter = new OleDbDataAdapter(updateCommand, updateConnection);

updateConnection.Close();
updateConnection.Dispose();

Answer 2

use OleDBCommand

string updateCommand = "UPDATE RoomsTable SET [Date Checked]='9/27/2012'";
updateCommand = new OleDbCommand(updateCommand, updateConnection);

updateCommand.ExecuteNonQuery();
updateConnection.Close();

maybe you could refractor the code using Using statement and parameterized the query. and column name Date Checked should be escaped with brackets.

string updateCommand = "UPDATE RoomsTable SET [Date Checked]=@checkedDate WHERE ID = @id"; // '9/27/2012'
using (OleDbConnection conn = new OleDbConnection("connectionStringHERE"))
{
    using (OleDbCommand comm = new OleDbCommand())
    {
        comm.Connection = conn;
        comm.CommandText = updateCommand;
        comm.CommandType = CommandType.Text
        comm.Parameters.AddWithValue("@checkedDate", this.dateTimePicker1.Value)
        comm.Parameters.AddWithValue("@id", row.roomID);
        try
        {
            comm.Open();
            conn.ExecuteNonQuery();
        }
        catch(OleDbException ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }
    }
}