Re: Server Data on Insert property

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" <jan_at_van-veldhuizen.nl> wrote in message news:416b9d6a$0$36861$e4fe514c_at_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;
>
>
Received on Tue Oct 12 2004 - 15:54:46 CEST