Re: ADO Addnew and identity columns

"Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message news:4166609a$0$37789$e4fe514c_at_news.xs4all.nl...
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
> news:JEb9d.216386$3l3.87609_at_attbi_s03...
> >
> > "Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message
> > news:4164f533$0$78753$e4fe514c_at_news.xs4all.nl...
> >>
> >> "Hans" <forbrich_at_gmail.com> wrote in message
> >> news:bd0e88c6.0410061202.250cb86b_at_posting.google.com...
> >> > "Jan van Veldhuizen" <jan_at_van-veldhuizen.nl> wrote in message
> >> > news:<4163d817$0$78279$e4fe514c_at_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 Received on Sun Oct 10 2004 - 03:15:34 CEST