XPost: comp.database.oracle, relcom.comp.dbms.oracle   
   From: jan@van-veldhuizen.nl   
      
   "Hans"  wrote in message   
   news:bd0e88c6.0410061202.250cb86b@posting.google.com...   
   > "Jan van Veldhuizen"  wrote in message   
   > news:<4163d817$0$78279$e4fe514c@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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)