Using OLEDB: How to get the autonumber generated on an insert. 
Author Message
 Using OLEDB: How to get the autonumber generated on an insert.

Hi all,

I'm using OLEDB to access Jet4.0 tables, and VC6.0.

In my relational database I have an autonumber field that is used to link my
related tables. After I call my CCommand::Insert() method, I want to get the
autonumber id that was generated with that insert so I can populate the
related tables.

I'm in a multi user environment so records are being added constantly, so
something like a movelast won't work. I'm also using transactions if that
makes a difference.

Any help would be greatly appreciated.

Cheers,
Brian



Fri, 07 Mar 2003 13:45:35 GMT  
 Using OLEDB: How to get the autonumber generated on an insert.

CRowset Insert(0,true) is followed by GetData().

Quote:

> Hi all,

> I'm using OLEDB to access Jet4.0 tables, and VC6.0.

> In my relational database I have an autonumber field that is used to link my
> related tables. After I call my CCommand::Insert() method, I want to get the
> autonumber id that was generated with that insert so I can populate the
> related tables.

> I'm in a multi user environment so records are being added constantly, so
> something like a movelast won't work. I'm also using transactions if that
> makes a difference.

> Any help would be greatly appreciated.

> Cheers,
> Brian



Sat, 08 Mar 2003 15:30:56 GMT  
 Using OLEDB: How to get the autonumber generated on an insert.
Thanks for the info.

But alas it didn't give the autonumber generated. If I clear the record and
do the GetData() method, it will get the record I added but the autonumber
field is zero. It's like it is getting the data from the rowset in the
cache, not from the actual database. I don't know enough about OLEDB to test
that. Any further help would be appreciated.

Below is a snippet of the code used to test it.

////////////////////////////////////////////////////////////
// Test Code

CCustomer rsCustomer; // CTable<CAccessor<CCustomerAccessor> >

if ( !FAILED(rsCustomer.Insert(1,TRUE)) )
{
 if ( !FAILED(rsCustomer.GetData(0)) )
  cout << rsCustomer.m_CustomerID;
 else
  cout << "Failed to get data");

Quote:
}

else
 cout << "Failed to insert record");

////////////////////////////////////////////////////////////


Quote:

> CRowset Insert(0,true) is followed by GetData().


> > Hi all,

> > I'm using OLEDB to access Jet4.0 tables, and VC6.0.

> > In my relational database I have an autonumber field that is used to
link my
> > related tables. After I call my CCommand::Insert() method, I want to get
the
> > autonumber id that was generated with that insert so I can populate the
> > related tables.

> > I'm in a multi user environment so records are being added constantly,
so
> > something like a movelast won't work. I'm also using transactions if
that
> > makes a difference.

> > Any help would be greatly appreciated.

> > Cheers,
> > Brian



Sun, 09 Mar 2003 03:00:00 GMT  
 Using OLEDB: How to get the autonumber generated on an insert.


Fri, 19 Jun 1992 00:00:00 GMT  
 Using OLEDB: How to get the autonumber generated on an insert.
I had the same problem with ADO and never found a solution.  My IDs were
GUIDs so I eventually gave up asking for the GUID from the new server
and simply created a GUID and stuck it into the new record as the
primary key and as the foreign key in all the other records.  This works
fine and helps identify which station added a record.

This only works if you use GUIDs though.  /Martin

Quote:
-----Original Message-----

Posted At: Wednesday, September 20, 2000 3:22 AM
Posted To: database
Conversation: Using OLEDB: How to get the autonumber generated on an
insert.
Subject: Re: Using OLEDB: How to get the autonumber generated on an
insert.

Thanks for the info.

But alas it didn't give the autonumber generated. If I clear the record
and
do the GetData() method, it will get the record I added but the
autonumber
field is zero. It's like it is getting the data from the rowset in the
cache, not from the actual database. I don't know enough about OLEDB to
test
that. Any further help would be appreciated.

Below is a snippet of the code used to test it.

////////////////////////////////////////////////////////////
// Test Code

CCustomer rsCustomer; // CTable<CAccessor<CCustomerAccessor> >

