Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

SCOPE_IDENTITY with procedire VB.NET

I have a stored procedure in the database that works fine.  It gives me the output and I can use it in other stored procedures.  However, when I get to the VB.NET code the scope_identity() is not returned?

No idea as to what I am doing wrong but hope you folks can help me out!  :)

Thanks in advance,

B
VB.NET Code:

        Protected Overridable Sub SPROC_INSPerson(ByVal ...)
            Try
                Dim ds As DataSet = New DataSet()
                Dim dt As DataTable = New DataTable()
                Dim db As Database = ...
                Dim sql As String = "SPROC..."
                Dim cmd As DbCommand = db.GetStoredProcCommand(sql)

                db.AddInParameter(cmd, "i_Prefix", DbType.String, i_Prefix)
                db.AddInParameter(cmd, "i_FirstName", DbType.String, i_FirstName)
                db.AddInParameter(cmd, "i_MiddleName", DbType.String, i_MiddleName)
                db.AddInParameter(cmd, "i_LastName", DbType.String, i_LastName)
                db.AddInParameter(cmd, "i_Suffix", DbType.String, i_Suffix)
                db.AddInParameter(cmd, "i_NickName", DbType.String, i_NickName)
                db.AddInParameter(cmd, "i_Alpha", DbType.DateTime, i_Alpha)
                db.AddInParameter(cmd, "i_Delta", DbType.Int64, i_Delta)
                db.AddInParameter(cmd, "i_DeltaCo", DbType.Int64, i_DeltaCo)
                db.AddOutParameter(cmd, "o_PersonID", DbType.Int64, 8)

                'Dim returnValue As Integer = db.ExecuteScalar(cmd)

                'o_PersonID = CType(db.GetParameterValue(cmd, "o_PersonID"), Int64)

                o_PersonID = cmd.Parameters("@o_PersonID").Value

            Catch ex As Exception
                Throw ex
            End Try

        End Sub

Procedure:

ALTER PROCEDURE [dbo].[SPROC_INSPerson]
      @i_Prefix					nvarchar(20)
    , @i_FirstName				nvarchar(40)
	, @i_MiddleName				nvarchar(40)
	, @i_LastName				nvarchar(40)
	, @i_Suffix					nvarchar(20)
	, @i_NickName				nvarchar(40)
    , @i_Alpha                  smalldatetime
	, @i_Delta					bigint
    , @i_DeltaCo                bigint
	, @o_PersonID				bigint					OUTPUT 
	
AS

BEGIN

SET NOCOUNT ON

declare @v_TranStarted			as     bit
set @v_TranStarted = 0

        IF( @@TRANCOUNT = 0 )
        
        BEGIN
	        BEGIN TRANSACTION
	        SET @v_TranStarted = 1
        END
        
        ELSE
    	    
    	    SET @v_TranStarted = 0
      
    INSERT dbo.T_Person
	(
      Prefix
	, FirstName
	, MiddleName
	, LastName
	, Suffix
	, NickName
	, Alpha
	, Delta
    , DeltaCo
	)
	
    VALUES 
    (
    @i_Prefix
  , @i_FirstName	
  , @i_MiddleName				
  , @i_LastName			
  , @i_Suffix					
  , @i_NickName
  , @i_Alpha
  , @i_Delta
  , @i_DeltaCo	
    )
    
            IF( @v_TranStarted = 1 )
        
        BEGIN
            
            IF  (
                  @@ERROR = 0
				)
				
				BEGIN
		        
				SET @v_TranStarted = 0
				COMMIT TRANSACTION
	            
	                RETURN 1
	                
				END
            
            ELSE
            
            GOTO Cleanup
    
				Cleanup:

				IF( @v_TranStarted = 1 )
			    
				BEGIN
					SET @v_TranStarted = 0
    				ROLLBACK TRANSACTION
					RETURN 0
				END
    
    END
    
    set @o_PersonID = cast(scope_identity() as bigint)
    
END

Open in new window

0
cyimxtck
Asked:
cyimxtck
  • 18
  • 9
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change:

'Dim returnValue As Integer = db.ExecuteScalar(cmd)
                'o_PersonID = CType(db.GetParameterValue(cmd, "o_PersonID"), Int64)
                o_PersonID = cmd.Parameters("@o_PersonID").Value

into:

