本文介绍了执行后的事务计数表示开始和提交的数量不匹配的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用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()
推荐答案
这篇关于执行后的事务计数表示开始和提交的数量不匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!