|
 |
asp_web_howto thread: SQL
Message #1 by "O'Hara, Elliott M" <EMOHARA@k...> on Wed, 21 Nov 2001 12:16:31 -0500
|
|
OK, I know this is simple, but I'm dumb...
So...
I have a insert statement,now I want a Identity value generated by the
insert for another insert statement.
I'd Like this to all happen with one stored procedure...
Alex, can we make it simple ???
;o)
Message #2 by Sam Clohesy <sam@e...> on Wed, 21 Nov 2001 17:30:04 -0000
|
|
This is'nt stored proc but I normally use something like this:
strSqlMax = "SELECT MAX(memberID) FROM tblMembers"
objRs.Open strSqlMax, objConn
if isnull(objRs(0)) then id_seq = 1
else
id_seq = objRs(0)+1
end if
You can then insert id_seq into various other tables
Alternatively use @@IDENTITY
Sam Clohesy
Project Manager
Tel: 0208 772 3958
E: samc@e...
W: http://www.etypemedia.co.uk
-----Original Message-----
From: O'Hara, Elliott M [mailto:EMOHARA@k...]
Sent: 21 November 2001 17:17
To: ASP Web HowTo
Subject: [asp_web_howto] SQL
OK, I know this is simple, but I'm dumb...
So...
I have a insert statement,now I want a Identity value generated by the
insert for another insert statement.
I'd Like this to all happen with one stored procedure...
Alex, can we make it simple ???
;o)
$subst('Email.Unsub')
Message #3 by "Mike Scott" <jstmehr4u3@h...> on Wed, 21 Nov 2001 09:20:59 -0800
|
|
Using the @@Identity is the easiest way.
Insert into table(columns)value(values)
select @IDVariable = @@Identity
return
----- Original Message -----
From: "Sam Clohesy" <sam@e...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Wednesday, November 21, 2001 9:30 AM
Subject: [asp_web_howto] RE: SQL
> This is'nt stored proc but I normally use something like this:
>
> strSqlMax = "SELECT MAX(memberID) FROM tblMembers"
>
> objRs.Open strSqlMax, objConn
>
> if isnull(objRs(0)) then id_seq = 1
> else
> id_seq = objRs(0)+1
> end if
>
>
> You can then insert id_seq into various other tables
>
> Alternatively use @@IDENTITY
>
> Sam Clohesy
> Project Manager
> Tel: 0208 772 3958
> E: samc@e...
> W: http://www.etypemedia.co.uk
>
>
> -----Original Message-----
> From: O'Hara, Elliott M [mailto:EMOHARA@k...]
> Sent: 21 November 2001 17:17
> To: ASP Web HowTo
> Subject: [asp_web_howto] SQL
>
>
> OK, I know this is simple, but I'm dumb...
> So...
> I have a insert statement,now I want a Identity value generated by the
> insert for another insert statement.
> I'd Like this to all happen with one stored procedure...
>
> Alex, can we make it simple ???
> ;o)
>
> $subst('Email.Unsub')
>
$subst('Email.Unsub')
>
Message #4 by "O'Hara, Elliott M" <EMOHARA@k...> on Wed, 21 Nov 2001 12:34:15 -0500
|
|
Thanks mike...
Worked like a charm, and I learned something...
If anyone is interested (probably not, but I'm sending it anyway)
heres the stored proc that works
CREATE PROCEDURE spAddTicket
@fkProduct int,@fkOS int,@charBIOSName varchar(50),
@charIP varchar(50),
@fkUser int,@dtDateOpened datetime,
@fkPriority int,@fkSubLocation int,@charRoomNum varchar(50),
@txtProblem text,@txtJustification text
AS
DECLARE @fkTicket int
/* make the ticket*/
INSERT INTO tblTickets
(fkUser,fkStatus)
VALUES
(@fkUser,1)
/*Get the ID generated*/
SELECT @fkTicket=@@IDENTITY
/*Enter the info*/
INSERT INTO tblInfo
(fkTicket,charBIOSName,charIP,fkSubLocation,charRoomNum,
fkPriority,fkOS,fkProduct,txtJustification)
VALUES
(@fkTicket,@charBIOSName,@charIP,@fkSubLocation,@charRoomNum,
@fkPriority,@fkOS,@fkProduct,@txtJustification)
GO
-----Original Message-----
From: Mike Scott [mailto:jstmehr4u3@h...]
Sent: Wednesday, November 21, 2001 12:21 PM
To: ASP Web HowTo
Subject: [asp_web_howto] RE: SQL
Using the @@Identity is the easiest way.
Insert into table(columns)value(values)
select @IDVariable = @@Identity
return
----- Original Message -----
From: "Sam Clohesy" <sam@e...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Wednesday, November 21, 2001 9:30 AM
Subject: [asp_web_howto] RE: SQL
> This is'nt stored proc but I normally use something like this:
>
> strSqlMax = "SELECT MAX(memberID) FROM tblMembers"
>
> objRs.Open strSqlMax, objConn
>
> if isnull(objRs(0)) then id_seq = 1
> else
> id_seq = objRs(0)+1
> end if
>
>
> You can then insert id_seq into various other tables
>
> Alternatively use @@IDENTITY
>
> Sam Clohesy
> Project Manager
> Tel: 0208 772 3958
> E: samc@e...
> W: http://www.etypemedia.co.uk
>
>
> -----Original Message-----
> From: O'Hara, Elliott M [mailto:EMOHARA@k...]
> Sent: 21 November 2001 17:17
> To: ASP Web HowTo
> Subject: [asp_web_howto] SQL
>
>
> OK, I know this is simple, but I'm dumb...
> So...
> I have a insert statement,now I want a Identity value generated by the
> insert for another insert statement.
> I'd Like this to all happen with one stored procedure...
>
> Alex, can we make it simple ???
> ;o)
>
> $subst('Email.Unsub')
>
$subst('Email.Unsub')
>
emohara@k...
$subst('Email.Unsub')
Message #5 by "Ken Schaefer" <ken@a...> on Thu, 22 Nov 2001 22:39:32 +1100
|
|
That's not a good way to do it.
Person1 inserts record
Person2 inserts record
Person1 does SELECT MAX()
Person1 now has the ID value of Persons2's newly inserted record.
Use @@Identity - which returns the last created ID on a connection by
connection basis.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Sam Clohesy" <sam@e...>
Subject: [asp_web_howto] RE: SQL
: This is'nt stored proc but I normally use something like this:
:
: strSqlMax = "SELECT MAX(memberID) FROM tblMembers"
:
: objRs.Open strSqlMax, objConn
:
: if isnull(objRs(0)) then id_seq = 1
: else
: id_seq = objRs(0)+1
: end if
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
View Cart
