ado question

[silvia]

Hi everyone,

How can I tell when it is safe to query a table that I have just inserted
records into. I keep running into timing problems and having to make my
program loop until it gets the right answer. Consider the following situation:


'This routine clears a temp table, then inserts new records based
'on the records in rst.
Call ProcessSystems(rst)

'The temp table now SHOULD contain 316 records - 4 each for 79 systems.

'Now open a sum query on the temp table.
Set rst = New ADODB.Recordset
rst.Open sSumQry, mcnn, adOpenStatic, adLockReadOnly

'--------------------------------
'Here is where the problem is. Without this code I get the wrong sums!!

'This will typically requery 5-6 times before the recordcount is correct.
Do While rst.RecordCount <> 79
rst.Requery
DoEvents
Loop
'--------------------------------


The problem is not specific to any particular application. As I said I am
constantly running into this problem any time I need to do this. I have
tried looping the .State property of the recordset is equal to adStateFetching
but this doesn't requery eveon once. I have also tried adStateExecuting
but this did not work either.

Am I missing something?

Any ideas are appreciated.
Thx,
Silvia

[Rick]

Silvia -

As a suggestion, make ProcessSystems a function and return a boolean true
on successful completion. Then test the boolean before doing the query.

eg -- If ProcessSystems = True then
'Do query
Else
'Die
Endif

Hope it helps

Rick




"silvia" <silram128@ureach.com> wrote:
>
>Hi everyone,
>
>How can I tell when it is safe to query a table that I have just inserted
>records into. I keep running into timing problems and having to make my
>program loop until it gets the right answer. Consider the following situation:
>
>
> 'This routine clears a temp table, then inserts new records based
> 'on the records in rst.
> Call ProcessSystems(rst)
>
> 'The temp table now SHOULD contain 316 records - 4 each for 79 systems.
>
> 'Now open a sum query on the temp table.
> Set rst = New ADODB.Recordset
> rst.Open sSumQry, mcnn, adOpenStatic, adLockReadOnly
>
> '--------------------------------
> 'Here is where the problem is. Without this code I get the wrong sums!!
>
> 'This will typically requery 5-6 times before the recordcount is correct.
> Do While rst.RecordCount <> 79
> rst.Requery
> DoEvents
> Loop
> '--------------------------------
>
>
>The problem is not specific to any particular application. As I said I
am
>constantly running into this problem any time I need to do this. I have
>tried looping the .State property of the recordset is equal to adStateFetching
>but this doesn't requery eveon once. I have also tried adStateExecuting
>but this did not work either.
>
>Am I missing something?
>
>Any ideas are appreciated.
>Thx,
>Silvia
>

[Ian Hossie]

Also, part of your problem might be that .recordcount doesn't work properly
with all drivers and with either client or server side cursors, depending
on your configuration. Which means that you're not always getting an accurate
recordcount.

I'd try, after doing an update,

rs.movelast
rs.movefirst

to get an accurate recordcount.

[silvia]

"Rick" <rsteinberger@icmarc.org> wrote:
>
>
>Silvia -
>
>As a suggestion, make ProcessSystems a function and return a boolean true
>on successful completion. Then test the boolean before doing the query.
>
>eg -- If ProcessSystems = True then
> 'Do query
> Else
> 'Die
> Endif
>
>Hope it helps
>
>Rick
>
>
>
>

Thanks Rick,
Sorry, I think I explained this incorrectly. ProcessSystems() inserts records
by using the .Execute method of the connection object. The problem does
not seem to be related to whether the inserts are successful. What I mean
is that after all the inserts succeed and ProcessSystems() finishes successfully,
the code just seems to be moving on too quickly, so the subsequent query
on that data is happening before the last insert(s) are finished executing.
It will work fine if I put a 1 second sleep just after ProcessSystems(),
but that is a hack in which I have little faith, since my users machines
may need 2 seconds or some other duration in order to work properly. (Their
workstations are quite a bit slower than my development machine). I don't
like guessing because I will invariably get it wrong <g>. I am trying to
avoid the classic "It works fine on my machine but fails on theirs" situation.
It seems like it should be possible to test cnn.State to make sure the last
insert is finished before moving on, but as I mentioned this doesn't work.

