Re: ADO Addnew and identity columns

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:JEb9d.216386$3l3.87609_at_attbi_s03...
>
> "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.
>>
>>
> Use the returning clause to get the values back out.
> Jim
>
>
The returning clause is used with the INSERT statement. I use the ADO Addnew function. Received on Fri Oct 08 2004 - 11:38:16 CEST