Re: Can someone please tell me...

On Tue, 21 Mar 2006 17:18:45 GMT, newsgroups@xxxxxxxxxxxxxxxx (Martin Aupperle) wrote:

¤ On Tue, 21 Mar 2006 10:07:08 -0600, Paul Clement
¤ <UseAdddressAtEndofMessage@xxxxxxxxxxxxxx> wrote:
¤
¤ >Oracle doesn't implement identity columns, but a sequence number. You create a sequence for a unique
¤ >(primary key) column in a Table. Once the sequence has been created you can query for the next
¤ >sequence number before inserting a new row or include the call to fetch the next sequence number in
¤ >your SQL statement:
¤ >
¤ >CREATE SEQUENCE supplier_seq
¤ > MINVALUE 1
¤ > START WITH 1
¤ > INCREMENT BY 1
¤ > CACHE 20;
¤ >
¤ >INSERT INTO suppliers
¤ >(supplier_id, supplier_name)
¤ >VALUES
¤ >(supplier_seq.nextval, 'Kraft Foods');
¤ >
¤ Thanks. But how do I get the seqeuence number for a newly inserted
¤ record back into my program? Its the primary key!
¤
¤ Tnx - Martin
¤

You will have to query for the next sequence number first. Since only a single value is returned use
ExecuteOracleScalar (for the Oracle provider) or ExecuteScalar (for OleDb).

SELECT supplier_seq.NEXTVAL FROM dual;

Then specify the returned value in your INSERT

"INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(" & supplier_seq_number & ", 'Kraft Foods')"


Paul
~~~~
Microsoft MVP (Visual Basic)
.

Relevant Pages

  • Re: Can someone please tell me...
    ... ¤>> everyone ... Oracle doesn't implement identity columns, but a sequence number. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ole questions
    ... ¤ All of these things have been verified. ... sequence numbers can get flushed, which means it has to fetch more to fill the cache. ... Our DBA says that cache is turned off. ...
    (microsoft.public.vb.general.discussion)
  • Re: ole questions
    ... ¤ All of these things have been verified. ... Driving me crazy! ... sequence numbers can get flushed, which means it has to fetch more to fill the cache. ... Our DBA says that cache is turned off. ...
    (microsoft.public.vb.general.discussion)
  • Re: Can someone please tell me...
    ... two trips are required if your code requires the value of the sequence number for a subsequent ... ¤>Don't know whether there is any real difference between the methods performance-wise. ... (such as a sequence number) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: multiple column primary key problem
    ... in what event of datagrid should i do this? ... ¤ if my data table is using 3 or 4 fields as primary key, ... with each one of the primary key fields as criteria in the ... WHERE clause, in order to ...
    (microsoft.public.vb.database)