SQL Server - Insert Record And Get New ID

Share |

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