What I do is use a stored procedure to do the insert and then pass back if
identity column
EX STORED PROC:
CREATE PROCEDURE insert_user
@username varchar(20),
@password varchar(20),
@IDcol int = NULL
AS
INSERT INTO u_users
(u_users.username, u_users.password)
VALUES (@username,@password)
SELECT @IDcol = @@IDENTITY
RETURN @IDcol
CF CODE:
<cfstoredproc procedure="insert_user" datasource="#application.dsn#"
returncode="Yes">
<CFPROCRESULT NAME="insert_user">
<CFPROCPARAM type="IN" cfsqltype="CF_SQL_VARCHAR" dbvarname="username"
value="#variables.username#">
<CFPROCPARAM type="IN" cfsqltype="CF_SQL_VARCHAR" dbvarname="password"
value="#variables.password#">
</CFSTOREDPROC>
<cfoutput> #cfstoredproc.statuscode# </cfoutput> ** Status code will be
the returned identity column number
-----Original Message-----
From: Steve Weiner [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 06, 2000 1:03 PM
To: '[EMAIL PROTECTED]'
Subject: Retrieving Unique ID in from sql table.
Can anyone help me here, I'm really stuck.
I come from a Microsoft Access background and now they've thrown me into SQL
with no training. What I am trying to do is the following:
<cftransaction>
<cfquery Insert> - In access I have an autonumber column that would get
updated here automatically.
<cfquery select> - Here I would go through and selected max(id) as
lastrecordadded
</cftransaction>
Now I could use this record to write to some other tables and keep a
relationship.
But with SQL, it seems that there is no autonumber so when I do my first
insert, I have a field with type "uniqueid" and when I write to that table I
pass NewId().
Now how do I retrieve that information? Max() does not want to work in my
sql routine.
I'm really stuck, and this should seem pretty easy to someone who knows SQL.
Thanks,
[EMAIL PROTECTED]
----------------------------------------------------------------------------
--
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
