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]]

Re: Need to wait for TableAdapter.Up date to finish

Boris,

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

Standard is this just a synchrone process.

Cor

"Boris227" <adrian_morris@ btinternet.coms chreef in bericht
news:1bf92f36-6706-4696-9821-efbce22420f3@59 g2000hsb.google groups.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]

Re: Need to wait for TableAdapter.Up date to finish

On 7 Apr, 12:10, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

Boris,
>
How are you updating the database, while using your save button.
>
Standard is this just a synchrone process.
>
Cor
>
"Boris227" <adrian_mor...@ btinternet.coms chreef in berichtnews:1bf 92f36-6706-4696-9821-efbce22420f3@59 g2000hsb.google groups.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.E ndEdit()
Try
Me.DiagAddTable Adapter.Update( Me.Visir_RTDS_v 3DataSet3)
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]

Re: Need to wait for TableAdapter.Up date to finish

On 7 Apr, 12:51, Boris227 <adrian_mor...@ btinternet.comw rote:

On 7 Apr, 12:10, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:
>
>
>
>
>

Boris,

>

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

>

Standard is this just a synchrone process.

>

Cor

>

"Boris227" <adrian_mor...@ btinternet.coms chreef in berichtnews:1bf 92f36-6706-4696-9821-efbce22420f3@59 g2000hsb.google groups.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.E ndEdit()
Try
        Me.DiagAddTable Adapter.Update( Me.Visir_RTDS_v 3DataSet3)
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]]

Re: Need to wait for TableAdapter.Up date to finish

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]

Re: Need to wait for TableAdapter.Up date to finish

On 7 Apr, 17:25, "Cor Ligthert[MVP]" <notmyfirstn... @planet.nlwrote :

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.up date 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]

Re: Need to wait for TableAdapter.Up date to finish

Boris227 wrote:

>Try
>Me.DiagAddTabl eAdapter.Update (Me.Visir_RTDS_ v3DataSet3)
>Catch
>MessageBox.Sho w("An error occurred during the update
>process")
>Finally
>strSQL = "INSERT INTO tbl1 (a, b, c) SELECT etc ....... ;"
>db.DoCmd.RunSQ L(strSQL)
>MessageBox.Sho w("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.DiagAddTable Adapter.Update( Me.Visir_RTDS_v 3DataSet3) 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]]

Re: Need to wait for TableAdapter.Up date to finish

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" <adrian_morris@ btinternet.coms chreef in bericht
news:523b2eb9-4c6a-4c8a-83a5-07238acb46f2@s3 9g2000prd.googl egroups.com...
On 7 Apr, 17:25, "Cor Ligthert[MVP]" <notmyfirstn... @planet.nlwrote :

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.up date 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]

Re: Need to wait for TableAdapter.Up date to finish

On 8 Apr, 03:31, "Steve Gerrard" <mynameh...@com cast.netwrote:

Boris227 wrote:

Try
Me.DiagAddTable Adapter.Update( Me.Visir_RTDS_v 3DataSet3)
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.DiagAddTable Adapter.Update( Me.Visir_RTDS_v 3DataSet3) 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.