Server Data on Insert property

[Jan van Veldhuizen]

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/w...0115/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;

[Jan van Veldhuizen]

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" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@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/w...0115/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;

[Jan van Veldhuizen]

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" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@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" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@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/w...0115/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;