ado question


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

Thread: ado question

  1. #1
    silvia Guest

    ado question


    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


  2. #2
    Rick Guest

    Re: ado question



    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
    >



  3. #3
    Ian Hossie Guest

    Re: ado question


    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.



  4. #4
    silvia Guest

    Re: ado question


    "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


  5. #5
    silvia Guest

    Re: ado question


    "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



  6. #6
    Rick Guest

    Re: ado question



    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
    >



  7. #7
    silvia Guest

    Re: ado question


    "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



  8. #8
    Rick Guest

    Re: ado question


    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



  9. #9
    James Jensen Guest

    Re: ado question


    "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



  10. #10
    Andre Luis Guest

    Re: ado question


    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
    >



  11. #11
    silvia Guest

    Re: ado question


    "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





  12. #12
    silvia Guest

    Re: ado question


    "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

  13. #13
    silvia Guest

    Re: ado question


    "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center