Insert Retrieve SP

  • Thread starter Thread starter Axi
  • Start date Start date
A

Axi

Hi,

I made an insert using a Stored Procedure (MS SQL 2000).
How can I store the ID of the last inserted record into a session var ?
I have code to do this with a regular "Insert" page (Using Dreamweaver), but
didnt work for the SP.

This is part of the code I have for the insert for the ASP (Dreamweaver).

code ....
MM_editCmd.Execute();

<!--set up Auto Number retrieval for SQL Server -->
var rsNewAutoIncrement = MM_editCmd.ActiveConnection.Execute("select
@@identity")
Session("svReq_ID") = rsNewAutoIncrement(0).Value
<!--rsNewAutoIncrement.Close -->
<!--var rsNewAutoIncrement = Nothing -->
<!--end retrieval -->


This is part of the code that I am using with the Insert Stored Procedure:

AddInvoice.Parameters.Append AddInvoice.CreateParameter("@State", 200,
1,25,AddInvoice__State)
AddInvoice.Parameters.Append AddInvoice.CreateParameter("@Zip", 200,
1,25,AddInvoice__Zip)
AddInvoice.CommandType = 4
AddInvoice.CommandTimeout = 0
AddInvoice.Prepared = true
AddInvoice.Execute()

%>

I need to add code here to retreive the ID just insterted, but the above
code didnt work

Thanks in advance,

Axi
ASP VB / MS SQL 2000
 
Try something like the following:

SELECT SCOPE_IDENTITY() AS [InsertedId]

This assumes that the primary key column of the table you are inserting into
is an Identy column.

Read about @@IDENTITY and SCOPE_IDENTITY() in the Sql Server Books Online.
In general you almost always want to use SCOPE_IDENTITY instead of
@@IDENTITY.

- Greg
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your stored procedure (SP) needs to return the @@Identity variable.
Something like this:

Create Procedure usp_MyProcedure @input Int, @output Int OUTPUT
AS
.... your stuff ...

INSERT INTO .... < table w/ Identity column > ...

- -- Get the Identity value just created by the INSERT INTO
SET @output = @@IDENTITY

=====

Then you'll have to declare an Output variable in the ADO call &
assign a variable the result of the SP call. E.g. (p. 93 of _ADO 2.0
Programmer's Reference_, Wrox Publishers):

objCmd.Parameters.Append objCmd.CreateParameter("ReturnValue", _
adInteger, adParamReturnValue)

Set objRec = objCmd.Execute

intParamAvail = objConn.Properties("Output Parameter Availability")
If intParamAvail = 4 Then
' parameter not available until recordset is closed
objRec.Close
End If
intReturnValue = objCmd.Parameters("ReturnValue")

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDqOp4echKqOuFEgEQL7ywCgiXv1XPZF4dtqBByi2pC/HotJFKwAmwf0
BnPHY8YzazHt9Y36B9npfO3r
=tRet
-----END PGP SIGNATURE-----
 
Back
Top