Adding Records to a DataBase

[John G Duffy]

I am trying to add recored to a Access DataBase using ADO and am having trouble trying to get the Record ID of a newly created record.
My Field in the Table is named "ID" with autoincrement YES. Here is the simple code to create a new record which works.
Mainline routine calls OPenDB then AddnewRCD. the last statement in addnewRCD is supposed to return the unique "ID" field geenerated by the .Addnew but always returns a 2.
What stupid thing am I doing wrong?

' OPen the database
public Function OpenDB(DBName as string)
on error GoTo openerror

set DB = new Connection
set rs = new Recordset
DB.CursorLocation = adUseClient
DB.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName & ";"
rs.Open "select * from DBRecords Order by ID", DB, adOpenStatic, adLockOptimistic
mvarDBName = DBName ' save for others to sue
mvarDBOpened = true ' Indicate DB opened
OpenDB = 1 ' no errors detected
Exit Function
'
'
Add a new record
public Function AddNewRCD()
rs.AddNew ' Add a new record to the dataBase
' set some default values in the Fields
rs.Fields("Left") = 1000
rs.Fields("Top") = 2000
rs.Fields("Width") = 3000
rs.Fields("Height") = 4000
rs.Fields("Text") = ""

rs.Update ' Update the record
rs.Requery ' reload to get record ID
AddNewRCD = rs.Fields("ID") ' Return Record number
End Function




John G

[enigmaos]

try rs.Edit. Let me know.


rs.AddNew ' Add a new record to the dataBase
' set some default values in the Fields
rs.Fields("Left") = 1000
rs.Fields("Top") = 2000
rs.Fields("Width") = 3000
rs.Fields("Height") = 4000
rs.Fields("Text") = ""
rs.Update
'########
rs.Edit
'########
AddNewRCD = rs.Fields("ID")




enigmaos@yahoo.com

[enigmaos]

Add rs.Close after AddNewRCD just to make sure the recordset is closed.


AddNewRCD = rs.Fields("ID")
rs.Close




enigmaos@yahoo.com

[Andrew_Fryer]

Hello,

You could try using

rs.MoveLast

before trying to get the ID - if the record is new then it should be at the end of the recordset.

I think requery may lose your position in the recordset.

In addition, your recordset is set to adOpenStatic - I seem to remember that if it is opened this way then any data added is not reflected when changes are made. You could try setting it to dynamic.

Andrew

[John G Duffy]

Thanks to all of you that replied. None of the suggestions worked but it pointed me to the right solution.
rs.Edit does not work at all (there is no .Edit method or property. Just .EditMode)
.MoveLast will work most of the time, but if two users update records simultameously, then errors could occur.
rs.Close will terminate all references without me knowing what the record ID is.
Those suggestions, howwever, got me thinking about using the MSDN Help facility and came up with this solution.
'
'
The solution I found to work is this. Using .BookMark to save the records position then restoring it as the current record gives me the ID field I am looking for.

rs.AddNew
' some code here
rs.Update ' Update the record
lret = rs.Bookmark
rs.Requery ' reload to get record ID
rs.Bookmark = lret
AddNewRCD = rs.Fields("ID") ' Return Record number



'
Thanks all


John G

[Cimperiali]

Nice solution your. However, it will work only on databases which support Bookmarks. When I had same matter on Oracle, I adopted another solution:
Using a query to retrieve last Id (the greater) just before inserting record which will have the retrieved +1 value. Both query (select and insert) are incapsulated in a Begin trans/end trans.


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[John G Duffy]

Cesare,
THanks for the comments. I was aware that the DB required Bookmark support but it doesn't effect me since it works. I do not concern myself with that sort of thing. When I run into a problem, I just keep trying things until something works. Probably not the best way to do things but it's hard to teach an old dog new tricks.
Understand what you said about Retriveing the last ID, but I do not know how to do that. Also Begin/End Transaction is vague stuff to me. Would not this method be subject to someone else sneeking in after .Addnew and before the request for last ID?
Remote as is sounds but I guess, with proper locking, it could be done - or something like that.

John G

[jn8230]

If you use a server side (adUseServer) cursor, then you should be able to retrieve the AutoIncrement field in an Access 97 or 2000... you will need to use a dynamic or keyset cursor though.

[Cimperiali]

About concern on addnew, you are right: the trick was that with Oracle you can take advantage of sequences (read: a kind of trigger) that will provide you next "autonumber" value and lock it for you untill you make it be used or you "undo" your changes (that is: you retrieve nextval, you insert new record, if something goes wrong, you will do a "Rollback" on transaction and that Id is freed for soeone else).

If you really do not know about this (but I can hardly believe this) have a look at "Transaction" or "Commit" or "Rollback" in MSDN.

About "Old": I still think of you as a 22 genious...

;-)

Do your best to have a nice day, you King of Seniores.

Cesare Imperiali

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater