Question:
When I insert a row into a table, the results from the query that come after the row don’t show up. If I re-fetch the results, I can get at all the results, but the row is not where I put it. Why?
Answer:
What you are seeing are the expected results of three underlying actions in Power Objects.
When you click on the Insert push button in the tool bar, it inserts a row into the recordset at the position of the current row. If your current row is in position 4, the new row is inserted in position 4 and all subsequent rows are moved back a postion in the recordset.
The recordset remains these positions for its lifetime. When you re-fetch your data, you build a new recordset, whose order is determined by either the OrderBy property of the form or the index used by default to retrieve the data.
The reason you seem to be ‘missing’ some results after your insert has to do with the interaction between your application and the database when the RowFetchMode is set to one of the incremental fetch modes, “Fetch As Needed” or “Fetch Count First”. It is good practice to commit your write operations, such as an insert, as soon as possible after the operation is complete to avoid causing too much contention in the database. Once you commit a transaction, the cursor for the transaction is destroyed by the database. Once the cursor for the transaction is destroyed, you cannot fetch any more data. So, if you are incrementally fetching data and do a commit or a roll back, you will be unable to retrieve any more data from the database, leaving you with an incomplete recordset in your application.
You can handle this situation by calling the FetchAllRows() method for a form that uses an incremental fetch mode before you commit or rollback a transaction.
