Click here to Skip to main content
15,889,909 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,
I am trying to insert a new record in sql server using SP.
I need last_increment_id after insert command. but it produce error
VB
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.


Please help

Sql - SP
SQL
USE [db_my_database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[data_add]
	@ID int OUTPUT,
	@TYPEID int = null OUTPUT,
	@name nvarchar(50),
	@code nvarchar(50),
	@type nvarchar(50)
AS
BEGIN transaction
	SET NOCOUNT ON
	SET @TYPEID = (select id from category where name = @type)

	insert into mytable(name, code, type)
	values (@name, @code, @TYPEID)
	SET @ID = IDENT_CURRENT('mytable')
        RETURN @ID
commit transaction

if @@error <> 0
	begin
	ROLLBACK transaction
end


VB.Net Code -
VB
Dim command As SqlCommand = New SqlCommand("data_add", conn)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output
command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "name here"
command.Parameters.Add("@code", SqlDbType.NVarChar).Value = "code here"
command.Parameters.Add("@type", SqlDbType.NVarChar).Value = "type here"
command.ExecuteNonQuery()
Posted
Updated 30-Sep-18 5:36am

Hi
Please be aware that you are calling the RETURN statement before the commit transaction instruction, so in a way your commit transaction statement never gets executed. You must know that inclusion of return statement implies that the it exits unconditionally from a query or procedure

Regards
Pawan
 
Share this answer
 
Invalid object name 'TET_to_bank_for_fee'.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
 
Share this answer
 
Comments
Richard Deeming 3-Oct-18 13:59pm    
If you want to ask a question, then ASK A QUESTION[^].

DO NOT post your question as a "solution" to someone else's question!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900