Get the record ID of newly insert record : The Official Microsoft ASP.NET Forums

Reply

DeepZone1

Member

32 Points

Get the record ID of newly insert record

Jun 12, 2012 08:12 PM|LINK

Hi, I try the following code in order to get the newly insert database record ID but always get a zero. Anybody has a clue what goes wrong? I can see the stored procedure does return a value if I run the SP only.

Private Function Insert() As Boolean
            _PropertyID = Convert.ToInt32(SqlHelper.ExecuteScalar(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString, _
             "Property_Insert", _
             _LastUpdBy, _Addr1, _Addr2, _City, _ProvinceID, _PostalCode, _Owner))
            Return _PropertyID > 0
        End Function

ALTER PROCEDURE dbo.Property_Insert
	(
	@LastUpdBy		INT,
	@Addr1			NVARCHAR(50),
	@Addr2			NVARCHAR(50),
	@City			NCHAR(20),
	@ProvinceID		INT,
	@PostalCode		NCHAR(10),
	@Owner			NVARCHAR(256)
	)
AS
	SET NOCOUNT ON
	
BEGIN
    
    DECLARE @ErrorCode     INT
    SET @ErrorCode = 0

    DECLARE @TranStarted   BIT
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 ) BEGIN
        BEGIN TRANSACTION
        SET @TranStarted = 1 END
    ELSE
        SET @TranStarted = 0
        
		IF( @@ERROR <> 0 ) BEGIN
			SET @ErrorCode = -1
			GOTO Cleanup
		END

IF EXISTS(SELECT PropertyID FROM Property WHERE Addr1=@Addr1 AND Addr2=@Addr2 AND Deleted=0)
    
    BEGIN    
		SET @ErrorCode = 1
		GOTO Cleanup
	END

INSERT INTO Property
(
LastUpdBy,
Addr1,
Addr2,
City,
ProvinceID,
PostalCode,
Owner
)
VALUES
(
@LastUpdBy,
@Addr1,
@Addr2,
@City,
@ProvinceID,
@PostalCode,
@Owner
) 

IF( @@ERROR <> 0 ) BEGIN
        SET @ErrorCode = -1
        GOTO Cleanup
END
    
IF( @TranStarted = 1 ) BEGIN
        SET @TranStarted = 0
        COMMIT TRANSACTION
END

    RETURN @@IDENTITY

Cleanup:

    IF( @TranStarted = 1 ) BEGIN
        SET @TranStarted = 0
        ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode

END

Edited by deepzone1 on Jun 12, 2012 08:13 PM
Edited by deepzone1 on Jun 12, 2012 08:29 PM
Edited by deepzone1 on Jun 12, 2012 08:30 PM
Edited by deepzone1 on Jun 12, 2012 08:30 PM
Moved by Mikesdotnetting on Jun 21, 2012 06:30 PM

Reply

Sparkers

Contributor

2086 Points

470 Posts

Re: Get the record ID of newly insert record

Jun 12, 2012 08:24 PM|LINK

Did you try to debug?

Put a breakpoint on the Return and hit f11 once you reach the _PropertyID and see what value are you returning..

As far as I am concerent you have to define that in a method and then pass the values of the form to that method and why are you declaring the conn string here? You can make use of DataSets or LINQ and define the stored procedures and the tables in them and it will your life much easier..

Please ask if I do not make any sense or completely misunderstood your issue..

Please remember to click “Mark as Answer” on the post that helps you.

Edited by sparkers on Jun 12, 2012 08:30 PM

Reply

DeepZone1

Member

32 Points

44 Posts

Re: Get the record ID of newly insert record

Jun 12, 2012 08:28 PM|LINK

Sparkers

Did you try to debug?

Put a breakpoint on the Return and hit f11 once you reach the _PropertyID and see what value are you returning..

As far as I am concerent you have to define that in a method and then pass the values of the form to that method and why are you declaring the conn string here? You can make use of DataSets or LINQ and define the stored procedures and the tables in them and it will your life much easier..

Please ask if I do not make any sense or completely misunderstood your issue..

Thanks for reply. I ran both VB and SP in debug mode. For VB, I got a 0 in _PropertyID whereas the SP ends at RETURN @@IDENTITY and it has the ID of newly added record.