SQL Server 2000 and @@Identity

2002-04-11 Thread Rick Walters 

Hi List,

I recently upgraded from SQLServer7 to 2k and the @@identity in the
code below now returns a null string.  Anyone know why this might be
happening?  

code block~~
!--- insert user info ---
cfquery name=insertPeople datasource=#dsn#
set nocount on
insert into people 
(password, username)
values
('something', 'testuser123456')
SELECT lastid = @@identity
set nocount off
/cfquery

!--- capture the peopleid from the previous query ---
cfset thepeopleid=#insertPeople.lastid#

!--- insert associated user detail ---
cfquery name=insertDetail datasource=#dsn#
insert into people_detail
(peopleid, firstname, lastname)
values
(#thepeopleid#, 'firstname', 'lastname')
/cfquery
end code block~~~

Thanks for the help.

Good Fortune,
Richard Walters,
Webmaster, Davita Laboratory Services
[EMAIL PROTECTED]
(800) 604-5227 x 3525

DaVita Inc.
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Server 2000 and @@Identity

2002-04-11 Thread Clint Tredway 

Try putting this into a stored proc and trying it.

I am using SQL 2000 and I have no problem doing this.

Clint

-Original Message-
From: Rick Walters [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 11, 2002 2:02 PM
To: CF-Talk
Subject: SQL Server 2000 and @@Identity


Hi List,

I recently upgraded from SQLServer7 to 2k and the @@identity in the
code below now returns a null string.  Anyone know why this might be
happening?

code block~~
!--- insert user info ---
cfquery name=insertPeople datasource=#dsn#
set nocount on
insert into people
(password, username)
values
('something', 'testuser123456')
SELECT lastid = @@identity
set nocount off
/cfquery

!--- capture the peopleid from the previous query ---
cfset thepeopleid=#insertPeople.lastid#

!--- insert associated user detail ---
cfquery name=insertDetail datasource=#dsn#
insert into people_detail
(peopleid, firstname, lastname)
values
(#thepeopleid#, 'firstname', 'lastname')
/cfquery
end code block~~~

Thanks for the help.

Good Fortune,
Richard Walters,
Webmaster, Davita Laboratory Services
[EMAIL PROTECTED]
(800) 604-5227 x 3525

DaVita Inc.

__
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Server 2000 and @@Identity

2002-04-11 Thread Tony_Petruzzi 

try upgrading your mdac. also why aren't you delcare lastid and making your
statement

SELECT @lastid = @@identity

Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org


-Original Message-
From: Rick Walters [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 11, 2002 3:02 PM
To: CF-Talk
Subject: SQL Server 2000 and @@Identity


Hi List,

I recently upgraded from SQLServer7 to 2k and the @@identity in the
code below now returns a null string.  Anyone know why this might be
happening?  

code block~~
!--- insert user info ---
cfquery name=insertPeople datasource=#dsn#
set nocount on
insert into people 
(password, username)
values
('something', 'testuser123456')
SELECT lastid = @@identity
set nocount off
/cfquery

!--- capture the peopleid from the previous query ---
cfset thepeopleid=#insertPeople.lastid#

!--- insert associated user detail ---
cfquery name=insertDetail datasource=#dsn#
insert into people_detail
(peopleid, firstname, lastname)
values
(#thepeopleid#, 'firstname', 'lastname')
/cfquery
end code block~~~

Thanks for the help.

Good Fortune,
Richard Walters,
Webmaster, Davita Laboratory Services
[EMAIL PROTECTED]
(800) 604-5227 x 3525

DaVita Inc.

__
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Server 2000 and @@Identity

2002-04-11 Thread Rick Walters 

Checking on the MDAC upgrade idea.. @lastid would assume lastid is a
declared variable, it's just part of my select statement.  I could have
named it anything.  Thanks for the MDAC idea.  It's a new server and I
forgot about that.

Good Fortune,
Richard Walters,
Webmaster, Davita Laboratory Services
[EMAIL PROTECTED]
(800) 604-5227 x 3525

 [EMAIL PROTECTED] 04/11/02 03:08PM 
try upgrading your mdac. also why aren't you delcare lastid and making
your
statement

SELECT @lastid = @@identity

Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED] 
http://www.sheriff.org 


-Original Message-
From: Rick Walters [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, April 11, 2002 3:02 PM
To: CF-Talk
Subject: SQL Server 2000 and @@Identity


Hi List,

I recently upgraded from SQLServer7 to 2k and the @@identity in the
code below now returns a null string.  Anyone know why this might be
happening?  

code block~~
!--- insert user info ---
cfquery name=insertPeople datasource=#dsn#
set nocount on
insert into people 
(password, username)
values
('something', 'testuser123456')
SELECT lastid = @@identity
set nocount off
/cfquery

!--- capture the peopleid from the previous query ---
cfset thepeopleid=#insertPeople.lastid#

!--- insert associated user detail ---
cfquery name=insertDetail datasource=#dsn#
insert into people_detail
(peopleid, firstname, lastname)
values
(#thepeopleid#, 'firstname', 'lastname')
/cfquery
end code block~~~

Thanks for the help.

Good Fortune,
Richard Walters,
Webmaster, Davita Laboratory Services
[EMAIL PROTECTED] 
(800) 604-5227 x 3525

DaVita Inc.


__
Your ad could be here. Monies from ads go to support these lists and provide more 
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Server 2000 and @@Identity

2002-04-11 Thread Dave Watts 

 also why aren't you declare lastid and making your statement
 
 SELECT lastid = identity

If he did that, he wouldn't get lastid back as a query column.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444

__
Structure your ColdFusion code with Fusebox. Get the official book at 
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Server 2000 and @@Identity

2002-04-11 Thread Chip Harlan 

I usually use

SET NOCOUNT ON
INSERT INTO people (password, username)
VALUES ('something', 'testuser123456')
SELECT @@identity AS lastID

works in SQL 2000 for me...

-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 11, 2002 3:36 PM
To: CF-Talk
Subject: RE: SQL Server 2000 and @@Identity


 also why aren't you declare lastid and making your statement
 
 SELECT @lastid = @@identity

If he did that, he wouldn't get lastid back as a query column.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444


__
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: SQL Server 2000 and @@Identity

2002-04-11 Thread Dave Watts 

   also why aren't you declare lastid and making your statement
  
   SELECT lastid = identity
 
  If he did that, he wouldn't get lastid back as a query column.
 
 I usually use
 
   SET NOCOUNT ON
   INSERT INTO people (password, username)
   VALUES ('something', 'testuser123456')
   SELECT identity AS lastID
   
 works in SQL 2000 for me...

Uh, yes, that's analogous to using

SELECT lastid = IDENTITY

The issue was that Tony had suggested storing the identity value in an SQL
variable, rather than returning it as a query column.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444

__
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists