Re: An Oracle Auto Generated Id Issue

"ryan" <ramon_at_caribsurf.com> wrote in message news:54342613.0304140919.3452d63a_at_posting.google.com...
> Hi
>
> I am currently developing an application with an Oracle
> backend after years of using Access and SQL Server.
>
> With Access and SQL Server there was an auto-increment option
> that can be specified for primary keys however unless I have
> missed somthing in Oracle one has to use the Sequence/Trigger method

Correct, and it has to be a 'before insert' trigger.

> My problem occurs when I am adding a new record to a table with an
> auto-incremented primary key within the application. Within VB, I use
> a third party component to manipulate data within the database and
> when
> I try to save the new record, the component gives an error indicating
> that the primary key field cannot be left empty.
>
> When I use SQL + and an Insert SQL statement for the same table the
> new
> record is added with no complaints about the primary key field. How
> can I
> get around this problem? Is there a property/setting within Oracle for
> the control to know that the primary key is being auto
> generated/incremented
> by use of the sequence/trigger?

No. If it works in SQL Plus, but fails in your 'component', then your component is doing something odd. And if you can't control what it is doing, then you're going to be in trouble.

Out of interest, knock up an Access front-end that allows you to insert every field bar the primary key into a series of simple form fields. If the before insert trigger is correctly done on the back-end, you should be able to insert a new record in Access without any coding on your part at all.

If you can get it working in Access, and SQL Plus, it's time to consult the vendor of your third-party component.

Regards
HJR
>
> thanks in advance
> ryan
Received on Mon Apr 14 2003 - 14:46:28 CDT