我有一个测试SQL Server表,其数据如下
ItemId Description ItemCost
1 first item 100
2 second item 200
3 third item 300以及将项添加到Items表中的存储过程。
create proc spInsertItem
@itemId int
,@itemDescription varchar(50)
,@itemCost decimal
as
begin
if(@itemCost < 0)
begin
raiserror('cost cannot be less than 0',16,1)
end
else
begin
begin try
begin tran
insert into Items(itemid, [description],itemCost)
values (@itemid, @itemdescription,@itemCost)
commit tran
end try
begin catch
rollback tran
select ERROR_LINE()as errorLine
,ERROR_MESSAGE() as errorMessage
,ERROR_STATE() as errorState
,ERROR_PROCEDURE() as errorProcedure
,ERROR_NUMBER() as errorNumber
end catch
end
end 当我在SSMS中执行该过程时,它会正确地报告负成本的错误。当我使用以下代码时:
protected void btnSubmit_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["dbcsI3"].ConnectionString;
using (var con = new SqlConnection(cs))
{
SqlTransaction tran = con.BeginTransaction();
try
{
using (var cmd = new SqlCommand("spInsertItem", con))
{
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@itemId", Convert.ToInt32(txtItemId.Text));
cmd.Parameters.AddWithValue("@itemdescription", txtItemDescription.Text);
cmd.Parameters.AddWithValue("@itemCost", Convert.ToInt32(txtItemCost.Text));
cmd.ExecuteNonQuery();
tran.Commit();
}
}
catch (Exception ex)
{
lblStatus.Text = ex.Message; //the intent is to print the error message to the user
tran.Rollback();
}
}
}当我使用这段代码时,我会得到一个异常,即连接已关闭,但随后我跳到SSMS,发现它正常工作。在我把东西搬到杂乱无章地工作之前,我想知道为什么我会收到连接被关闭的错误。每当我将可行的数据输入到该表中时,该过程也会起作用。
发布于 2013-08-12 15:04:46
尝试在BeginTransaction之前打开连接
using (var con = new SqlConnection(cs))
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
// rest of the code https://stackoverflow.com/questions/18190340
复制相似问题