Solved

ADO recordset.Addnew and returning a sequence value

Posted on 2004-10-06
4
960 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
Comment Utility
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
Comment Utility
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
Comment Utility
Submitted to PAQ with points refunded (250)

DarthMod
Community Support Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 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

12 Experts available now in Live!

Get 1:1 Help Now