Appending to a disconnected recordset
My problem is this;
I have a disconnected recordset which contains say 3 rows from a sql server database (ado 2.6) and normally i will change this data and then use a mts componet to update the recordset using updatebatch. This works find, the problem I have is when I have the 3 rows in the recordset and I discover that I need to add another row from the database table. Is there a way to issue a query to the database and tell it to append to the existing database??
I have tried to read the new record into a temp recordset then use the addnew on the original recordset and move the temp to the original field by field, however when I use the updatebatch it thinks this record I added is a new record to the database and of course fails on primary key constraint.
Thanks
Jack Dagnon
Software/Hardware used:
Software/Hardware used:
ASKED:
February 18, 2005 7:08 PM
UPDATED:
February 28, 2005 5:19 PM
Answer Wiki:
Hello,
I don't know ado specificaly.
You may have an isolation level issue : if you have 2 distinct transactions, if the first transaction (say tranA) selects some data and if the second transaction (say tranB) inserts some data after the tranA has begun, then, depending on isolation level, the tranA will not be aware of data inserted by tranB.
Solutions are to abort tranA and start a new transaction after tranB has been commited or to change isolation level so that transactions are less isolated from each other.
To see all answers submitted to the Answer Wiki: View Answer History.
Whats wrong with just rerunning your recordset query? As is sounds you need to connect to the database to query the data anyway, let it just find all new records that should be included. I am not familiar with the updatebatch command you mention but as I understand recordsets, once the query has been done and results loaded into the recordset then the recordset is read-only till you close it. It seems to me that you would undergo more computational headache trying to update this query (should it even be possible) then just rerunning the query. Just my humble take though… correct me if im wrong.
pC0der