Solved

ADO recordset.Addnew and returning a sequence value

Posted on 2004-10-06
4
971 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

732 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