if ( !FAILED(rsCustomer.Insert(1,TRUE)) )
{
 if ( !FAILED(rsCustomer.GetData(0)) )
  cout << rsCustomer.m_CustomerID;
 else
  cout << "Failed to get data");
}
else
 cout << "Failed to insert record");

////////////////////////////////////////////////////////////



> CRowset Insert(0,true) is followed by GetData().


> > Hi all,

> > I'm using OLEDB to access Jet4.0 tables, and VC6.0.

> > In my relational database I have an autonumber field that is used to
link my
> > related tables. After I call my CCommand::Insert() method, I want to
get
the
> > autonumber id that was generated with that insert so I can populate
the
> > related tables.

> > I'm in a multi user environment so records are being added
constantly,
so
> > something like a movelast won't work. I'm also using transactions if
that
> > makes a difference.

> > Any help would be greatly appreciated.

> > Cheers,
> > Brian



Sun, 09 Mar 2003 03:00:00 GMT  
 Using OLEDB: How to get the autonumber generated on an insert.


Fri, 19 Jun 1992 00:00:00 GMT  
 Using OLEDB: How to get the autonumber generated on an insert.

Sorry you are having trouble with this I am only helping since this is
the only thing I know about. Currently I am using Access 2000 with oledb
templates, and atl wizard generated .h files in vc6. GetData works as I
described, or else I have to get all the software back from those
customers, ha! At first I used a setting DBPROP_SERVERDATAONINSERT in
the accessor but I took this out and it still works. This issue has been
covered before in this newsgroup if you search on it. Here is my
accessor code with my modifications:

class CtblITEM : public CCommand<CAccessor<CtblITEMAccessor> >
{
public:
        HRESULT Open(CDataSource datasource, LPCSTR szSQL = NULL)
        {
        // DataSource object must be initialized.
        HRESULT hr;
        if ( datasource.m_spInit.IsEqualObject( NULL ) )
        {
            TRACE("CtblITEM::Open m_spInit = NULL!\n");
            return E_INVALIDARG;
        }

                hr = m_session.Open(datasource);
                if (FAILED(hr))
                        return hr;

                return OpenRowset(szSQL);
        }
        HRESULT Open()
        {
                HRESULT         hr;

                hr = OpenDataSource();
                if (FAILED(hr))
                        return hr;

                return OpenRowset();
        }
        HRESULT OpenDataSource()
        {
                HRESULT         hr;
                CDataSource db;
                CDBPropSet      dbinit(DBPROPSET_DBINIT);

                dbinit.AddProperty(DBPROP_AUTH_CACHE_AUTHINFO, true);
                dbinit.AddProperty(DBPROP_AUTH_ENCRYPT_PASSWORD, false);
                dbinit.AddProperty(DBPROP_AUTH_MASK_PASSWORD, false);
                dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR(""));
                dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));
                dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("--not used--"));
                dbinit.AddProperty(DBPROP_INIT_MODE, (long)16);
                dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
                dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR(""));
                dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
                dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);

                hr = db.OpenWithServiceComponents(_T("Microsoft.Jet.OLEDB.4.0"),
&dbinit);
                if (FAILED(hr))
                        return hr;

                return m_session.Open(db);
        }
        HRESULT OpenRowset(LPCSTR szSQL = NULL)
        {
                // Set properties for open
                CDBPropSet      propset(DBPROPSET_ROWSET);
                propset.AddProperty(DBPROP_IRowsetChange, true);
                propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE |
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_DELETE);
        //propset.AddProperty(DBPROP_SERVERDATAONINSERT, true);    //
test

        if(szSQL == NULL)
                    return CCommand<CAccessor<CtblITEMAccessor> >::Open(m_session,
NULL, &propset);
                else
            return CCommand<CAccessor<CtblITEMAccessor>

Quote:
>::Open(m_session, szSQL, &propset);

        }
        CSession        m_session;

Quote:
};

