Pages

Advertisement

Sunday, August 5, 2007

SQL Server - Insert Record And Get New ID

Here its a sample program to insert records through stored procedure ..

' Active Server Pages code. <% 

Dim msADOConStr
Dim moADOCon
Dim moADOCom
Dim msFName
Dim msLName
Dim msNewID

msADOConStr = "your connection string goes here"
msFName = "George W."
msLName = "Bush"

Set moADOCon = Server.CreateObject("ADODB.Connection")
Set moADOCom = Server.CreateObject("ADODB.Command")

moADOCon.CursorLocation = adUseClient
moADOCon.open msADOConStr

with moADOCom
.activeconnection = moADOCon
.commandtext = "sp_SaveRecord"
.commandtype = adCmdStoredProc
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
.Parameters.Append .CreateParameter("@FName", adVarWChar, adParamInput, 40, msFName)
.Parameters.Append .CreateParameter("@LName", adVarWChar, adParamInput, 40, msLName)
.Parameters.Append .CreateParameter("@new_id", adInteger, adParamOutput, 0)
end with

moADOCom.Execute , , adExecuteNoRecords

if err.number = 0 then
Select Case trim(moADOCom("@RETURN_VALUE"))
Case "0": msNewID = trim(moADOCom("@new_id"))
Case Else: ' received an error
End Select
else
response.write err.description
end if

On Error Resume Next

If moADOCon.STATE = adStateOpen Then moADOCon.Close

Set moADOCom = nothing
Set moADOCon = Nothing

%>

' SQL Server stored procedure code starts here.

CREATE PROCEDURE sp_SaveRecord
(
@FName varchar(40),
@LName varchar(40),
@new_id int OUTPUT
)
AS

DECLARE @rc int

select @rc = 0

BEGIN TRANSACTION SaveRecord

insert into mytable (fname,lname) values (@FName,@LName)

if (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION SaveRecord
select @rc = @@Error
Goto OnExit
END

select @new_id = @@identity

COMMIT TRANSACTION SaveRecord

OnExit:
RETURN @rc

GO

No comments: