Re: ADODC Requery and refresh don't work right

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Val Mazur (group51a_at_hotmail.com)
Date: 06/04/04 

Date: Thu, 3 Jun 2004 22:03:14 -0400

Hi Michael,

Not each type of the cursor reflects changes in a database. If you recordset
is opened on a client side, then it is a static-type cursor, which does not
allow to reflect latest changes in a database. In this case you need to use
Requery method to get them from the database. Only Dynamic type of
cursor/recordset allows recordset to see dynamic changes in a database. What
you need to do is to open recordset on a server side as a dynamic, but in
this case Filter property will work much slower, because it would require
roundtrips between the server and client. 

-- 
Val Mazur
Microsoft MVP
"Michael Lee" <anonymous@discussions.microsoft.com> wrote in message 
news:58D8293A-0196-47AB-9638-D5CB80035B45@microsoft.com...
> Hi Everyone,
> I hope you all can give some insite on this problem. I'm having alot of 
> problems with the ADODC control. I have a True DBgrid displaying the order 
> details that are filtered through the data control (Filter prooerty). The 
> recordset contains a join between two tables.
> The problem is that when a record is deleted through the code below, the 
> recordcount is still the same as before the deletion, the record is 
> deleted (verified right after the execute command) but the 
> grid/datacontrol refuses to reflect the change. What could be happening. 
> Could it have something to do with the filtering or what. Also if I choose 
> another order (which sets a filter on the recordset, I see the items for 
> that order, but when I go back to the original order, the grid still 
> displays the deleted record. If I close the form and reload it again, the 
> deleted record no longer there. It seems as if the grid/datacontrol is 
> holding the data in a buffer and not refreshing it. One more thing, if I 
> put a breakpoint at the end of this function, or pause the app right after 
> a deletion, I can do a data1.Requery and then check the recordcount, the 
> count is correct. Any ideas. Please help. Thanks
> Michael
>
>
> Private Sub cmdDelete_Click()
> On Error GoTo Err
> Dim sql As String
> Debug.Print "Starting Delete"
>   pIsDeleteRow = True
> Debug.Print "Record Count Before Delete: " & 
> datOrderItems.Recordset.RecordCount
> ' Delete the selected record
>   sql = "Delete from OrderDetails where Orderid = " & 
> datOrderItems.Recordset!OrderId
>   sql = sql & " and ProductId = '" & datOrderItems.Recordset!ProductId & 
> "'"
>   gconn.Execute sql
> Debug.Print "Record Deleted!"
> Debug.Print "Record Count: " & datOrderItems.Recordset.RecordCount
>   datOrderItems.Recordset.Requery
> Debug.Print "Record Count after Requery: " & 
> datOrderItems.Recordset.RecordCount
>   DoEvents
>   If Not datOrderItems.Recordset.BOF Or Not datOrderItems.Recordset.EOF 
> Then
>      datOrderItems.Recordset.MoveNext
>      If datOrderItems.Recordset.EOF Then
>       datOrderItems.Recordset.MoveFirst
>      End If
>   End If
>   datOrderItems.Recordset.Filter = adFilterNone
>   datOrderItems.Recordset.Filter = "Orderid = " & 
> cmbOrders.Columns.Item(0).Text
>   Debug.Print "Record Count after Filter: " & 
> datOrderItems.Recordset.RecordCount
>   pIsDeleteRow = False
>
>  Debug.Print "Record Count after 2nd Requery: " & 
> datOrderItems.Recordset.RecordCount
>   Exit Sub
> Err:
>   MsgBox Err.Description
> End Sub
>
>

Relevant Pages

  • Re: Change ROW colors in a grid.
    ... quick database. ... SQL languange implementation in VFP9 was however greatly enhanced in ... I select one of the records from the cursor and modify that record ... color but when I do a filter to the table and then look at the ...
    (microsoft.public.fox.programmer.exchange)
  • Re: combo box not working
    ... This database will be used to send automatic emails, ... Excel does not update in real-time and sharing such a database would ... I added a combo box....tried to set the filter and it does work. ... Since you only want to filter this list by client, ...
    (microsoft.public.access.formscoding)
  • Re: combo box not working
    ... Microsoft Office/Access MVP ... This database will be used to send automatic emails, ... I added a combo box....tried to set the filter and it does work. ... Since you only want to filter this list by client, ...
    (microsoft.public.access.formscoding)
  • Re: insert statement blocked
    ... My protection to duplicate entries is handled by the client ... Even if integrity is enforced in the application, the database must still be ... After that, using a cursor, I intend to fetch the records ... from the temp table. ...
    (comp.databases.ms-sqlserver)
  • Linking Forms for new record
    ... basing it on a prior database created in DOS, so I can't change too much so ... SSNs, and checks to see if they are new or existing records. ... opens Client Information with the new SSNs filled in, ... I have noticed that if I click on the toggle filter button twice (off then ...
    (microsoft.public.access.forms)