And here is the actual usage:

        CtblITEM cmd;
        HRESULT hr = cmd.Open(m_DataSource);
        if( FAILED(hr) )
        {
            DisplayOLEDBErrorRecords( hr );
            if(pErrcode)
                *pErrcode = hr;
            return NULL;
        }

        // Insert new row
        cmd.ClearRecord();      //  FixQuotes(CString cs)

        strcpy( cmd.m_ItemName,         FixQuotes(pItem->m_csItemName)
);
        strcpy( cmd.m_AlternateName,  
FixQuotes(pItem->m_csAlternateName) );
        strcpy( cmd.m_AudioRef,         FixQuotes(pItem->m_csAudioRef) );
        strcpy( cmd.m_HelpURL,          FixQuotes(pItem->m_csHelpURL) );
        strcpy( cmd.m_References,       FixQuotes(pItem->m_csReference) );
        strcpy( cmd.m_Notes,            FixQuotes(pItem->m_csNotes) );

        cmd.m_Relevance                 = pItem->m_fRelevance;

        cmd.m_ulIIDStatus               = DBSTATUS_S_IGNORE;
        cmd.m_ulItemNameLength          = strlen(cmd.m_ItemName);
        cmd.m_ulNotesLength             = strlen(cmd.m_Notes);
        cmd.m_ulReferencesLength        = strlen(cmd.m_References);
        cmd.m_ulAlternateNameLength     = strlen(cmd.m_AlternateName);
        cmd.m_ulAudioRefLength          = strlen(cmd.m_AudioRef);
        cmd.m_ulHelpURLLength           = strlen(cmd.m_HelpURL);

        hr = cmd.Insert(0,true);
        if( FAILED(hr) )
        {
            if(hr != 0x80040e2f) // duplicate
                DisplayOLEDBErrorRecords( hr );
            if(pErrcode)
                *pErrcode = hr;
            return NULL;
        }
        hr = cmd.GetData();
        if( FAILED(hr) )
        {
            DisplayOLEDBErrorRecords( hr );
            if(pErrcode)
                *pErrcode = hr;
            return NULL;
        }

        ASSERT(cmd.m_IID > 0);
        cmd.Close();

Quote:

> Thanks for the info.

> But alas it didn't give the autonumber generated. If I clear the record and
> do the GetData() method, it will get the record I added but the autonumber
> field is zero. It's like it is getting the data from the rowset in the
> cache, not from the actual database. I don't know enough about OLEDB to test
> that. Any further help would be appreciated.

> Below is a snippet of the code used to test it.

> ////////////////////////////////////////////////////////////
> // Test Code

> CCustomer rsCustomer; // CTable<CAccessor<CCustomerAccessor> >

> if ( !FAILED(rsCustomer.Insert(1,TRUE)) )
> {
>  if ( !FAILED(rsCustomer.GetData(0)) )
>   cout << rsCustomer.m_CustomerID;
>  else
>   cout << "Failed to get data");
> }
> else
>  cout << "Failed to insert record");

> ////////////////////////////////////////////////////////////



> > CRowset Insert(0,true) is followed by GetData().


> > > Hi all,

> > > I'm using OLEDB to access Jet4.0 tables, and VC6.0.

> > > In my relational database I have an autonumber field that is used to
> link my
> > > related tables. After I call my CCommand::Insert() method, I want to get
> the
> > > autonumber id that was generated with that insert so I can populate the
> > > related tables.

> > > I'm in a multi user environment so records are being added constantly,
> so
> > > something like a movelast won't work. I'm also using transactions if
> that
> > > makes a difference.

> > > Any help would be greatly appreciated.

> > > Cheers,
> > > Brian



Sun, 09 Mar 2003 03:00:00 GMT  
 Using OLEDB: How to get the autonumber generated on an insert.
Thanks I got it working ok. My problem was that I was using a Jet4.0
datasource thru ODBC. For my project I don't need to use ODBC so I won't
bother finding a solution. Problem solved.

Thanks,
Brian.


** Stuff Deleted **



Mon, 10 Mar 2003 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. autonumber...how to insert a new row?

2. Getting autonumber of newly created record

3. Getting autonumber of a newly added record

4. Help on creating AutoNumber field using DBDAO

5. oledb insert command

6. VC++ 6 and OLEDB Insert

7. How to insert BLOB data into MSSQL with OLEDB templates library

8. Multiple inserts w/OLEDB w/Transactions

9. OLEDB: Cannot Insert BLOBS when CDataSource out of Proc

10. OLEDB, Fail Insert, SQL Server 7

11. OLEDB Insert Problem

12. E_NOINTERFACE on OLEDB Consumer Insert

 

 
Powered by phpBB® Forum Software