Hi !

If got a problem that will take a while to describe:

I'm working on an SQL-Server Database via ODBC with CDatabase an CRecordset-derived classes.

The database got several relations between tables.

I've got to tables like:
TABLE ADRESS
-------------------
ID PRIMARY KEY, IDENTY (automatically updated)
Name
adress
... etc

TABLE CUSTOMER
----------------
CustNo PRIMARY KEY - but that doesn't matter here
AdressID FOREIGN KEY, UNIQUE
... additional customer data

and a relation from customer.AdressID into adress.ID

Now to the problem:
I want to insert an entry in the customer table and a corresponding entry in the adress table.

So i do:

pAdr: pointer to class derived from CRecordset reflecting adress table
pCus: pointer .... reflecting customer table

BOTH RECORDSETS ARE OPEND AS SNAPSHOT, 'CAUSE DYNASET IS NOT SUPPORTED FOR DATABASE !!!!

----
long adrID;

pAdr->AddNew();
pAdr->m_Name = ...
pAdr->m_... = ...
...
adrID = m_ID; // ID makes no sense ... value like 123456798
pAdr->Update();
adrID = m_ID; // ID of the row positioned last BEFORE AddNew();
--------

now I need the newly added ID of the new adress row to do:

----------
pCus->AddNew();
pCus->m_CustNo = ...
pCus->m_adrID = adrID;
...
---------

How can I get the ID of the newly added adress row WITHOUT requerying pAdr ? the new row isn't member of the snaphot-Recordset .... and I dont have any other unique values in the adress-table except the ID ....

When working with dynasets in an earlyer project I could use:
----
...
pAdr->Update();
pAdr->MoveLast();
adrID = pAdr->m_ID;
-----
in a snapshot this doesn't work ...

Does any one know a workaround ? Perhaps with an CDatabase::ExecuteSQL( "INSERT ... " ) statement ? I would'nt like this, cause actually the tables have MANY fields ...


Please help me out ....

Thanks...

Marco

eMail: [email protected]