关于事务的问题,很奇怪,怎么回事呢?

herony420 2003-09-29 11:26:31
使用 Dim trnInsert As SqlClient.SqlTransaction
strWebConn = ConfigurationSettings.AppSettings("strWebConn")
objConn = New SqlConnection(strWebConn)

objConn.Open()
'开始事务
trnInsert = objConn.BeginTransaction
开始执行插入的操作,在这之前有一个插入的事务,但那个连接conn已经关闭,然后重新创建一个新的conn对象使用事务,当执行插入时,出现异常,异常的描述是下面的一句话,这是怎么回事呢?请大家指点
"当分配给命令的连接处于挂起的本地事务中时,命令必须具有事务对象才能执行。该命令的 Transaction 属性尚未初始化。"
...全文
27 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
qimini 2003-09-29
  • 打赏
  • 举报
回复
把你的代码改一下:

Dim strWebConn As String
Dim strSql As String
Dim sError As String '定义错误信息


Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim trnInsert As SqlClient.SqlTransaction

Try
strWebConn = ConfigurationSettings.AppSettings("strWebConn")
objConn = New SqlConnection(strWebConn)

objConn.Open()
'开始事务
trnInsert = objConn.BeginTransaction
'执行插入的存储过程
objCmd = New SqlClient.SqlCommand("ct_insert_act_log", objConn)
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Transaction = trnInsert;<<<<<<<<<<<<<<<<<<<加上这句


qimini 2003-09-29
  • 打赏
  • 举报
回复
objCmd.Connection = objConn;
objCmd.Transaction = trnInsert;

要给SqlCommand对象绑定Transaction属性到具体的SqlTransaction对象
herony420 2003-09-29
  • 打赏
  • 举报
回复
我就是按照这样的实现,具体的过程是首先是在增加的按钮事件中添加以下代码
Dim dp As New DataOper '新建数据操作对象
Dim sError As String '返回的错误信息

Dim trnInsert As SqlTransaction = objConn.BeginTransaction

Try
objCmd = New SqlClient.SqlCommand("ct_scheme_new_step_last_updata", objConn)
'开始事务处理
objCmd.Transaction = trnInsert

objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = Trim(Session("UserName"))
objCmd.Parameters.Add("@oper_sms_type", SqlDbType.Int, 4).Value = CInt(Trim(Session("MessageType")))
objCmd.Parameters.Add("@shme_message_content", SqlDbType.NVarChar, 255).Value = Trim(Session("BasicSms"))
objCmd.Parameters.Add("@shme_message_type", SqlDbType.NVarChar, 20).Value = ""
objCmd.Parameters.Add("@oper_scheme_status", SqlDbType.Int, 4).Value = 1001
objCmd.Parameters.Add("@oper_client_id", SqlDbType.Int, 4).Value = CInt(Trim(Session("ClientID")))
objCmd.Parameters.Add("@oper_region", SqlDbType.Char, 5).Value = Trim(Session("ClientRegion"))
objCmd.Parameters.Add("@oper_section_id", SqlDbType.Int, 4).Value = CInt(Trim(Session("NewScheme_select_cell_id")))
objCmd.Parameters.Add("@oper_start_time", SqlDbType.SmallDateTime, 4).Value = Session("NewSchemeStartTime")
objCmd.Parameters.Add("@oper_finish_time", SqlDbType.SmallDateTime, 4).Value = Session("NewSchemeFinishTime")
objCmd.Parameters.Add("@oper_client_group", SqlDbType.NVarChar, 500).Value = Session("NewScheme_select_send_client_group")
objCmd.Parameters.Add("@oper_section_group", SqlDbType.NVarChar, 500).Value = Session("NewScheme_select_send_section_group")
objCmd.Parameters.Add("@oper_region_group", SqlDbType.NVarChar, 500).Value = Session("NewScheme_select_send_zip")
objCmd.Parameters.Add("@oper_min_repeat_timeslot", SqlDbType.Int, 4).Value = CInt(Session("NewSchemeTimeSpace"))
objCmd.Parameters.Add("@oper_message_live_period", SqlDbType.Int, 4).Value = CInt(Session("NewSchemeLiveTime"))
objCmd.Parameters.Add("@oper_object_action", SqlDbType.Int, 4).Value = CInt(Session("NewSchemeObjectActionNum"))
objCmd.Parameters.Add("@oper_defer_time", SqlDbType.Int, 4).Value = CInt(Session("NewSchemeDelayTime"))
objCmd.Parameters.Add("@oper_visit_frequency", SqlDbType.Int, 4).Value = CInt(Session("NewSchemeVisitFrequency"))
objCmd.Parameters.Add("@oper_submit_reason", SqlDbType.NVarChar, 20).Value = "新业务申请"
objCmd.Parameters.Add("@oper_fee_type", SqlDbType.Int, 4).Value = CInt(Session("selectFeeType"))
objCmd.Parameters.Add("@oper_fee_mobile_code", SqlDbType.Char, 14).Value = Session("FeeMobile")
objCmd.Parameters.Add("@oper_fee_rate", SqlDbType.Int, 4).Value = CInt(Session("FeeRate"))
objCmd.Parameters.Add("@oper_max_send_everyday", SqlDbType.Int, 4).Value = 0
objCmd.Parameters.Add("@oper_scheme_priority_quotiety", SqlDbType.Int, 4).Value = 0
objCmd.Parameters.Add("@oper_send_order", SqlDbType.Int, 4).Value = 1
objCmd.Parameters.Add("@oper_is_sm_script", SqlDbType.Int, 1).Value = Session("smsFlag")
objCmd.Parameters.Add("@oper_sm_script", SqlDbType.NVarChar, 500).Value = Session("advSms")
objCmd.Parameters.Add("@oper_remark", SqlDbType.VarChar, 500).Value = txtRemark.Text