cmd.Parameters("@o_PersonID").Direction = Output
cmd.ExecuteNonQuery
o_PersonID = cmd.Parameters("@o_PersonID").Value
0
 
cyimxtckAuthor Commented:
Still returns a 0 value?
0
 
cyimxtckAuthor Commented:
I had to change it to:

                cmd.Parameters("@o_PersonID").Direction = ParameterDirection.Output
                cmd.ExecuteNonQuery()
                o_PersonID = cmd.Parameters("@o_PersonID").Value
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
srikanthreddyn143Commented:
in procedure are you assigning the scope identity value to this parameter?
0
 
cyimxtckAuthor Commented:
yes:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[SPROC_INSPerson]
      @i_Prefix                              nvarchar(20)
    , @i_FirstName                        nvarchar(40)
      , @i_MiddleName                        nvarchar(40)
      , @i_LastName                        nvarchar(40)
      , @i_Suffix                              nvarchar(20)
      , @i_NickName                        nvarchar(40)
    , @i_Alpha                  smalldatetime
      , @i_Delta                              bigint
    , @i_DeltaCo                bigint
      , @o_PersonID                        bigint                              OUTPUT
      
AS

BEGIN

SET NOCOUNT ON

declare @v_TranStarted                  as     bit
set @v_TranStarted = 0

        IF( @@TRANCOUNT = 0 )
       
        BEGIN
              BEGIN TRANSACTION
              SET @v_TranStarted = 1
        END
       
        ELSE
              
              SET @v_TranStarted = 0
     
    INSERT dbo.T_Person
      (
      Prefix
      , FirstName
      , MiddleName
      , LastName
      , Suffix
      , NickName
      , Alpha
      , Delta
    , DeltaCo
      )
      
    VALUES
    (
    @i_Prefix
  , @i_FirstName      
  , @i_MiddleName                        
  , @i_LastName                  
  , @i_Suffix                              
  , @i_NickName
  , @i_Alpha
  , @i_Delta
  , @i_DeltaCo      
    )
   
            IF( @v_TranStarted = 1 )
       
        BEGIN
           
            IF  (
                  @@ERROR = 0
                        )
                        
                        BEGIN
                   
                        SET @v_TranStarted = 0
                        COMMIT TRANSACTION
                  
                      RETURN 1
                      
                        END
           
            ELSE
           
            GOTO Cleanup
   
                        Cleanup:

                        IF( @v_TranStarted = 1 )
                      
                        BEGIN
                              SET @v_TranStarted = 0
                            ROLLBACK TRANSACTION
                              RETURN 0
                        END
   
    END
   
    set @o_PersonID = cast(scope_identity() as bigint)
   
END
0
 
srikanthreddyn143Commented:
i think once return is called it doesnt execute next statements so it is not setting the PersonID. have a variable assign 1/0 at return statements and return it after set statement
DECLARE @RetValue AS INT

      BEGIN
           
            IF  (
                  @@ERROR = 0
                        )
                       
                        BEGIN
                   
                        SET @v_TranStarted = 0
                        COMMIT TRANSACTION
                 
                      @RetValue = 1
                     
                        END
           
            ELSE
           
            GOTO Cleanup
   
                        Cleanup:

                        IF( @v_TranStarted = 1 )
                     
                        BEGIN
                              SET @v_TranStarted = 0
                            ROLLBACK TRANSACTION
                            @RetValue =0
                        END
   
    END
   
    set @o_PersonID = cast(scope_identity() as bigint)

RETURN @RetValue
0
 
cyimxtckAuthor Commented:
I wanted to return error codes with the return?
0
 
srikanthreddyn143Commented:
you can return it after that set statement that should work.
0
 
srikanthreddyn143Commented:
use of return exits the sub not setting your personid
0
 
cyimxtckAuthor Commented:
I am not sure I understand?
0
 
cyimxtckAuthor Commented:
use of return exits the sub not setting your personid

That is the part I cannot understand.

Thanks,

B
0
 
srikanthreddyn143Commented:
If you return the value before set statement, set statement will not be executed. This is because when a return statement is executed, store procedure will be exited.
0
 
cyimxtckAuthor Commented:
So I should do or should not do:

    set @o_PersonID = cast(scope_identity() as bigint)

?

Sorry for sounding silly here.

B
0
 
cyimxtckAuthor Commented:
Like even after I make a mod in the proc to be:

    set @o_PersonID = cast(scope_identity() as bigint)
    select @o_PersonID


It still doesn't work?
0
 
srikanthreddyn143Commented:
you have to do that but have the return state after it as I have mentioned
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[SPROC_INSPerson]
      @i_Prefix                              nvarchar(20)
    , @i_FirstName                        nvarchar(40)
      , @i_MiddleName                        nvarchar(40)
      , @i_LastName                        nvarchar(40)
      , @i_Suffix                              nvarchar(20)
      , @i_NickName                        nvarchar(40)
    , @i_Alpha                  smalldatetime
      , @i_Delta                              bigint
    , @i_DeltaCo                bigint
      , @o_PersonID                        bigint                              OUTPUT
      
AS

BEGIN

SET NOCOUNT ON

declare @v_TranStarted                  as     bit
set @v_TranStarted = 0
declare @RetValue as int

        IF( @@TRANCOUNT = 0 )
       
        BEGIN
              BEGIN TRANSACTION
              SET @v_TranStarted = 1
        END
       
        ELSE
              
              SET @v_TranStarted = 0
     
    INSERT dbo.T_Person
      (
      Prefix
      , FirstName
      , MiddleName
      , LastName
      , Suffix
      , NickName
      , Alpha
      , Delta
    , DeltaCo
      )
      
    VALUES
    (
    @i_Prefix
  , @i_FirstName      
  , @i_MiddleName                        
  , @i_LastName                  
  , @i_Suffix                              
  , @i_NickName
  , @i_Alpha
  , @i_Delta
  , @i_DeltaCo      
    )
   
            IF( @v_TranStarted = 1 )
       
        BEGIN
           
            IF  (
                  @@ERROR = 0
                        )
                        
                        BEGIN
                   
                        SET @v_TranStarted = 0
                        COMMIT TRANSACTION
                  
                     -- RETURN 1
                      @RetValue = 1
                        END
           
            ELSE
           
            GOTO Cleanup
   
                        Cleanup:

                        IF( @v_TranStarted = 1 )
                      
                        BEGIN
                              SET @v_TranStarted = 0
                            ROLLBACK TRANSACTION
                             -- RETURN 0
                             @RetValue = 0
                        END
   
    END
   
    set @o_PersonID = cast(scope_identity() as bigint)
   RETURN @RetValue
END

Open in new window

0
 
cyimxtckAuthor Commented:
I see what you are saying about the RETURN value and have qualified it as you have mentioned but still get a 0 value:

            Try
                Dim ds As DataSet = New DataSet()
                Dim dt As DataTable = New DataTable()
                Dim db As Database = DatabaseFactory.CreateDatabase("EZPlacementConnectionString")
                Dim sql As String = "SPROC_INSPerson"
                Dim cmd As DbCommand = db.GetStoredProcCommand(sql)

                db.AddInParameter(cmd, "i_Prefix", DbType.String, i_Prefix)
                db.AddInParameter(cmd, "i_FirstName", DbType.String, i_FirstName)
                db.AddInParameter(cmd, "i_MiddleName", DbType.String, i_MiddleName)
                db.AddInParameter(cmd, "i_LastName", DbType.String, i_LastName)
                db.AddInParameter(cmd, "i_Suffix", DbType.String, i_Suffix)
                db.AddInParameter(cmd, "i_NickName", DbType.String, i_NickName)
                db.AddInParameter(cmd, "i_Alpha", DbType.DateTime, i_Alpha)
                db.AddInParameter(cmd, "i_Delta", DbType.Int64, i_Delta)
                db.AddInParameter(cmd, "i_DeltaCo", DbType.Int64, i_DeltaCo)
                db.AddOutParameter(cmd, "o_PersonID", DbType.Int64, 8)

                'db.ExecuteScalar(cmd)

                'o_PersonID = CType(db.GetParameterValue(cmd, "o_PersonID"), Int64)
                'o_PersonID = cmd.Parameters("@o_PersonID").Value

                'o_PersonID = db.GetParameterValue(cmd, "o_PersonID")

                cmd.Parameters("@o_PersonID").Direction = ParameterDirection.Output
                cmd.ExecuteNonQuery()
                o_PersonID = cmd.Parameters("@o_PersonID").Value

            Catch ex As Exception
                Throw ex

            End Try
0
 
cyimxtckAuthor Commented:
I modified the procedure as:

ALTER PROCEDURE [dbo].[SPROC_INSPerson]
      @i_Prefix                              nvarchar(20)
    , @i_FirstName                        nvarchar(40)
      , @i_MiddleName                        nvarchar(40)
      , @i_LastName                        nvarchar(40)
      , @i_Suffix                              nvarchar(20)
      , @i_NickName                        nvarchar(40)
    , @i_Alpha                  smalldatetime
      , @i_Delta                              bigint
    , @i_DeltaCo                bigint
      , @o_PersonID                        bigint                              OUTPUT
      
AS

BEGIN

SET NOCOUNT ON

declare @v_TranStarted                  as     bit
set @v_TranStarted = 0

declare @o_RetValue as int

        IF( @@TRANCOUNT = 0 )
       
        BEGIN
              BEGIN TRANSACTION
              SET @v_TranStarted = 1
        END
       
        ELSE
              
              SET @v_TranStarted = 0
     
    INSERT dbo.T_Person
      (
      Prefix
      , FirstName
      , MiddleName
      , LastName
      , Suffix
      , NickName
      , Alpha
      , Delta
    , DeltaCo
      )
      
    VALUES
    (
    @i_Prefix
  , @i_FirstName      
  , @i_MiddleName                        
  , @i_LastName                  
  , @i_Suffix                              
  , @i_NickName
  , @i_Alpha
  , @i_Delta
  , @i_DeltaCo      
    )
   
            IF( @v_TranStarted = 1 )
       
        BEGIN
           
            IF  (
                  @@ERROR = 0
                        )
                        
                        BEGIN
                   
                        SET @v_TranStarted = 0
                        COMMIT TRANSACTION
                  
                     set @o_RetValue = 1
                      
                        END
           
            ELSE
           
            GOTO Cleanup
   
                        Cleanup:

                        IF( @v_TranStarted = 1 )
                      
                        BEGIN
                              SET @v_TranStarted = 0
                            ROLLBACK TRANSACTION
                              
                              set @o_RetValue = 0
                              
                        END
   
    END
   
    set @o_PersonID = cast(scope_identity() as bigint)
    return @o_RetValue
   
END
0
 
srikanthreddyn143Commented:
does the record gets inserted?
0
 
cyimxtckAuthor Commented:
No this blows up:


                cmd.Parameters("@o_PersonID").Direction = ParameterDirection.Output
                cmd.ExecuteNonQuery()
                o_PersonID = cmd.Parameters("@o_PersonID").Value
0
 
cyimxtckAuthor Commented:
this inserts though:

DECLARE      @return_value int,
            @o_PersonID bigint

SELECT      @o_PersonID = null

EXEC      @return_value = [dbo].[SPROC_INSPerson]
            @i_Prefix = N'MR.',
            @i_FirstName = N'Donald',
            @i_MiddleName = N'Rose',
            @i_LastName = N'Klingensmith',
            @i_Suffix = N'Esq.',
            @i_NickName = N'Klingy',
            @i_Alpha = '01/07/2010',
            @i_Delta = 0,
            @i_DeltaCo = 0,
            @o_PersonID = @o_PersonID OUTPUT

SELECT      @o_PersonID as N'@o_PersonID'

SELECT      'Return Value' = @return_value

GO
0
 
cyimxtckAuthor Commented:
And to add to that you can see the insert and scope_identity attached.

Thanks,

B
0
 
cyimxtckAuthor Commented:
This makes sense as 1 means success and the output is correct in a test environment.

Thanks,

B
0
 
cyimxtckAuthor Commented:
Not sure if the file made it or not?
0
 
cyimxtckAuthor Commented:
Anyway in SQL Server it gives me back the value of 1 for the return (meaning succeeded) and also the @o_PersonID of 30
0
 
cyimxtckAuthor Commented:
I am sorry if this is basic an trivial for you.  Just getting into some of this is starting to make what little hair I have left fall out.

Please give me some input.  Even if it is "you are on your own" kinda thing.

Thanks,

B
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>No this blows up:
what error?

the t-sql proc code is fine.
0
 
srikanthreddyn143Commented:
debug and let us know the exception
0
 
cyimxtckAuthor Commented:
I finally got this with the help.

Thanks for everyones input.

B
0
 
srikanthreddyn143Commented:
what was the final change you made
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 18
  • 9
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now