BTW, I am using VB6 SP5, Access2000, Jet 4.0. Also using ClientSide Cursor.

Thx again,
Silvia

[silvia]

"Ian Hossie" <ian.hossie@usa.xerox.com> wrote:
>
>Also, part of your problem might be that .recordcount doesn't work properly
>with all drivers and with either client or server side cursors, depending
>on your configuration. Which means that you're not always getting an accurate
>recordcount.
>
>I'd try, after doing an update,
>
>rs.movelast
>rs.movefirst
>
>to get an accurate recordcount.
>
>

Thanks Ian,
I tried .MoveLast, .MoveFirst but this had no effect. I guess what it does
is just move to whatever it THINKS is the last record at the time, then goes
back to the first record. I remember having to use this technique with DAO,
but it does not seem to work the same with ADO.

The problem does indeed seem to be that recordcount is reporting the wrong
number of records, since it is reporting less than 79 when in fact, there
are (or will be) 79 records in the recordset. They are just not quite all
visible yet. They all become visible if I make the code wait a second or
two which is what this does:

'This will typically requery 5-6 times before the recordcount is correct.
Do While rst.RecordCount <> 79
rst.Requery
DoEvents
Loop

But this just doesn't seem to be the right way to approach the problem. There
has got to be a more elegant solution.

Thx,
Silvia

[Rick]

silvia -

I think you missed the point of the code sample. By making continuation dependent
on the out come of the ProcessSystems function, the timing issue should disapear.

Give it a try.

Rick



"silvia" <silram128@ureach.com> wrote:
>
>"Rick" <rsteinberger@icmarc.org> wrote:
>>
>>
>>Silvia -
>>
>>As a suggestion, make ProcessSystems a function and return a boolean true
>>on successful completion. Then test the boolean before doing the query.
>>
>>eg -- If ProcessSystems = True then
>> 'Do query
>> Else
>> 'Die
>> Endif
>>
>>Hope it helps
>>
>>Rick
>>
>>
>>
>>
>
>Thanks Rick,
>Sorry, I think I explained this incorrectly. ProcessSystems() inserts records
>by using the .Execute method of the connection object. The problem does
>not seem to be related to whether the inserts are successful. What I mean
>is that after all the inserts succeed and ProcessSystems() finishes successfully,
>the code just seems to be moving on too quickly, so the subsequent query
>on that data is happening before the last insert(s) are finished executing.
> It will work fine if I put a 1 second sleep just after ProcessSystems(),
>but that is a hack in which I have little faith, since my users machines
>may need 2 seconds or some other duration in order to work properly. (Their
>workstations are quite a bit slower than my development machine). I don't
>like guessing because I will invariably get it wrong <g>. I am trying to
>avoid the classic "It works fine on my machine but fails on theirs" situation.
> It seems like it should be possible to test cnn.State to make sure the
last
>insert is finished before moving on, but as I mentioned this doesn't work.
>
>BTW, I am using VB6 SP5, Access2000, Jet 4.0. Also using ClientSide Cursor.
>
>Thx again,
>Silvia
>

[silvia]

"Rick" <rsteinberger@icmarc.org> wrote:
>
>
>silvia -
>
>I think you missed the point of the code sample. By making continuation
dependent
>on the out come of the ProcessSystems function, the timing issue should
disapear.
>
>Give it a try.
>
>Rick
>
>
>

Rick, I appreciate you bearing with me on this. I'm not sure what you mean
by making continuation dependent on the out come of ProcessSystems. What
is the criteria for success? Basically, in this situation, success means
(to me) that I have the correct number of records in the recordset that is
to be opened immediately after ProcessSystems finishes inserting records
into the temp table. I guess I could put the validation code into ProcessSystems
as follows -

Private Function ProcessSystems(rstIn As ADODB.Recordset) As Boolean
Dim rst As ADODB.Recordset
Dim bRetval As Boolean

bRetval = False

'Clear the temp table
mcnn.Execute "delete * from temp;"