objCmd.ExecuteNonQuery()
trnInsert.Commit()
'trnInsert.Dispose()
'objCmd.Dispose()
'objConn.Dispose()
Catch ex As Exception
trnInsert.Rollback()
objCmd.Dispose()
objConn.Close()
MsgBox("出现错误,事务已回滚!" & ex.Message, False)
'trnInsert.Dispose()
Exit Sub
End Try
'关闭并释放
objCmd.Dispose()
objConn.Close()

'这里是调用类,也是出错的地方
'写入操作日志
sError = dp.InsertActLog(Session("ClientName"), Now, 5, "新增业务", "", 1)
'****************************************************
Public Function InsertActLog(ByVal actMan As String, ByVal actTime As DateTime, ByVal actId As Integer, ByVal describe As String, ByVal effect As String, ByVal iEnd As Integer) As String
'数据库的连接与打开,以函数作为生命周期
Dim strWebConn As String
Dim strSql As String
Dim sError As String '定义错误信息


Dim objConn As SqlConnection
Dim objCmd As SqlCommand
Dim trnInsert As SqlClient.SqlTransaction

Try
strWebConn = ConfigurationSettings.AppSettings("strWebConn")
objConn = New SqlConnection(strWebConn)

objConn.Open()
'开始事务
trnInsert = objConn.BeginTransaction
'执行插入的存储过程
objCmd = New SqlClient.SqlCommand("ct_insert_act_log", objConn)
objCmd.CommandType = CommandType.StoredProcedure
'@actMan nvarchar(10), --操作人
'@actTime datetime,--操作发生的时间
'@actId int, --操作动作的ID
'@describe ntext, --操作结果描述
'@effect char(20), --操作的影响
'@end bit --操作发生的终端
objCmd.Parameters.Add("@actMan", SqlDbType.NVarChar, 10).Value = actMan
objCmd.Parameters.Add("@actTime", SqlDbType.DateTime, 8).Value = actTime
objCmd.Parameters.Add("@actId", SqlDbType.Int, 4).Value = actId
objCmd.Parameters.Add("@describe", SqlDbType.NText, 16).Value = describe
objCmd.Parameters.Add("@effect", SqlDbType.Char, 20).Value = effect
objCmd.Parameters.Add("@end", SqlDbType.TinyInt, 2).Value = iEnd
objCmd.ExecuteNonQuery()

'开始事务处理
trnInsert.Commit()
'关闭,释放资源
objCmd.Dispose()
''trnInsert.Dispose()
objConn.Dispose()
Catch ex As Exception
'如果出现错误,首先将操作回滚
trnInsert.Rollback()
'trnInsert.Dispose()
objCmd.Dispose()
objConn.Close()
sError = "error:" & ex.Message
Return sError
Finally
objConn.Close()
End Try
End Function
'**************************************************
'发短信并写入操作日志
sError = dp.SendSmsToAudit(Session("ClientRegion"), Session("ClientName"), "发送新增业务的提醒短信")
If sError <> "" Then
MsgBox(sError, False)
Exit Sub
Else
MsgBox("添加成功!!", False)
End If
qimini 2003-09-29
  • 打赏
  • 举报
回复
=======================These code from MSDN 2003 may help you

SqlConnection myConnection = new SqlConnection(myConnString);
myConnection.Open();

SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;

// Start a local transaction
myTrans = myConnection.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}

Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
herony420 2003-09-29
  • 打赏
  • 举报
回复
多谢!!我怎么没有看到这地方的事务,多谢多谢!!给分

62,025

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见,支持健康理性的辩论和互动,反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