none
SCOPE_IDENTITY() returns -1 in vb.net

    Question

  • Hai

    I use following code in stored procedure

    ALTER PROCEDURE [dbo].[Proc_IMaster] 
    	@Master_Id int = null,
    	@MasterGroup_Id int = null,
    	@Name nchar(60) = null,
    	@StType char(6)
    
    AS
    BEGIN
    	SET NOCOUNT ON;
    	BEGIN
    	IF @StType = 'Insert'
    		INSERT Imaster(MasterGroup_Id, Name)
    		VALUES (@MasterGroup_Id, @Name)
    		Return SCOPE_IDENTITY()
    	END
     
    END
    

    And in vb.net

    Dim Com As New SqlCommand()
    Com.Connection = Con
    Com.CommandText = "Proc_Imaster"
    Com.CommandType = CommandType.StoredProcedure
    Com.Parameters.AddWithValue("@MasterGroup_Id", CompanyID)
    Com.Parameters.AddWithValue("@Name", Txt_Name.Text)
    Com.Parameters.AddWithValue("@StType", "Insert")
    
    Dim Master_Id As Int16 = Com.ExecuteNonQuery()
    

    Here  Master_Id  is my id field in SQL table and new record is inserted perfectly 

    My Error is Master_Id  returns -1 value (IE.  SCOPE_IDENTITY)

    Please help me to sort out  my mistake.

    Thanks


    programmer

    Monday, April 3, 2017 6:44 AM

Answers

  • Consider the next way too:

    In SQL:

       . . .
       SELECT SCOPE_IDENTITY() -- instead of RETURN

    In VB:

       . . .
       Dim Master_Id As Integer = CInt(Com.ExecuteScalar())


    • Edited by Viorel_MVP Monday, April 3, 2017 5:29 PM
    • Marked as answer by babucr Wednesday, April 5, 2017 11:15 AM
    Monday, April 3, 2017 5:27 PM

All replies

  • Hi, you must define an OUT parameter in the stored procedure signature and assign SCOPE_IDENTITY to that parameter.

    ALTER PROCEDURE [dbo].[Proc_IMaster] 
    	@Master_Id int = null,
    	@MasterGroup_Id int = null,
    	@Name nchar(60) = null,
    	@StType char(6),
    	@InsertedID int OUT
    
    AS
    BEGIN
    	SET NOCOUNT ON;
    	BEGIN
    
    	SET @InsertedID = 0;
    
    	IF @StType = 'Insert'
    		INSERT Imaster(MasterGroup_Id, Name)
    		VALUES (@MasterGroup_Id, @Name)
    		
    		SET @InsertedID = SCOPE_IDENTITY(); -- <<<<<
    	END
     
    END
    Hope will help you


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it




    Monday, April 3, 2017 6:58 AM
  • For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

    Success
    Cor

    Monday, April 3, 2017 7:11 AM
  • Hello,

    Check out my code sample which is done slightly different than yours. You need to use ExecuteScalar.

    https://code.msdn.microsoft.com/SQL-stored-procedures-1384f04c

    Stored procedure

    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
    CREATE PROCEDURE [dbo].[InsertCustomer]  
        @CompanyName nvarchar(200), 
        @ContactName nvarchar(200), 
        @ContactTitle nvarchar(200), 
        @Identity int OUT 
    AS 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON; 
     
    INSERT INTO Customer(CompanyName,ContactName,ContactTitle)  
        VALUES(@CompanyName,@ContactName,@ContactTitle) 
     
    SET @Identity = SCOPE_IDENTITY() 
     
    END 
    

    Code which calls the stored procedure

    Public Function AddCustomer(ByVal CompanyName As String, ByVal ContactName As String, ByVal ContactTitle As String) As Integer 
    
        Try 
            Using cn As New SqlConnection With {.ConnectionString = Me.ConnectionString} 
                Using cmd As New SqlCommand With {.Connection = cn, .CommandType = CommandType.StoredProcedure, .CommandText = "dbo.InsertCustomer"} 
                    cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@CompanyName", .SqlDbType = SqlDbType.NVarChar}) 
                    cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@ContactName", .SqlDbType = SqlDbType.NVarChar}) 
                    cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@ContactTitle", .SqlDbType = SqlDbType.NVarChar}) 
                    cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Identity", .SqlDbType = SqlDbType.Int, 
                                                              .Direction = ParameterDirection.Output}) 
    
                    cmd.Parameters("@CompanyName").Value = CompanyName 
                    cmd.Parameters("@ContactName").Value = ContactName 
                    cmd.Parameters("@ContactTitle").Value = ContactTitle 
                    cn.Open() 
                    Dim affected = cmd.ExecuteScalar 
    
                    Me.Exception = Nothing 
                    Return CInt(cmd.Parameters("@Identity").Value) 
    
                End Using 
            End Using 
        Catch ex As Exception 
            Me.Exception = ex 
            Return -1 
        End Try 
    End Function 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Monday, April 3, 2017 11:09 AM
    Moderator
  • Consider the next way too:

    In SQL:

       . . .
       SELECT SCOPE_IDENTITY() -- instead of RETURN

    In VB:

       . . .
       Dim Master_Id As Integer = CInt(Com.ExecuteScalar())


    • Edited by Viorel_MVP Monday, April 3, 2017 5:29 PM
    • Marked as answer by babucr Wednesday, April 5, 2017 11:15 AM
    Monday, April 3, 2017 5:27 PM