Appending to a disconnected recordset

pts.
Tags:
Development
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • PC0der
    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
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following