Identity column and Disconnected recordset 
Author Message
 Identity column and Disconnected recordset

I have 2 tables in SQL Server (let's say Table_A and Table_B). The primary
keys for those tables are identities. Table_B has a foreign key from
Table_A.

I have a master-detail form with 2 grids. Each grid are binded to a
disconnected recordsets representing these tables (rsTable_A and rsTable_B).
When I select a record in the grid representing Table_A, the rsTable_B is
filtered to display only records relative to the one selected in rsTable_A
(using the foreign key).

So when I insert a record in rsTable_B, I have to specify the foreign key
programatically... but when I insert a record in rsTable_A, the primary key
(identity) is set to 0... so how can I link my new records in rsTable_B to
new records in rsTable_A ?

Sorry for my bad english, hope It's clear enough :o)

Thanks

Marc



Fri, 15 Oct 2004 22:49:03 GMT  
 Identity column and Disconnected recordset
Marc,
    You have run into the main problem with auto assigned identity values -
you don't know what they are until later.
    My suggestion would be to change the key field type to another type
(integer?) and keep a setup table that tracks the next ID value. Otherwise,
especially with disconnected recordsets, you are out of luck with getting
the ID value until you have saved back the information.
Sorry,
CF

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Quote:

> I have 2 tables in SQL Server (let's say Table_A and Table_B). The primary
> keys for those tables are identities. Table_B has a foreign key from
> Table_A.

> I have a master-detail form with 2 grids. Each grid are binded to a
> disconnected recordsets representing these tables (rsTable_A and
rsTable_B).
> When I select a record in the grid representing Table_A, the rsTable_B is
> filtered to display only records relative to the one selected in rsTable_A
> (using the foreign key).

> So when I insert a record in rsTable_B, I have to specify the foreign key
> programatically... but when I insert a record in rsTable_A, the primary
key
> (identity) is set to 0... so how can I link my new records in rsTable_B to
> new records in rsTable_A ?

> Sorry for my bad english, hope It's clear enough :o)

> Thanks

> Marc



Sat, 16 Oct 2004 04:03:49 GMT  
 Identity column and Disconnected recordset
You would need to save the record in table_A so that the identity field is
applied,
then


 to get the latest identity value from that table.

Kevin

Quote:

> I have 2 tables in SQL Server (let's say Table_A and Table_B). The primary
> keys for those tables are identities. Table_B has a foreign key from
> Table_A.

> I have a master-detail form with 2 grids. Each grid are binded to a
> disconnected recordsets representing these tables (rsTable_A and
rsTable_B).
> When I select a record in the grid representing Table_A, the rsTable_B is
> filtered to display only records relative to the one selected in rsTable_A
> (using the foreign key).

> So when I insert a record in rsTable_B, I have to specify the foreign key
> programatically... but when I insert a record in rsTable_A, the primary
key
> (identity) is set to 0... so how can I link my new records in rsTable_B to
> new records in rsTable_A ?

> Sorry for my bad english, hope It's clear enough :o)

> Thanks

> Marc



Sun, 31 Oct 2004 13:46:27 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Identity column and Disconnected recordset

2. How can I creat an Identity field in a Disconnected Recordset

3. Updating Identity field in disconnected recordset

4. Checking an identity in a disconnected recordset

5. How to sum column of a disconnect recordset

6. ADO fails to release Recordset cursor after opening disconnected Recordset

7. SQL SERVER + IDENTITY column + DbSeeChanges PROBLEM

8. OpenRecordset and SQL Table w/ IDENTITY Column

9. Run time error 3622: OpenRecordSet: SQL server table: IDENTITY column

10. identity column (autonumber)

11. error 3622 - table has identity column

12. IDENTITY Column

 

 
Powered by phpBB® Forum Software