Re: ADO Addnew and identity columns

"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. Received on Thu Oct 07 2004 - 09:47:31 CEST