执行后的事务计数表示开始和提交的数量不匹配 [英] Transaction count after execute indicates a mismatching number of begin and commit

问题描述

我正在尝试使用SP在sql server中插入新记录。

插入命令后我需要last_increment_id。但它产生错误

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

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

请帮助



Sql - SP

Please help

Sql - SP

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代码 -

VB.Net Code -

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()

推荐答案

您好b $ b

请注意，您在提交事务指令之前调用RETURN语句，所以在某种程度上你的提交事务语句永远不会被执行。你必须知道包含return语句意味着它无条件退出查询或程序



问候

Pawan

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

无效的对象名称'TET_to_bank_for_fee'。

EXECUTE之后的事务计数表示BEGIN和COMMIT语句的数量不匹配。先前的计数= 0，当前计数= 1.

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.

这篇关于执行后的事务计数表示开始和提交的数量不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！