Need to wait for TableAdapter.Update to finish

[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

[Cor Ligthert [MVP]]

Boris,

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

Standard is this just a synchrone process.

Cor

"Boris227" <ad***********@btinternet.comschreef in bericht
news:1b**********************************@59g2000h sb.googlegroups.com...

>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

[Boris227]

On 7 Apr, 12:10, "Cor Ligthert [MVP]" <[e-mail]>
wrote:

Boris,

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

Standard is this just a synchrone process.

Cor

"Boris227" <[e-mail] in berichtnews:1b**********************************@5 9g2000hsb.googlegroups.com...

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- Hide quoted text -

- Show quoted text -

Me.Validate()
Me.dgvDiagadd.EndEdit()
Try
Me.DiagAddTableAdapter.Update(Me.Visir_RTDS_v3Data Set3)
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

[Boris227]

On 7 Apr, 12:51, Boris227 <[e-mail]:

On 7 Apr, 12:10, "Cor Ligthert [MVP]" <[e-mail]>
wrote:

Boris,

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

Standard is this just a synchrone process.

Cor

"Boris227" <[e-mail] in berichtnews:1b**********************************@5 9g2000hsb.googlegroups.com...

>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- Hide quoted text -

- Show quoted text -

Me.Validate()
Me.dgvDiagadd.EndEdit()
Try
* * * * Me.DiagAddTableAdapter.Update(Me.Visir_RTDS_v3Data Set3)
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 -

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.

[Cor Ligthert[MVP]]

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

[Boris227]

On 7 Apr, 17:25, "Cor Ligthert[MVP]" <[e-mail]:

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

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

[Steve Gerrard]

Boris227 wrote:

>Try
Me.DiagAddTableAdapter.Update(Me.Visir_RTDS_v3Dat aSet3)
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 -

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.

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

[Cor Ligthert[MVP]]

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" <ad***********@btinternet.comschreef in bericht
news:52**********************************@s39g2000 prd.googlegroups.com...
On 7 Apr, 17:25, "Cor Ligthert[MVP]" <[e-mail]:

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

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

[Boris227]

On 8 Apr, 03:31, "Steve Gerrard" <[e-mail]:

Boris227 wrote:

Try
Me.DiagAddTableAdapter.Update(Me.Visir_RTDS_v3Data Set3)
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 -

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.

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_v3Data Set3) 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 -

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.