Question:
I have a very large table that contains thousands of rows. I want to let a user insert new rows into it on a particular form without having to query the table. How can I create a recordset that is bound to a table and session so that I can quickly insert the row and then close the recordset?
Answer:
If you want to use a recordset to insert the row, it’s not a big effort. You can create a standalone recordset with the NEW DBRECORDSET function. You then associate an SQL query with the recordset with the SetQuery() method of the recordset, making sure to set the second parameter of the method to TRUE, which will allow the recordset to be updatable. Executing the SetQuery() method will cause a recordset to be created, with columns for each column mentioned in the SQL query, but will not populate the recordset with any columns.
After the recordset is created, you can simply use the InsertRow() method to add a row to the recordset and set the column values appropriately. Your final step will be to commit the transaction with the CommitWork() method of the session and delete the recordset.
If your session name was ORDERDAT, the table was ITEMS, and the required fields for the table were ITEM_NUMBER, DESCRIPTION and PRICE, the code to implement the above functionality would be:
DIM oRecSet AS objectoRecSet = NEW DBRECORDSET(ORDERDAT)oRecSet.SetQuery(“select ITEM_NUMBER,DESCRIPTION,PRICE from items”,true)oRecSet.InsertRow()oRecSet.SetColVal(“ITEM_NUMBER”, 99)oRecSet.SetColVal(“DESCRIPTION”, “Oh boy!”)oRecSet.SetColVal(“PRICE”,100)ORDERDAT.CommitWork()DELETE oRecSet
But you may not have to do even this much work. You could also use bound containers in a form and just set the DefaultCondition to a condition that you know will not return any rows, such as “1 = 0”. This will prevent any rows from being retrieved, while still allow you to use all the default functionality of the form to insert and commit rows.Finally, remember that Power Objects is optimized for client-server data retrieval. If you use one of the incremental fetch modes for the RowFetchMode property, only a small subset of rows will be returned to your form.
