Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADO recordset.Addnew and returning a sequence value

Posted on 2004-10-06
4
Medium Priority
?
974 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

610 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