*RESOLVED* Getting ID from SQL server

[cboffsite]

Hi All,

I'm trying to do some CGI and register people by issuing them a booking number (autonumber ID).

When I run the code locally it's fine.

When I rn it on a different PC , the one that actually holds the DB

It tells me : Identity cannot be determined for newly inserted rows.

My code is as following....


gb.Open "Provider=sqloledb;Data Source=" & servername & ";Initial Catalog=" & DatabaseName & ";User id=" & username & ";Password=" & password
visitor.Open "select * from demographics where id<=1", gb, adOpenKeyset, adLockOptimistic

With visitor
.AddNew
!Title = tle
!GivenName = giv
!Surname = sur
!Position = pos
!Organisation = org
!Address1 = add1
!Address2 = add2
!Address3 = add3
!Suburb = UCase(srb)
!State = UCase(ste)
!Pcode = UCase(pcde)
!Country = UCase(cty)
!Phone = ph
!Fax = fx
!Mobile = mb
!Email = eml
.Update

BID = !ID
!Comment = Cmt & !ID
.Update

End With

visitor.Close
gb.Close

Please help, as this is the only thing stopping me from launching my app.

Regards

Chris

[cboffsite]

I worked it out.

I have to close the database after taking the ID and reopen it to modify it.

[swatty]

Originally posted by cboffsite
I worked it out.

I have to close the database after taking the ID and reopen it to modify it.

No you don't but after the first update you want to edit the record therefore you have to put a .Edit before you fill up the comment field.

[cboffsite]

But you don't have .EDIT in ADO

Regards

Chris

[McManister]

This thread is insanely old, but I was having this issue the other day and was looking for answers. Just thought I'd share what I discovered. I was able to prevent this error by adding the following line of code after the first Update call:

rs.AbsolutePosition = rs.AbsolutePosition

Setting the AbsolutePosition to itself performs a cursor position change behind the scenes and somehow puts the RecordSet in a state where it can be updated again. I'm not sure what is happening underneath the hood here, but this was the most elegant solution I could find.