Need to wait for TableAdapter.Update to finish

  • Thread starter Thread starter Boris227
  • Start date Start date
B

Boris227

I have written an application that has a DataGridView for users to
amend a table. When finished they click a 'Save' button, which
generates a series of queries based on the new data. The problem is
this:

The underlying table is always updated correctly and when I step
through the code with the debugger it works perfectly, but when run it
live the events don't take account of the changes. This makes me
believe that the queries are not waiting for the update to complete.
Does this make sense? Can I do anything to make them wait?

Thanks
 
Boris,

How are you updating the database, while using your save button.

Standard is this just a synchrone process.

Cor
 
Boris,

How are you updating the database, while using your save button.

Standard is this just a synchrone process.

Cor

"Boris227" <[email protected]> schreef in bericht





- Show quoted text -
Click to expand...

Me.Validate()
Me.dgvDiagadd.EndEdit()
Try
Me.DiagAddTableAdapter.Update(Me.Visir_RTDS_v3DataSet3)
Catch
MessageBox.Show("An error occurred during the update
process")
Finally
strSQL = "INSERT INTO tbl1 (a, b, c) SELECT etc ....... ;"
db.DoCmd.RunSQL(strSQL)
MessageBox.Show("Save completed")
End Try
 
Me.Validate()
Me.dgvDiagadd.EndEdit()
Try
        Me.DiagAddTableAdapter.Update(Me.Visir_RTDS_v3DataSet3)
Catch
         MessageBox.Show("An error occurred during the update
process")
Finally
        strSQL = "INSERT INTO tbl1 (a, b, c) SELECT etc ....... ;"
        db.DoCmd.RunSQL(strSQL)
        MessageBox.Show("Save completed")
End Try- Hide quoted text -

- Show quoted text -
Click to expand...

My current work around is to provide a second button for all the SQL
statements after the update. Yes, it works (although presumably it
wouldn't if the user got to the second button before the update
completed) but there must be a better way.
 
Boris,

What is the purpose of this code below?
I have never seen it.

Finally
strSQL = "INSERT INTO tbl1 (a, b, c) SELECT etc ....... ;"
db.DoCmd.RunSQL(strSQL)
MessageBox.Show("Save completed")
End Try


Cor
 
Boris,

What is the purpose of this code below?
I have never seen it.

Finally
        strSQL = "INSERT INTO tbl1 (a, b, c) SELECT etc ....... ;"
        db.DoCmd.RunSQL(strSQL)
        MessageBox.Show("Save completed")
End Try

Cor
Click to expand...

Sorry, I was trying to simplify the code and I've made it harder to
understand.

What that block represents is a series of updates to other tables in
the same database based on the new data that has just been put into or
amended through the table adapter.

The reason I did this was because the table adapter update works and
all the rest of the code works but only if I stop the process after
the tableadapter.update line and then run the remaining code
seperately. If they follow up in sequence the queries do NOT include
the updated data, although when you look at the database after the
procedure has run this core table in the database (the one updated by
the tableadapter) is correct
 
Boris,

So is that is processed, then the Update is as well processed, or even more,
if the update is not processed, then the finally will be processed.

In my idea not clever to set this code in a finally block. A finally block
is forever processed as long as you don't power down the computer.

In your place I would start to seperate the processes, while my first
opinion is that your problem is in your second situation, not in the
standard update.

Cor

"Boris227" <[email protected]> schreef in bericht
Boris,

What is the purpose of this code below?
I have never seen it.

Finally
strSQL = "INSERT INTO tbl1 (a, b, c) SELECT etc ....... ;"
db.DoCmd.RunSQL(strSQL)
MessageBox.Show("Save completed")
End Try

Cor
Click to expand...

Sorry, I was trying to simplify the code and I've made it harder to
understand.

What that block represents is a series of updates to other tables in
the same database based on the new data that has just been put into or
amended through the table adapter.

The reason I did this was because the table adapter update works and
all the rest of the code works but only if I stop the process after
the tableadapter.update line and then run the remaining code
seperately. If they follow up in sequence the queries do NOT include
the updated data, although when you look at the database after the
procedure has run this core table in the database (the one updated by
the tableadapter) is correct
 
Boris,

So is that is processed, then the Update is as well processed, or even more,
if the update is not processed, then the finally will be processed.

In my idea not clever to set this code in a finally block. A finally block
is forever processed as long as you don't power down the computer.

In your place I would start to seperate the processes, while my first
opinion is that your problem is in your second situation, not in the
standard update.

Cor

"Boris227" <[email protected]> schreef in bericht




Sorry, I was trying to simplify the code and I've made it harder to
understand.

What that block represents is a series of updates to other tables in
the same database based on the new data that has just been put into or
amended through the table adapter.

The reason I did this was because the table adapter update works and
all the rest of the code works but only if I stop the process after
the tableadapter.update line and then run the remaining code
seperately. If they follow up in sequence the queries do NOT include
the updated data, although when you look at the database after the
procedure has run this core table in the database (the one updated by
the tableadapter) is correct
Click to expand...

Thanks for the guidance about the finally block. It was a vain attempt
to split the processes up. I accept your opinion that it isn't right
and doesn't help.

I have just tried it outside the finally block, both inside and
outside the Try block and it doesn't work in either. Same result
again. The series of queries run using the pre-update state of the
data. Post-run analysis still shows that the table has been updated.
For test purposes I am only updating a single record, but the update
is still too slow for the queries that follow.
 
I read your reply to Cor, but I answered here because you have the code here.

I think you are encountering a problem of using two different connections.The
line db.DoCmd.RunSQL(strSQL) is probably not using the same connection to the
database that Me.DiagAddTableAdapter.Update(Me.Visir_RTDS_v3DataSet3) is using.

The database won't fully commit the changes instantaneously, so the second
connection may or may not seem them, depending on how soon after the update it
runs. Sound familiar?

You might be able to fix it by using a transaction around the first update, and
committing it, but the better solution is to use the same connection for both
updates. That might take a little rearranging, but if you do a sequence like
        Open connection
        Run first update
        Run second update
        Close connection
you should get the data consistency you are looking for.- Hide quoted text-

- Show quoted text -
Click to expand...

Yes, they are definitely using different connections because I just
created a new one through the DataGridView wizard. I never thought of
that. It sounds promising. I will give this a go.
 
Back
Top