VB doesn't wait until SQL-query has finished


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: VB doesn't wait until SQL-query has finished

Hybrid View

  1. #1
    Daniel Teufert Guest

    VB doesn't wait until SQL-query has finished


    Hi Folks,
    I hope you can help me, maybe it's just one simple command I can't find in
    my VB books.

    I'm performing a SQL-query from VB 5 to a Access 97 dabatbase. Right after
    that I call a dll that analyses the data from the table I just changed.
    Here is the source code and I thought it would go just fine...

    --------------------------------------------------------------------------
    ' open database and edit the content
    Set rst = dbs.OpenRecordset("select * from stoerdat;")

    If rst.EditMode = dbEditNone Then
    rst.Edit
    End If

    For k = 1 To UBound(arrayMessages)
    rst.AddNew

    rst("Faultmessage") = arrayMessages(k).Msg
    rst("Duration") = arrayMessages(k).Dur
    rst("Starttime") = arrayMessages(k).startMsg
    rst("Endtime") = arrayMessages(k).endMsg
    rst("Profile") = arrayMessages(k).profile
    rst("BrandNo") = arrayMessages(k).brandno
    rst("Startbrand") = arrayMessages(k).startBrand
    rst("Endbrand") = arrayMessages(k).endBrand
    rst("Status") = arrayMessages(k).status
    rst("startAuto") = arrayMessages(k).startAuto
    rst("endAuto") = arrayMessages(k).endAuto

    rst.Update
    Next k

    dbs.Close

    'call dll for evaluation of the data
    call statresult(AAV, AAM, AAS, ADV, ADM, ADS)
    -------------------------------------------------------------------------

    ...but it doesn't!!
    VB doesn't wait, until the SQL-query has finished. So the dll starts the
    evaluation while Access is still changing the data of the table. I don't
    have to tell you that I'm getting wrong results by this. For example the
    table stoerdat contains 60.000 rows, but the dll is called at a time when
    there are just 40.000 rows in that table.

    How can I make VB wait, until Access finished the SQL-query??
    Maybe there is a parameter like 'vbModal' for forms??!!??
    Maybe I don't see the wood for the trees!!

    Thanks a lot for your support,

    Daniel Teufert

  2. #2
    Paul Clement Guest

    Re: VB doesn't wait until SQL-query has finished

    On 23 Apr 2002 00:52:27 -0800, "Daniel Teufert" <teufert.daniel@do.khs-ag.com> wrote:


    Hi Folks,
    I hope you can help me, maybe it's just one simple command I can't find in
    my VB books.

    I'm performing a SQL-query from VB 5 to a Access 97 dabatbase. Right after
    that I call a dll that analyses the data from the table I just changed.
    Here is the source code and I thought it would go just fine...

    --------------------------------------------------------------------------
    ' open database and edit the content
    Set rst = dbs.OpenRecordset("select * from stoerdat;")

    If rst.EditMode = dbEditNone Then
    rst.Edit
    End If

    For k = 1 To UBound(arrayMessages)
    rst.AddNew

    rst("Faultmessage") = arrayMessages(k).Msg
    rst("Duration") = arrayMessages(k).Dur
    rst("Starttime") = arrayMessages(k).startMsg
    rst("Endtime") = arrayMessages(k).endMsg
    rst("Profile") = arrayMessages(k).profile
    rst("BrandNo") = arrayMessages(k).brandno
    rst("Startbrand") = arrayMessages(k).startBrand
    rst("Endbrand") = arrayMessages(k).endBrand
    rst("Status") = arrayMessages(k).status
    rst("startAuto") = arrayMessages(k).startAuto
    rst("endAuto") = arrayMessages(k).endAuto

    rst.Update
    Next k

    dbs.Close

    'call dll for evaluation of the data
    call statresult(AAV, AAM, AAS, ADV, ADM, ADS)
    -------------------------------------------------------------------------

    ..but it doesn't!!
    VB doesn't wait, until the SQL-query has finished. So the dll starts the
    evaluation while Access is still changing the data of the table. I don't
    have to tell you that I'm getting wrong results by this. For example the
    table stoerdat contains 60.000 rows, but the dll is called at a time when
    there are just 40.000 rows in that table.

    How can I make VB wait, until Access finished the SQL-query??
    Maybe there is a parameter like 'vbModal' for forms??!!??
    Maybe I don't see the wood for the trees!!


    The only thing I can think of here is that there are implicit transactions pending when you execute
    your statresult function.

    You might want to try wrapping your Update code between BeginTrans and CommitTrans (with the
    dbFlushOSCacheWrites argument) to ensure that all updates have been committed.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  3. #3
    Menghraj P Guest

    Re: VB doesn't wait until SQL-query has finished


    HI Daniel

    if ur "call statresult" this call is to another dll which is making another
    connection to Access db then the changes done by one connection are not avialable
    for another connection to do processing so in ur case its not tht VB is not
    waiting for query to finish but the problem is Access is not giving away
    data for another connection. i bet if u run ur code in debug mode it will
    work fine. Try to use same connection for working on ur data from VB code
    and dll by passing connection object.
    look at this article from msdn october 1999 collection


    get back o me if u need more info there is nuthing like tht VB is not waiting
    for query to finish or something like tht i can replicate the same problem
    100 out of 100 times pal

    PRB: Single-User Concurrency Problems With ADO and Jet
    ID: Q216925



    --------------------------------------------------------------------------------
    The information in this article applies to:

    Microsoft Visual Basic Professional and Enterprise Editions for Windows,
    version 6.0
    ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1

    --------------------------------------------------------------------------------


    SYMPTOMS
    When using ADO against a Microsoft Jet database, you may notice that saving
    information via the ADO Data control or an ADO Recordset is not immediately
    visible to other ADO Data controls or Recordsets. The data is visible after
    a few seconds delay.



    CAUSE
    Each object is opened on a different connection.



    RESOLUTION
    Make all ADO Recordset objects and ADO Data controls use the same connection.




    STATUS
    This behavior is by design.



    MORE INFORMATION
    Microsoft Jet maintains a separate cache for each connection to the engine.
    By default, the cache times out after five seconds, which means that changes
    made to an MDB file on one connection may not be visible on another connection
    for up to five seconds.

    This is different behavior than DAO, where all Connection or Database objects
    default to share the same read cache.

    This article outlines some strategies that allow you to share connections
    between Recordset objects and the ADO Data control.

    When opening an ADO Recordset object, it is easy to share a connection. You
    can open a global connection object and reference it in the rs.Open method.
    This method can provide application or form-wide connection sharing:



    rs1.Open "Employees", cn
    rs2.Open "Employees", cn

    Another more limited method of connection sharing is to reference the ActiveConnection
    property of an already open Recordset:


    rs1.Open "Employees", "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=NWIND.MDB"
    rs2.Open "Employees", rs1.ActiveConnection

    You can open a Recordset on the same connection as an ADO Data control by
    referencing the ActiveConnection property of the Recordset property:


    rs.Open "Employees", ADODC1.Recordset.ActiveConnection

    If the ADO Data control (ADODC2, ADODC3, and ADODC4 in the example below)
    is using client-side cursors, then you can make it share an existing connection:


    Set ADODC2.Recordset.ActiveConnection = cn
    Set ADODC3.Recordset.ActiveConnection = rs.ActiveConnection
    Set ADODC4.Recordset.ActiveConnection = ADODC1.Recordset.ActiveConnection


    The next technique allows ADO Data controls to share connections and use
    server-side cursors. It involves setting the ADO Data control's Recordset
    property to an existing Recordset that you create using a shared Connection
    object.

    NOTE: You cannot substitute the connection if using server-side cursors (the
    default):


    rs.Open "Employees", cn
    Set ADODC1.Recordset = rs


    Steps to Reproduce Behavior
    Open a new Project in Visual Basic 6.0 with a form (Form1).


    Add a reference to Microsoft ActiveX Data Objects 2.0 Library.


    Add the ADO Data Control (6.0) to the project.


    Add the ADO Data control to the form and set the following properties:

    Property Value
    Name ADODC1
    CommandType adCmdTable
    ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Data Source=NWIND.MDB

    CursorLocation adUseServer
    CursorType adOpenStatic
    LockType adLockOptimistic
    RecordSource Employees



    Add four textboxes to the form and set the following properties:

    Property Value
    Name FirstName
    DataSource ADODC1
    DataField FirstName

    Property Value
    Name LastName
    DataSource ADODC1
    DataField LastName

    Property Value
    Name SameConnection

    Property Value
    Name NewConnection



    Add a CommandButton (Command1) to the form.


    Add the following code to the form:


    Option Explicit

    Private Sub Command1_Click()
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    ' Save the data if necessary
    If ADODC1.Recordset.EditMode <> adEditNone Then
    ADODC1.Recordset.Update
    End If

    ' Open Recordset on same connection as the data control
    rs.Open "Employees", ADODC1.Recordset.ActiveConnection, , , adCmdTable
    SameConnection.Text = rs!LastName
    rs.Close

    ' Open recordset on a new connection
    rs.Open "Employees", ADODC1.ConnectionString, , , adCmdTable
    NewConnection.Text = rs!LastName
    rs.Close
    End Sub



    Run the project and edit the Employee's last name.


    Click on the CommandButton to save the data, and then read it. You should
    see the edited value displayed in the SameConnection textbox and the original
    value displayed in the NewConnection textbox.


    Wait a few seconds and click the CommandButton again. The edited value should
    now also show in the NewConnection text box. This is due to the cache timing
    out.

    NOTE: When using the ADO Data control in the manner described above, avoid
    using the Refresh method:


    ADODC1.Refresh
    because this will result in a new connection. Use the Requery method of the
    Recordset property instead:

    ADODC1.Recordset.Requery



    Microsoft Corporation 1999, All Rights Reserved.
    Contributions by Malcolm Stewart, Microsoft Corporation


    Additional query words:


    Keywords : kbADO200 kbJET kbVBp kbVBp600 kbGrpVBDB
    Version : WINDOWS:2.0,2.01,2.1,6.0
    Platform : WINDOWS
    Issue type : kbprb

    Last Reviewed: April 3, 1999


    --------------------------------------------------------------------------------
    Send feedback to MSDN.Look here for MSDN Online resources.


    "Daniel Teufert" <teufert.daniel@do.khs-ag.com> wrote:
    >
    >Hi Folks,
    >I hope you can help me, maybe it's just one simple command I can't find

    in
    >my VB books.
    >
    >I'm performing a SQL-query from VB 5 to a Access 97 dabatbase. Right after
    >that I call a dll that analyses the data from the table I just changed.
    >Here is the source code and I thought it would go just fine...
    >
    >--------------------------------------------------------------------------
    >' open database and edit the content
    >Set rst = dbs.OpenRecordset("select * from stoerdat;")
    >
    > If rst.EditMode = dbEditNone Then
    > rst.Edit
    > End If
    >
    > For k = 1 To UBound(arrayMessages)
    > rst.AddNew
    >
    > rst("Faultmessage") = arrayMessages(k).Msg
    > rst("Duration") = arrayMessages(k).Dur
    > rst("Starttime") = arrayMessages(k).startMsg
    > rst("Endtime") = arrayMessages(k).endMsg
    > rst("Profile") = arrayMessages(k).profile
    > rst("BrandNo") = arrayMessages(k).brandno
    > rst("Startbrand") = arrayMessages(k).startBrand
    > rst("Endbrand") = arrayMessages(k).endBrand
    > rst("Status") = arrayMessages(k).status
    > rst("startAuto") = arrayMessages(k).startAuto
    > rst("endAuto") = arrayMessages(k).endAuto
    >
    > rst.Update
    > Next k
    >
    >dbs.Close
    >
    >'call dll for evaluation of the data
    >call statresult(AAV, AAM, AAS, ADV, ADM, ADS)
    >-------------------------------------------------------------------------
    >
    >...but it doesn't!!
    >VB doesn't wait, until the SQL-query has finished. So the dll starts the
    >evaluation while Access is still changing the data of the table. I don't
    >have to tell you that I'm getting wrong results by this. For example the
    >table stoerdat contains 60.000 rows, but the dll is called at a time when
    >there are just 40.000 rows in that table.
    >
    >How can I make VB wait, until Access finished the SQL-query??
    >Maybe there is a parameter like 'vbModal' for forms??!!??
    >Maybe I don't see the wood for the trees!!
    >
    >Thanks a lot for your support,
    >
    >Daniel Teufert



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