Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 Is there a simpler way ?
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gremlin
General Help Moderator

New Zealand
7528 Posts

Posted - 12 April 2001 :  19:00:53  Show Profile  Visit Gremlin's Homepage
Hi, Just after a quick answer on something if someone can help please.

I have a recordset with an identity column called RECORD_ID etc ...

On one page I update the recordset with a new record, but then i need to
retrieve the value of RECORD_ID thats been automatically assigned by SQL7 to use as an
index for a write to another database, which is where I'm having some
problems.

I've written some pretty nasty code to get it back i.e

raiddetails.requery()
raid_id1 = (raiddetails.fields.item("record_id").value)
raiddetails.MoveNext
While (Not raiddetails.EOF)
raid_id= (raiddetails.fields.item("record_id").value)
If raid_id > raid_id1 Then
Raid_id1 = raid_id
End If
raiddetails.MoveNext
Wend
raid_id = raid_id1

This code works based on the assumption that the record just allocated will
have the highest number. Of course this wont work too well with multiple
people potentially updating the database simultaneously.

Is there a simpler way to do what I want ??

Any help would be appreciated

Thanks
Brangwyn


HuwR
Forum Admin

United Kingdom
20595 Posts

Posted - 12 April 2001 :  20:16:18  Show Profile  Visit HuwR's Homepage
if you are using SQL (and possibly Jet4) you can do the following

SELECT @@IDENTITY AS LASTID

Also, another way is to always have a field which references the user doing the insert, then you could do SELECT MAX(RECORD_ID) FROM TABLE WHERE USER_ID = CURRENTUSER

Go to Top of Page

Doug G
Support Moderator

USA
6493 Posts

Posted - 12 April 2001 :  21:05:29  Show Profile
If you're using SQL Server you need to use NOCOUNT too.

set rs1 = con1.Execute("SET NOCOUNT ON; SELECT @@identity as autoID; SET NOCOUNT OFF;")
identity = rs1("autoID")


======
Doug G
======
Go to Top of Page

Gremlin
General Help Moderator

New Zealand
7528 Posts

Posted - 13 April 2001 :  00:45:17  Show Profile  Visit Gremlin's Homepage
Thank you both :) I knew there had to be a more logical way of doing this.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 1.09 seconds. Powered By: Snitz Forums 2000 Version 3.4.07