rstIn.MoveFirst
Do While Not rstIn.EOF
'.............
'Processing code...
'.............

'Inserting new records
sSQL = "insert into temp... "
mcnn.Execute

rstIn.MoveNext
Loop

'The temp table now SHOULD contain 316 records - 4 each for 79 systems.

'Now open a sum query on the temp table.
Set rst = New ADODB.Recordset
rst.Open sSumQry, mcnn, adOpenStatic, adLockReadOnly


'If I simply do this, bRetval will always be false
'because it is happening to soon.
bRetval = (rst.RecordCount = 79)


'I could set bRetval here.
'Now the function will always return true.
'But this is the code that I am calling into question. I feel
'like a hacker having to put this in. Isn't there a better way?
'It also does't really make sense to open this recordset here unless
'I am going to be using here. All I am using it for here is to
'check to see if I have the right number of records so I can set
'the return value for the function.

bRetval = (rst.RecordCount = 79)
Do while bRetval = False
rst.Requery
bRetval = (rst.RecordCount = 79)
DoEvents
Loop

ProcessSystems = bRetval
End Function

Thx again for your patience and help.
Silvia

[Rick]

silvia -

This is a suggested structure for the code. Of course, I have not attached
this to a database and run it. And I can't be sure of the scope of your RS
and CNN objects, but this format should allow you to separate the build of
the temp file from its processing.

Let me know how it goes.

Rick



Private Sub SetTemp()

'This routine clears a temp table, then inserts new records based on the
records in rst.
If ProcessSystems(rst) = True Then

'The temp table now SHOULD contain 316 records - 4 each for 79 systems.

'Now open a sum query on the temp table.
Set rstNEW = New ADODB.Recordset
rstNEW.Open sSumQry, mcnn, adOpenStatic, adLockReadOnly


'--------------------------------
'Here is where the problem is. Without this code I get the wrong sums!!

'This will typically requery 5-6 times before the recordcount is correct.
'Do While rst.RecordCount <> 79 ........... ??? 79 or 316
' rst.Requery
' DoEvents
'Loop
'--------------------------------

Else
MsgBox "ProcessSystemes Error"

End If

End Sub


Private Function ProcessSystems(rstIn As ADODB.Recordset) As Boolean

'Set error handling
On Error GoTo ErrorHandler

'Declare vars and objects
Dim rst As ADODB.Recordset

'Clear the temp table
mcnn.Execute "delete * from temp;"

rstIn.MoveFirst
Do While Not rstIn.EOF
'.............
'Processing code...
'.............

'Inserting new records
sSQL = "insert into temp... "
mcnn.Execute

rstIn.MoveNext
Loop

'The temp table now SHOULD contain 316 records - 4 each for 79 systems.


CleanUp:
ProcessSystems = True
Exit Function

ErrorHandler:
ProcessSystems = False

End Function

[James Jensen]

"silvia" <silram128@ureach.com> wrote:
>
>Hi everyone,
>
>How can I tell when it is safe to query a table that I have just inserted
>records into. I keep running into timing problems and having to make my
>program loop until it gets the right answer. Consider the following situation:
>
>
> 'This routine clears a temp table, then inserts new records based
> 'on the records in rst.
> Call ProcessSystems(rst)
>
> 'The temp table now SHOULD contain 316 records - 4 each for 79 systems.
>
> 'Now open a sum query on the temp table.
> Set rst = New ADODB.Recordset
> rst.Open sSumQry, mcnn, adOpenStatic, adLockReadOnly
>
> '--------------------------------
> 'Here is where the problem is. Without this code I get the wrong sums!!
>
> 'This will typically requery 5-6 times before the recordcount is correct.
> Do While rst.RecordCount <> 79
> rst.Requery
> DoEvents
> Loop
> '--------------------------------
>
>
>The problem is not specific to any particular application. As I said I
am
>constantly running into this problem any time I need to do this. I have
>tried looping the .State property of the recordset is equal to adStateFetching
>but this doesn't requery eveon once. I have also tried adStateExecuting
>but this did not work either.
>
>Am I missing something?
>
>Any ideas are appreciated.
>Thx,
>Silvia
>

