CODEWITHSUNDEEP
c#ms-accessdatagridviewado.net
Stonep123
Stonep123

Reputation: 625

sql update query not updating database

Im writing an application that updates a database that ive created. The application has a datagridview on it which displays the data from the database. Something very weird is going on with my app.

Here is the code that updates the database 

string updateCommandString = "UPDATE RoomsTable SET [Date Checked]=@checkedDate WHERE      ID = @id"; 
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\spreston\documents\visual studio 2012\Projects\roomChecksProgram\roomChecksProgram\roomsBase.accdb"))
{
    using (OleDbCommand updateCommand = new OleDbCommand())
    {
        updateCommand.Connection = conn;
        updateCommand.CommandText = updateCommandString;
        updateCommand.CommandType = CommandType.Text;
        updateCommand.Parameters.AddWithValue("@checkedDate", 
            this.dateTimePicker1.Value.ToShortDateString());
        updateCommand.Parameters.AddWithValue("@id", row.roomID);
        try
        {
            conn.Open();
            updateCommand.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
        }
        catch(OleDbException ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }
    }
}

Now when I run that code, close out off the app, and run the app again, the changed are correctly displayed in my datagridview that is connected to the database, but when i look at the actual database, nothing has changed at all. I dont know why this is happening.

My sql update updates the database, which is connected to the datagrid view. Sow HOW is the datagrid view displaying the correct data but not the database itself.

edit: I have had no sort of experience with sql before.

edit: transaction code:

 string updateCommandString = "UPDATE RoomsTable SET [Date Checked]=@checkedDate WHERE ID = @id"; 
                using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\spreston\documents\visual studio 2012\Projects\roomChecksProgram\roomChecksProgram\roomsBase.accdb"))
                        {
                            OleDbTransaction transaction = null;
                         using (OleDbCommand updateCommand = new OleDbCommand())
                             {

                                    updateCommand.Connection = conn;
                                    updateCommand.Transaction = transaction;    
                                    updateCommand.CommandText = updateCommandString;
                                    updateCommand.CommandType = CommandType.Text;
                                    updateCommand.Parameters.AddWithValue("@checkedDate", this.dateTimePicker1.Value.ToShortDateString());
                                    updateCommand.Parameters.AddWithValue("@id", row.roomID);
                            try
                            {
                                conn.Open();
                                transaction = conn.BeginTransaction();
                                updateCommand.ExecuteNonQuery();
                                transaction.Commit();

                                conn.Close();
                                conn.Dispose();
                            }
                            catch(OleDbException ex)
                            {
                                MessageBox.Show(ex.Message.ToString());
                            }
                        }
                    }

Upvotes: 2

Views: 2723

Answers (2)

Kamil
Kamil

Reputation: 13931

Possible reasons:

  1. Your application may connect to copy of database file. Search your project directory for copies of database (.accdb extension?)
  2. Maybe David S is right, but you have to commit changes on database (OleDbTransaction help at MSDN). Changing isolation settings to READ UNCOMMITTED is inelegant approach to problem.

Upvotes: 2

David S
David S

Reputation: 13851

Sounds like you may have a transaction isolation issue to me. If you are unfamiliar with this topic, suggest you google & read the docs. I believe READ COMMITTED is the default ... Setting it to READ UNCOMMITTED probably fixes issue for you not being able to see the updates. But, this is not really a recommended approach. In general, you will just want to make sure that all of your transactions are committed to the database before trying to inspect the information.

Additional note: I've had need from time to time during testing an application to want to be able to query the db from a SQL tool outside of my application to check on something. However, in almost 15 years of developing db applications, I've never had need to set this to READ UNCOMMITTED for production. I would be very cautious about going to production on anything other than READ COMMITTED. I'm not saying there is never a case where you would need to want that, but it's not the norm (IMHO).

Upvotes: 0

Related Questions