Solved

ADO recordset.Addnew and returning a sequence value

Posted on 2004-10-06
4
966 Views
Last Modified: 2010-08-05
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

0
Comment
Question by:jvv
4 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 12244925
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)
0
 

Author Comment

by:jvv
ID: 12289917
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.
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 14108347
Submitted to PAQ with points refunded (250)

DarthMod
Community Support Moderator
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle report printing 2 pages in one page 2 67
Oracle RAC 12c 8 71
Oracle and DateTime math 6 37
Oracle 10g standard edition server with 4 processors 3 52
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now