Re: ADO Addnew and identity columns

Hans,

The problem is NOT the trigger.
The insert trigger does properly increment the sequence and puts the value in the desired column.
That's exactly what I want to be happen.

The problem is: the ADO Recordset does not immediately return the new value after the Addnew method.
The same source runs perfectly with SqlServer, because then the Addnew really return the new identity value (as it is claad in MSSQL) I've seen a few postings about this issue in several forums, but no one seems te have found a solution....:-(

"Hans" <forbrich_at_gmail.com> wrote in message news:bd0e88c6.0410070755.352fbb3e_at_posting.google.com... > "Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message news:<4164f533$0$78753$e4fe514c_at_news.xs4all.nl>...
> > "Hans" <forbrich_at_gmail.com> wrote in message
> > news:bd0e88c6.0410061202.250cb86b_at_posting.google.com...
> > > "Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message
> > > news:<4163d817$0$78279$e4fe514c_at_news.xs4all.nl>...
> > >> I have an application which is running fine with MS SqlServer, but it
> > >> should
> > >> be working with Oracle as weel.
> > >> At a lot of places we rely upon the ADO Recordset to return
incremented
> > >> identity columns.
> > >> Oralce however returns null or zero.
> > >> How can this be fixed easily?
> > >>
> > >>
> > >> Dim mConn As New ADODB.Connection
> > >> Dim rs As New ADODB.Recordset
> > >>
> > >> mConn.ConnectionString = "Provider=OraOLEDB.Oracle;User
> > >> ID=user;Password=pwd;Data Source=dbname;"
> > >> mConn.Open
> > >>
> > >> rs.Open "select * from testidentity where id < -1", mConn,
> > >> adOpenForwardOnly, adLockPessimistic
> > >> rs.AddNew "name", "peter"
> > >> rs.Update
> > >> MsgBox rs("id")
> > >>
> > >> mConn.Close
> > >
> > > It can not easily be fixed - identity is not a SQL standard. It is a
> > > MS SQLServer peculiarity and you will need to 'port' to get this to
> > > work right.
> > >
> > > The closest you come to a quick solution is to use Oracle's
> > > 'sequences' which serve a similar purpose (handing out unique numbers
> > > serially) but are implemented quite differently. A sequence is
> > > accessed as part of a SQL statement, generally as part of the select
> > > list, using either CURR_VAL or NEXT_VAL 'methods'.
> > >
> > I already have implemented sequences and triggers in the database.
> > I have seen this working because I looked into the database right after
the
> > Addnew function.
> > Problem is still that it is not returned in the recordset.

>
> 1) This should be discussed in comp.databases.oracle.server, not
>     comp.database.oracle (AFAIK, no charter, not an official group)
>     comp.databases.oracle (defunct - see http://orafaq.com)
>
> I have added cdo.server and hopefully we will get more people looking
> at this.  In your reply PLEASE remove the two bad groups from the
> distribution.
>
> 2) I'm not sure I understand the problem.  You say you have created
> sequences and triggers - how are you using them?  Suggest you post the
> trigger code.
>
> /Hans