2

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());
                            }
                        }
                    }
7
  • I'm not sure if dateTimePicker1.Value.ToShortDateString() is necessary. Converting DateTime to string is culture specific. Commented Sep 30, 2012 at 17:25
  • 3
    why use conn.Dispose? when you use "Using" objects atomatically Dispose Commented Sep 30, 2012 at 17:25
  • Is it possible you're hitting and exception within but it's not an OldDbException? Do you find anything if you expand the catch to 'Exception'? Commented Sep 30, 2012 at 17:27
  • i just cant think of any way the datagrid displays the newly updated data and not the database if the datagrid gets its data from the database Commented Sep 30, 2012 at 17:30
  • @Kamil ok that fixed one issue i was trying to solve, which is updating the datagrid view after i submit the new dates. But it still doesnt update the actual database Commented Sep 30, 2012 at 17:39

2 Answers 2

2

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.
Sign up to request clarification or add additional context in comments.

8 Comments

I added an additional note to my answer. Yes; in general, don't change to READ UNCOMMITTED. Either commit the transaction in your app, or if you want to inspect a transaction/data without committing during testing, then temporarily change the level. I have had occasion where I wanted to do this while trying to debug a problem, etc. But, not something I would usually go to production with. I agree with your assertion that it is an "inelegant" approach.
ahh i see. i didnt call a commit() method. but what would I call the method on?
@Stonep123 you have to create transaction object. Look here: msdn.microsoft.com/en-us/library/… Let us know if this will work.
posted edit to question with the new transaction code...the executenonquery is throwing an exeption
Put "updateCommand.Transaction = transaction"; after you create object transaction. I mean put it after "transaction = conn.BeginTransaction();", in that "try" block.
|
0

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).

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.