It's a bit difficult without seeing the code for CallProcessSystems(). However,
it sounds like a transactional issue with the database. Unfortunately, I
am not learned enough in that area to offer good advice. If it is possible,
you may want to consult the db vendor.

-Jim

[Andre Luis]

Hi Silvia,

I never had a problem like yours, of inserting records and when querying
them right after, they don't exist.

I think you should consider reviewing your connection string, then the Connection
object configuration (like asynchronous commands), and make some tests outside
of your VB environment (directly on the database) to check the existence
of locking and concurrency problems.

Are you using a transactional RDBMS like SQL/Oracle or using some desktop
database (Access/DBF) ?

I hope this help.

Andre Luis
andre.freitas@zipmail.com

"silvia" <silram128@ureach.com> wrote:
>
>Hi everyone,
>
>How can I tell when it is safe to query a table that I have just inserted
>records into. I keep running into timing problems and having to make my
>program loop until it gets the right answer. Consider the following situation:
>
>
> 'This routine clears a temp table, then inserts new records based
> 'on the records in rst.
> Call ProcessSystems(rst)
>
> 'The temp table now SHOULD contain 316 records - 4 each for 79 systems.
>
> 'Now open a sum query on the temp table.
> Set rst = New ADODB.Recordset
> rst.Open sSumQry, mcnn, adOpenStatic, adLockReadOnly
>
> '--------------------------------
> 'Here is where the problem is. Without this code I get the wrong sums!!
>
> 'This will typically requery 5-6 times before the recordcount is correct.
> Do While rst.RecordCount <> 79
> rst.Requery
> DoEvents
> Loop
> '--------------------------------
>
>
>The problem is not specific to any particular application. As I said I
am
>constantly running into this problem any time I need to do this. I have
>tried looping the .State property of the recordset is equal to adStateFetching
>but this doesn't requery eveon once. I have also tried adStateExecuting
>but this did not work either.
>
>Am I missing something?
>
>Any ideas are appreciated.
>Thx,
>Silvia
>

[silvia]

"Rick" <rsteinberger@icmarc.org> wrote:
>
>silvia -
>
>This is a suggested structure for the code. Of course, I have not attached
>this to a database and run it. And I can't be sure of the scope of your
RS
>and CNN objects, but this format should allow you to separate the build
of
>the temp file from its processing.
>
>Let me know how it goes.
>
>Rick
>
>
>


Rick,
I figured out what the problem was. I am actually working with two databases.
(I know I didn't mention that). ProcessSystems inserts records into a table
in a central database, then my sum query joins a few tables together in a
local database, one of which is linked from the central database - the one
ProcessSystems just inserted records into. I guess the linked data gets
stale and is not updated immediately after the inserts are done. By adding
some code to delete and relink that table, the problem was solved.

BTW,
I will be incorporating your error handling because it is possible for one
of the inserts to fail and this is something that would need to be caught.

Thank you very much for all your help and patience.
Silvia

[silvia]

"Andre Luis" <andre.freitas@zipmail.com> wrote:
>
>Hi Silvia,
>
>I never had a problem like yours, of inserting records and when querying
>them right after, they don't exist.
>
>I think you should consider reviewing your connection string, then the Connection
>object configuration (like asynchronous commands), and make some tests outside
>of your VB environment (directly on the database) to check the existence
>of locking and concurrency problems.
>
>Are you using a transactional RDBMS like SQL/Oracle or using some desktop
>database (Access/DBF) ?
>
>I hope this help.
>
>Andre Luis
>andre.freitas@zipmail.com
>

Thanks Andre, I have solved the problem. Please see my last reply to Rick
below.

Thx again.
Silvia

[silvia]

"James Jensen" <james_jensen1350@hotmail.com> wrote:
>
>
>It's a bit difficult without seeing the code for CallProcessSystems().
However,
>it sounds like a transactional issue with the database. Unfortunately,
I
>am not learned enough in that area to offer good advice. If it is possible,
>you may want to consult the db vendor.
>
>-Jim
>
>

Thanks Jim, I have just solved this. Please see my last reply to Rick below.

Thx again.
Silvia