MelissaEvans
Programmer
Overview:
I have an ADO connection object and recordset object. I need to be able to add new records to the recordset object and so I am using the AddNew method. (My other option is to do it "behind its back" by executing a SQL insert statement; but I can only get a static cursor, so it doesn't see the new one. To get around that, I'd have to resync or close then open the recordset and as there are 10,000 records in my test table - 100,000 in the real one - so that is not a valid option.)
Problem:
The problem is the ID field (primary key) is not updated in my recorset, it always comes back as 0. Even looking at the UnderlyingValue property shows 0; but there really honestly is a valid key in the table itself. Without having the key, I cannot alter or delete the record (it complains that the key I have in the recordset has been deleted in the table). The primary key is an ID field that automatically
incriments itself.
Details:
I am connecting to a SQL server database, but my manager doesn't want to be tied to this forever - our customers would love to be able to use Access also. I am currently using the MSDASQL provider (ODBC) and Client side cursor (server side brakes other things) for the connection object. The recordset has a Static cursor type (I have requested KeySet and Dynamic, but always get Static) and the lock types I have tried are BatchOptimistic and Optimistic.
What I've Tried:
I *can* get the (assumed) new primary key (I look for the largest value in the table itself immediately after the AddNew). This isn't ideal as there may be multiple people adding at the same time that could cause problems. I tried to use the @@IDENTITY property, but it only returns 0 (and it's a global variable for the entire database, there are other tables that could be added to and change this value to something totally wrong - not to mentiong this is a SQL only solution). Even assuming I can get the correct key
each time (a leap of faith), I can't do anything with it. I've tried to assign the key value I retrieved to the key field in my recordset; but that field is not updatable.
Any ideas on how to get around this?
I have an ADO connection object and recordset object. I need to be able to add new records to the recordset object and so I am using the AddNew method. (My other option is to do it "behind its back" by executing a SQL insert statement; but I can only get a static cursor, so it doesn't see the new one. To get around that, I'd have to resync or close then open the recordset and as there are 10,000 records in my test table - 100,000 in the real one - so that is not a valid option.)
Problem:
The problem is the ID field (primary key) is not updated in my recorset, it always comes back as 0. Even looking at the UnderlyingValue property shows 0; but there really honestly is a valid key in the table itself. Without having the key, I cannot alter or delete the record (it complains that the key I have in the recordset has been deleted in the table). The primary key is an ID field that automatically
incriments itself.
Details:
I am connecting to a SQL server database, but my manager doesn't want to be tied to this forever - our customers would love to be able to use Access also. I am currently using the MSDASQL provider (ODBC) and Client side cursor (server side brakes other things) for the connection object. The recordset has a Static cursor type (I have requested KeySet and Dynamic, but always get Static) and the lock types I have tried are BatchOptimistic and Optimistic.
What I've Tried:
I *can* get the (assumed) new primary key (I look for the largest value in the table itself immediately after the AddNew). This isn't ideal as there may be multiple people adding at the same time that could cause problems. I tried to use the @@IDENTITY property, but it only returns 0 (and it's a global variable for the entire database, there are other tables that could be added to and change this value to something totally wrong - not to mentiong this is a SQL only solution). Even assuming I can get the correct key
each time (a leap of faith), I can't do anything with it. I've tried to assign the key value I retrieved to the key field in my recordset; but that field is not updatable.
Any ideas on how to get around this?