Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 19th, 2007, 10:12 AM
Friend of Wrox
 
Join Date: May 2005
Location: , , .
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default identity column id find!!

Hi,
in the past i used the following code to get the last recordset(identity column ) id of a field when it is inserted in the ACCESS db
but now when i use the same code for MS SQL it does not work?
please help

set Rs2=server.createobject("adodb.recordset")
Rs2.open "select * from Events ",conn,3,3
Rs2.addNew
Rs2("UserID")=intUserID
Rs2("Event_Name")=strEventName
Rs2.Update


Set RS2 = Conn.Execute( "SELECT @@IDENTITY" )
intNewRec = RS2(0)

Reply With Quote
  #2 (permalink)  
Old January 19th, 2007, 10:23 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Read this:
http://doc.ddart.net/mssql/sql70/globals_7.htm

The reason this is happening is because @@Identity is to be used in a single connection e.g. SELECT @@Identity needs to directly follow a SELECT INTO or INSERT statement.

Also consider using scope_identity() as @@Identitiy can cause some problems in being accurate.

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Reply With Quote
  #3 (permalink)  
Old January 19th, 2007, 10:44 AM
Friend of Wrox
 
Join Date: May 2005
Location: , , .
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

but I do not user insert into command!!!
i am using recordset add/update
can you write the code

Reply With Quote
  #4 (permalink)  
Old January 19th, 2007, 11:06 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

No I will not write you the code because I am not here to do your work for you, I will try to help you though. Since you are trying to do this inside of MS SQL I would suggest writing a stored procedures that would add your UserID and Event_Name to the database and then call @@Identity.

Here is why: if you call SELECT @@Identity, such as you are doing, it will return NULL for every record in the current table.

So your procedure would need to do this

INSERT INTO table(columns)values(values)
SELECT @@Identity

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Reply With Quote
  #5 (permalink)  
Old January 19th, 2007, 12:02 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The id field of your recordset should already contain the id after you have called the Update method on the recordset.
Is this not happening?

Woody Z
http://www.learntoprogramnow.com
Reply With Quote
  #6 (permalink)  
Old January 20th, 2007, 01:04 AM
Authorized User
 
Join Date: Jan 2007
Location: , , India.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to saurabhj Send a message via Yahoo to saurabhj
Default

You have to use Insert Into Statement...

-Saurabhj

Reply With Quote
  #7 (permalink)  
Old January 20th, 2007, 06:03 AM
Friend of Wrox
 
Join Date: May 2005
Location: , , .
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No woodyz it return null

Reply With Quote
  #8 (permalink)  
Old January 20th, 2007, 01:12 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You state that the record is getting created, correct?
If so, the recordset can contain the newly generated Id as long as several things are in place.
Is that your actual code you are showing us?
Is the Id field set as the Primary Key and Autonumber?
What is the provider you use for your connection?

I use this mechanism all the time for the very purpose you need.


Woody Z
http://www.learntoprogramnow.com
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Identity Column mike_remember SQL Server 2000 3 July 9th, 2007 08:12 PM
getting identity column from the table g_vamsi_krish SQL Server 2000 1 March 15th, 2006 05:05 PM
identity column rohit_sant SQL Language 4 June 6th, 2005 09:27 AM
Identity column jbenson001 SQL Server 2000 5 April 12th, 2005 03:01 PM



All times are GMT -4. The time now is 12:25 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.