asked on

ADO recordset.Addnew and returning a sequence value

I have an application which is running fine with MS SqlServer, but it should
be working with Oracle as well.
At a lot of places we rely upon the ADO Recordset to return incremented
identity columns.
Oralce however returns null or zero, but I've seen via isqlplus that the sequence and trigger has done it's job. When I do a Requery at that dataset, the correct values are returned. But I cannot do a Requery because I want to knwo the unique value immediately.

SqlServer, Sybase, MySql all do this properly. How can this be fixed with Oracle 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

seazodiac

well, you should create oracle's version of sequence object , equivalent of testidentity in SQL server.

try this in your oracle:

SQL>create sequence test_seq start with 1 ;

optionally, create a public synonym if you have different users to use it:

SQL>create public synonym test_seq for test_seq;

then replace your query:
select * from testidentity where id < -1

with

select test_seq.nextval from dual; to get next sequence number (identity_id)

jvv

ASKER

I have already a sequencer and a trigger.

In the mean time I found the answer myself.

The connection's cursorlocation should be adUseServer, and the recordset must be opened with the adOpenKeySet parameter.
Then it works fine!

Thanks anyway for replying.

ASKER CERTIFIED SOLUTION

DarthMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial