473,320 Members | 1,948 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ADO Addnew and identity columns

I have an application which is running fine with MS SqlServer, but it should
be working with Oracle as weel.
At a lot of places we rely upon the ADO Recordset to return incremented
identity columns.
Oralce however returns null or zero.
How can this be fixed 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

Jul 19 '05 #1
9 8060
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message news:<41***********************@news.xs4all.nl>...
I have an application which is running fine with MS SqlServer, but it should
be working with Oracle as weel.
At a lot of places we rely upon the ADO Recordset to return incremented
identity columns.
Oralce however returns null or zero.
How can this be fixed 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


It can not easily be fixed - identity is not a SQL standard. It is a
MS SQLServer peculiarity and you will need to 'port' to get this to
work right.

The closest you come to a quick solution is to use Oracle's
'sequences' which serve a similar purpose (handing out unique numbers
serially) but are implemented quite differently. A sequence is
accessed as part of a SQL statement, generally as part of the select
list, using either CURR_VAL or NEXT_VAL 'methods'.

Decent discussion around this is available in Thomas Kyte's "Expert
One on One Oracle" book.

HTH
/Hans

BTW: newsgroup comp.databases.oracle is officially defunct and fewer
ISPs are carrying it. The replacement is the heirarchy
'comp.databases.oracle.*' as discussed at http://orafaq.com
Jul 19 '05 #2

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:<41***********************@news.xs4all.nl>...
I have an application which is running fine with MS SqlServer, but it
should
be working with Oracle as weel.
At a lot of places we rely upon the ADO Recordset to return incremented
identity columns.
Oralce however returns null or zero.
How can this be fixed 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


It can not easily be fixed - identity is not a SQL standard. It is a
MS SQLServer peculiarity and you will need to 'port' to get this to
work right.

The closest you come to a quick solution is to use Oracle's
'sequences' which serve a similar purpose (handing out unique numbers
serially) but are implemented quite differently. A sequence is
accessed as part of a SQL statement, generally as part of the select
list, using either CURR_VAL or NEXT_VAL 'methods'.

I already have implemented sequences and triggers in the database.
I have seen this working because I looked into the database right after the
Addnew function.
Problem is still that it is not returned in the recordset.
Jul 19 '05 #3

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:<41***********************@news.xs4all.nl>...
I have an application which is running fine with MS SqlServer, but it
should
be working with Oracle as weel.
At a lot of places we rely upon the ADO Recordset to return incremented
identity columns.
Oralce however returns null or zero.
How can this be fixed 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
It can not easily be fixed - identity is not a SQL standard. It is a
MS SQLServer peculiarity and you will need to 'port' to get this to
work right.

The closest you come to a quick solution is to use Oracle's
'sequences' which serve a similar purpose (handing out unique numbers
serially) but are implemented quite differently. A sequence is
accessed as part of a SQL statement, generally as part of the select
list, using either CURR_VAL or NEXT_VAL 'methods'.

I already have implemented sequences and triggers in the database.
I have seen this working because I looked into the database right after

the Addnew function.
Problem is still that it is not returned in the recordset.

Use the returning clause to get the values back out.
Jim
Jul 19 '05 #4
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message news:<41***********************@news.xs4all.nl>...
"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:<41***********************@news.xs4all.nl>...
I have an application which is running fine with MS SqlServer, but it
should
be working with Oracle as weel.
At a lot of places we rely upon the ADO Recordset to return incremented
identity columns.
Oralce however returns null or zero.
How can this be fixed 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


It can not easily be fixed - identity is not a SQL standard. It is a
MS SQLServer peculiarity and you will need to 'port' to get this to
work right.

The closest you come to a quick solution is to use Oracle's
'sequences' which serve a similar purpose (handing out unique numbers
serially) but are implemented quite differently. A sequence is
accessed as part of a SQL statement, generally as part of the select
list, using either CURR_VAL or NEXT_VAL 'methods'.

I already have implemented sequences and triggers in the database.
I have seen this working because I looked into the database right after the
Addnew function.
Problem is still that it is not returned in the recordset.


1) This should be discussed in comp.databases.oracle.server, not
comp.database.oracle (AFAIK, no charter, not an official group)
comp.databases.oracle (defunct - see http://orafaq.com)

I have added cdo.server and hopefully we will get more people looking
at this. In your reply PLEASE remove the two bad groups from the
distribution.

2) I'm not sure I understand the problem. You say you have created
sequences and triggers - how are you using them? Suggest you post the
trigger code.

/Hans
Jul 19 '05 #5

"Jim Kennedy" <ke****************************@attbi.net> wrote in message
news:JEb9d.216386$3l3.87609@attbi_s03...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
> "Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
> news:<41***********************@news.xs4all.nl>...
>> I have an application which is running fine with MS SqlServer, but it
>> should
>> be working with Oracle as weel.
>> At a lot of places we rely upon the ADO Recordset to return
>> incremented
>> identity columns.
>> Oralce however returns null or zero.
>> How can this be fixed 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
>
> It can not easily be fixed - identity is not a SQL standard. It is a
> MS SQLServer peculiarity and you will need to 'port' to get this to
> work right.
>
> The closest you come to a quick solution is to use Oracle's
> 'sequences' which serve a similar purpose (handing out unique numbers
> serially) but are implemented quite differently. A sequence is
> accessed as part of a SQL statement, generally as part of the select
> list, using either CURR_VAL or NEXT_VAL 'methods'.
>

I already have implemented sequences and triggers in the database.
I have seen this working because I looked into the database right after

the
Addnew function.
Problem is still that it is not returned in the recordset.

Use the returning clause to get the values back out.
Jim

The returning clause is used with the INSERT statement. I use the ADO Addnew
function.
Jul 19 '05 #6
Hans,

The problem is NOT the trigger.
The insert trigger does properly increment the sequence and puts the value
in the desired column.
That's exactly what I want to be happen.

The problem is: the ADO Recordset does not immediately return the new value
after the Addnew method.
The same source runs perfectly with SqlServer, because then the Addnew
really return the new identity value (as it is claad in MSSQL)
I've seen a few postings about this issue in several forums, but no one
seems te have found a solution....:-(

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message

news:<41***********************@news.xs4all.nl>...
"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:<41***********************@news.xs4all.nl>...
> I have an application which is running fine with MS SqlServer, but it
> should
> be working with Oracle as weel.
> At a lot of places we rely upon the ADO Recordset to return incremented> identity columns.
> Oralce however returns null or zero.
> How can this be fixed 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

It can not easily be fixed - identity is not a SQL standard. It is a
MS SQLServer peculiarity and you will need to 'port' to get this to
work right.

The closest you come to a quick solution is to use Oracle's
'sequences' which serve a similar purpose (handing out unique numbers
serially) but are implemented quite differently. A sequence is
accessed as part of a SQL statement, generally as part of the select
list, using either CURR_VAL or NEXT_VAL 'methods'.

I already have implemented sequences and triggers in the database.
I have seen this working because I looked into the database right after the Addnew function.
Problem is still that it is not returned in the recordset.


1) This should be discussed in comp.databases.oracle.server, not
comp.database.oracle (AFAIK, no charter, not an official group)
comp.databases.oracle (defunct - see http://orafaq.com)

I have added cdo.server and hopefully we will get more people looking
at this. In your reply PLEASE remove the two bad groups from the
distribution.

2) I'm not sure I understand the problem. You say you have created
sequences and triggers - how are you using them? Suggest you post the
trigger code.

/Hans

Jul 19 '05 #7

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...
Hans,

The problem is NOT the trigger.
The insert trigger does properly increment the sequence and puts the value
in the desired column.
That's exactly what I want to be happen.

