XPost: comp.databases.oracle.misc, comp.databases.oracle.server   
   From: jan@van-veldhuizen.nl   
      
   And the fun is:   
      
   there's only *ONE* difference with the SqlServer behaviour here: SqlServer   
   requires a ClientSide cursor for this functionality.   
   So I still am able to make minor changes to my source to let it work with   
   both databases.   
      
   "Jan van Veldhuizen"  wrote in message   
   news:416be207$0$25965$e4fe514c@news.xs4all.nl...   
   >I found the solution myself:   
   >   
   > The connection.cursorlocation should be adUseServer and you must use   
   > adOpenKeyset at the Open method.   
   >   
   > So this is working:   
   >   
   > Dim mConn As New ADODB.Connection   
   > Dim rs As New ADODB.Recordset   
   >   
   > mConn.ConnectionString = "Provider=OraOLEDB.Oracle;User   
   > ID=jan;Password=test;Data Source=test;   
   > mConn.CursorLocation = adUseServer   
   > mConn.Open   
   >   
   > rs.Open "select * from test where id < -1", mConn, adOpenKeyset,   
   > adLockOptimistic   
   > rs.AddNew "name", "newname"   
   > rs.Update   
   >   
   > MsgBox rs("id")   
   >   
   > mConn.Close   
   >   
   >   
   >   
   > "Jan van Veldhuizen"  wrote in message   
   > news:416b9d6a$0$36861$e4fe514c@news.xs4all.nl...   
   >>I try to use the Server Data on Insert property to return a column value   
   >>swhich is set in a trigger.   
   >> But it does not work. Why not? What am I doing wrong?   
   >> (BTW I'm using Oracle version 10)   
   >>   
   >> My VB source is:   
   >>   
   >> Dim mConn As New ADODB.Connection   
   >> Dim rs As New ADODB.Recordset   
   >>   
   >> mConn.ConnectionString = "Provider=OraOLEDB.Oracle;User   
   >> ID=jan;Password=test;Data Source=test;"   
   >> mConn.Open   
   >>   
   >> rs.ActiveConnection = mConn   
   >> rs.CursorLocation = adUseServer   
   >> rs.Properties("Server Data on Insert").Value = True   
   >>   
   >> rs.Open "select * from test where id < -1", mConn, adOpenForwardOnly,   
   >> adLockOptimistic   
   >> rs.AddNew "name", "newname"   
   >> rs.Update   
   >>   
   >> MsgBox rs("id")   
   >>   
   >> mConn.Close   
   >>   
   >> The test table has an ID column which is filled in a trigger by getting a   
   >> sequence nextvalue.   
   >>   
   >> I am using this source because I use this way of insert all over the   
   >> application with a SqlServer database. The ID column in de Sql2000   
   >> database is an Identity column which immediately returns it's new value   
   >> after the AddNew.   
   >> I am looking for a way to make my application working at an Oracle   
   >> database without a lot of source modifications.   
   >> I found this article:   
   >> http://www.tju.cn/docs/odb10.1.0.2/win.101/b10115/using.htm where the   
   >> 'Server Data on Insert' property is described.   
   >>   
   >> If anybody wants to test it in his own database, this is the definition   
   >> of the table:   
   >>   
   >> CREATE TABLE Test (   
   >> id  NUMBER,   
   >> name VARCHAR2(20)   
   >> );   
   >>   
   >> CREATE SEQUENCE seqTest;   
   >>   
   >> CREATE TRIGGER newTest   
   >> BEFORE INSERT ON Test   
   >>  FOR EACH ROW   
   >>  WHEN (NEW.id IS NULL)   
   >>  BEGIN   
   >>    SELECT seqTest.NEXTVAL INTO :new.id FROM DUAL;   
   >>  END;   
   >>   
   >>   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)