Need to when ExecuteNonQuery has finished before proceeding to next line of code

Question

I searched and checked but did not see what I need. I am using a data adapter's UpdateCommand's ExecuteNonQuery to update a local Access database. When I run the code, the ExecuteNonQuery does not appear to finish before the next line of code is executed and therefore, the updated database is not reflected in my grid or my text boxes. If I put a messagbox right after the ExecuteNonQuery, then when I hit ok in the messagebox, the rest of the code is executed and the updated database is reflected in my grid and text boxes. Or if I step through the code in debug without the messagebox to hault execution, the update to the database is reflected on my form. But if I just run the code without something after the query to slow it down or stop it, the update is not shown in my controls.

OleDbConnection connection = new OleDbConnection(connString);
string sql = "Update Table1 set Country = '" + txtNewText.Text + "' where SomeNum =   
1111";           

connection.Open();
dAdapter.UpdateCommand = connection.CreateCommand();
dAdapter.UpdateCommand.CommandText = sql;             
dAdapter.UpdateCommand.ExecuteNonQuery(); 

//MessageBox.Show("Pause");

dTable.Clear(); //Clear or it will add everything to what was there.
dAdapter.Fill(dTable);

row = dTable.Rows[0];
txtCompany.Text = row["Name"].ToString();
txtGenre.Text = row["Job"].ToString();
txtId.Text = row["Id"].ToString();
txtSomeNum.Text = row["SomeNum"].ToString();
txtCountry.Text = row["Country"].ToString();

I'm assuming that the update query is not finishing before I re-fill the data table. Can someone please help me with this. I looked for something to show the status of the update query but did not see what I need. What am I missing here???

Thank you!

VH

10/28/14 Last night I added dAdapter.UpdateCommand.ExecuteNonQuery() right after dAdapter.UpdateCommand.ExecuteNonQuery(); then everything worked. I was not sure if this was just something that took enough time to allow the query to finish and therefore causing the update to display, or if it was just some fluke "band-aide" that worked. I then replaced that with connection.Close(); and that worked as well. Again...I don't know if closing the connection caused the query to finish and the connection to close before moving to the next line of code, or if this was also just "fudging" it to work. I have seen other people having similar problems in my searching for answers but I have not seen any one explain what the problem is and why this happens or the most appropriate solution.

Check this out : http://stackoverflow.com/questions/6374911/multiple-concurrent-calls-to-sqlcommand-beginexecutenonquery-using-same-sqlconne — Avia, Oct 27 '14 at 22:22

score 0 · Answer 1 · answered Oct 27 '14 at 22:22

0

You should use the OleDbDataReader, since you aren't using SQL.

Provides a way of reading a forward-only stream of data rows from a data source.

An example:

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    OleDbCommand command = new OleDbCommand(queryString, connection);

    connection.Open();
    OleDbDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine(reader[0].ToString());
    }
    reader.Close();
}

This will iterate through each specified column allowing you to fill your data. Documentation can be found here.

answered Oct 27 '14 at 22:22

Greg

11,302
2
48
79

Thanks Greg. I'm trying to explore/learn all the different ways of manipulating databases. I will try what you suggested but I still would like to know how to fix what I have to learn how to use it and the pros and cons of this method... It updates the DB but it seems to be a timing issue. It's irritating that it works when you step through the code or hault code using a message box. It seems there has to be a way to use this and and I must be missing something... – valhalla Oct 27 '14 at 22:50
I just added the following – valhalla Oct 27 '14 at 23:11
Sorry... I just added the following: – valhalla Oct 27 '14 at 23:11

Need to when ExecuteNonQuery has finished before proceeding to next line of code

1 Answers1