The problem is: the ADO Recordset does not immediately return the new value after the Addnew method.
The same source runs perfectly with SqlServer, because then the Addnew
really return the new identity value (as it is claad in MSSQL)
I've seen a few postings about this issue in several forums, but no one
seems te have found a solution....:-(

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message news:<41***********************@news.xs4all.nl>...
"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
> "Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
> news:<41***********************@news.xs4all.nl>...
>> I have an application which is running fine with MS SqlServer, but it >> should
>> be working with Oracle as weel.
>> At a lot of places we rely upon the ADO Recordset to return incremented >> identity columns.
>> Oralce however returns null or zero.
>> How can this be fixed 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
>
> It can not easily be fixed - identity is not a SQL standard. It is a > MS SQLServer peculiarity and you will need to 'port' to get this to
> work right.
>
> The closest you come to a quick solution is to use Oracle's
> 'sequences' which serve a similar purpose (handing out unique numbers > serially) but are implemented quite differently. A sequence is
> accessed as part of a SQL statement, generally as part of the select
> list, using either CURR_VAL or NEXT_VAL 'methods'.
>
I already have implemented sequences and triggers in the database.
I have seen this working because I looked into the database right
after the Addnew function.
Problem is still that it is not returned in the recordset.


1) This should be discussed in comp.databases.oracle.server, not
comp.database.oracle (AFAIK, no charter, not an official group)
comp.databases.oracle (defunct - see http://orafaq.com)

I have added cdo.server and hopefully we will get more people looking
at this. In your reply PLEASE remove the two bad groups from the
distribution.

2) I'm not sure I understand the problem. You say you have created
sequences and triggers - how are you using them? Suggest you post the
trigger code.

/Hans


What about the returning clause?
Jim
Jul 19 '05 #8

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...

"Jim Kennedy" <ke****************************@attbi.net> wrote in message
news:JEb9d.216386$3l3.87609@attbi_s03...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
> "Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
> news:<41***********************@news.xs4all.nl>...
>> I have an application which is running fine with MS SqlServer, but it >> should
>> be working with Oracle as weel.
>> At a lot of places we rely upon the ADO Recordset to return
>> incremented
>> identity columns.
>> Oralce however returns null or zero.
>> How can this be fixed 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
>
> It can not easily be fixed - identity is not a SQL standard. It is a
> MS SQLServer peculiarity and you will need to 'port' to get this to
> work right.
>
> The closest you come to a quick solution is to use Oracle's
> 'sequences' which serve a similar purpose (handing out unique numbers
> serially) but are implemented quite differently. A sequence is
> accessed as part of a SQL statement, generally as part of the select
> list, using either CURR_VAL or NEXT_VAL 'methods'.
>
I already have implemented sequences and triggers in the database.
I have seen this working because I looked into the database right after the
Addnew function.
Problem is still that it is not returned in the recordset.

Use the returning clause to get the values back out.
Jim

The returning clause is used with the INSERT statement. I use the ADO

Addnew function.

Add new is an insert statement; it is just a proprietary API to do an
insert.
Jim
Jul 19 '05 #9
Jim,

You're still recommending the 'returning clause'. I do not know how to use
that in an Addnew method.
There's no place to specify the insert-statement that is built-in into that
method.
It's not a .Net project, but VB6. (The ADO.Net data provider allows you to
customize all sql command behind the insert, update and delete actions)

However, finally I found something which points me in the right direction:
http://www.tju.cn/docs/odb10.1.0.2/w...0115/using.htm

Scroll to the paragraph about the 'Server Data on Insert Property'. I think
that's exactly what I need.
Unfortunetaly the machine with my oracle db crashed. I am going to reinstall
everything tomorrow.
In the mean time I am going to look for code examples using this property.

Jan
"Jim Kennedy" <ke****************************@attbi.net> wrote in message
news:I00ad.219970$D%.30457@attbi_s51...

"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
news:41***********************@news.xs4all.nl...
Hans,

The problem is NOT the trigger.
The insert trigger does properly increment the sequence and puts the value
in the desired column.
That's exactly what I want to be happen.

The problem is: the ADO Recordset does not immediately return the new value
after the Addnew method.
The same source runs perfectly with SqlServer, because then the Addnew
really return the new identity value (as it is claad in MSSQL)
I've seen a few postings about this issue in several forums, but no one
seems te have found a solution....:-(

"Hans" <fo******@gmail.com> wrote in message
news:bd**************************@posting.google.c om...
"Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message

news:<41***********************@news.xs4all.nl>...
> "Hans" <fo******@gmail.com> wrote in message
> news:bd**************************@posting.google.c om...
> > "Jan van Veldhuizen" <ja*@van-veldhuizen.nl> wrote in message
> > news:<41***********************@news.xs4all.nl>...
> >> I have an application which is running fine with MS SqlServer, but it > >> should
> >> be working with Oracle as weel.
> >> At a lot of places we rely upon the ADO Recordset to return incremented
> >> identity columns.
> >> Oralce however returns null or zero.
> >> How can this be fixed 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
> >
> > It can not easily be fixed - identity is not a SQL standard. It
is a > > MS SQLServer peculiarity and you will need to 'port' to get this
to > > work right.
> >
> > The closest you come to a quick solution is to use Oracle's
> > 'sequences' which serve a similar purpose (handing out unique

numbers > > serially) but are implemented quite differently. A sequence is
> > accessed as part of a SQL statement, generally as part of the select > > list, using either CURR_VAL or NEXT_VAL 'methods'.
> >
> I already have implemented sequences and triggers in the database.
> I have seen this working because I looked into the database right

after
the
> Addnew function.
> Problem is still that it is not returned in the recordset.

1) This should be discussed in comp.databases.oracle.server, not
comp.database.oracle (AFAIK, no charter, not an official group)
comp.databases.oracle (defunct - see http://orafaq.com)

I have added cdo.server and hopefully we will get more people looking
at this. In your reply PLEASE remove the two bad groups from the
distribution.

2) I'm not sure I understand the problem. You say you have created
sequences and triggers - how are you using them? Suggest you post the
trigger code.

/Hans


What about the returning clause?
Jim

Jul 19 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Roger Withnell | last post by:
I'm inserting a new record into an MS SQL database table and I want to obtain the new records autonumber immediately afterwards, as follows: MadminRS.CursorLocation = adUseServer...
2
by: baskar | last post by:
Hi, I have a table with 90 fields. When I try to use the recordset with addnew and set all the 90 fields and then update the record set it fails the following error seen in the log, SQL0117N...
25
by: dixie | last post by:
I have some code that adds new records into a table for each ID in a list box when a button on a form is clicked. This works fine. My problem now is that I wish to be able to edit all the records...
17
by: Trevor Best | last post by:
I don't know if this has been reported before but it appears to be a bug with Access. If I create two tables both with an identity column then create an insert trigger on table1 that inserts a...
2
by: Omavlana Omav | last post by:
Hi, I have created a datatable, dataadapter, added the table to a dataset and filling the table. Here I want to add an identity column to the above datatable and Later I will use this...
1
by: Leonardo | last post by:
Hi. I'm trying to build my first application with database access using VB 2005. I'm a VB 6 programmer and learning everything again has been challenging. I managed to write a code using some tips...
41
by: pb648174 | last post by:
In a multi-user environment, I would like to get a list of Ids generated, similar to: declare @LastId int select @LastId = Max(Id) From TableMania INSERT INTO TableMania (ColumnA, ColumnB)...
9
by: Jan van Veldhuizen | last post by:
I have an application which is running fine with MS SqlServer, but it should be working with Oracle as weel. At a lot of places we rely upon the ADO Recordset to return incremented identity...
11
by: stegze | last post by:
Hi All, I have a problem with a DB2 server of my customer. It is a Debian Linux running DB2 Express-C. I have an IDENTITY field as PK in a table and I use this value as FK in another table. Two...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.