*RESOLVED* Getting ID from SQL server

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

I worked it out.

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

Quote:

---

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.

But you don't have .EDIT in ADO

Regards

Chris

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.