''' <summary> ''' 使用OleDbConnection执行Sql ''' </summary> ''' <param name="sql"></param> ''' <param name="myConnection"></param> ''' <returns></returns> ''' <res></res> Public Function ExecSql(ByVal sql As String, ByVal myConnection As OleDb.OleDbConnection) As Long Dim myTrans As OleDb.OleDbTransaction Try Dim myCommand As OleDb.OleDbCommand myCommand = New OleDb.OleDbCommand() If myConnection.State = ConnectionState.Closed Then Try myConnection.Open() Catch ex As Exception sql = "" If SystemConfig.IsShowDebugInfo = True Then Throw New Exception(Def.ConstPrompt.C系统异常 & ex.Message) Else Throw New Exception(Def.ConstPrompt.C数据库连接失败) End If End Try Else myConnection.Close() myConnection.Open() End If myCommand.CommandTimeout = _ExecTimeout myTrans = myConnection.BeginTransaction() myCommand.Connection = myConnection myCommand.Transaction = myTrans myCommand.CommandText = sql ExecSql = myCommand.ExecuteNonQuery() myTrans.Commit() '数据库操作完成! If ExecSql = -1 Then ExecSql = 0 End If Catch ex As Exception '数据库操作未完成! myTrans.Rollback() If SystemConfig.IsShowDebugInfo = True Then Throw New Exception(Def.ConstPrompt.C系统异常 & ex.Message) Else ExecSql = -1 End If Finally If myTrans IsNot Nothing Then myTrans.Dispose() End If myTrans = Nothing If myConnection.State = ConnectionState.Open Then myConnection.Close() End Try End Function居然没有vb的代码。放C#里面了。。这个返回受影响行数,报错就返回-1. 调用 ExecSql(sql,con)
用事务去判断是否执行成功?如果执行不成功会有错误返回。
也可以有try ..... catch2.
insert into tb(....) values(....)
select @@ROWCOUNT -->加这一行
@@ROWCOUNT是系统变量,返回所影响的行数
楼主可在存储过程里,强制返回UPDATE记录数。
declare @updatecount int
update tb set fieldvalue = 2 where id = 1
set @updatecount = @@rowcount
return @updatecount
参考:
http://www.cnblogs.com/net515/archive/2012/06/10/2544131.html
2:我是用的连接对象的execute :m_pConnection->Execute(strSql,NULL,adCmdText); 返回值是记录集对象。
没有ExecuteNonQuery 方法
''' <summary>
''' 使用OleDbConnection执行Sql
''' </summary>
''' <param name="sql"></param>
''' <param name="myConnection"></param>
''' <returns></returns>
''' <res></res>
Public Function ExecSql(ByVal sql As String, ByVal myConnection As OleDb.OleDbConnection) As Long
Dim myTrans As OleDb.OleDbTransaction
Try
Dim myCommand As OleDb.OleDbCommand
myCommand = New OleDb.OleDbCommand() If myConnection.State = ConnectionState.Closed Then
Try
myConnection.Open()
Catch ex As Exception
sql = "" If SystemConfig.IsShowDebugInfo = True Then
Throw New Exception(Def.ConstPrompt.C系统异常 & ex.Message)
Else
Throw New Exception(Def.ConstPrompt.C数据库连接失败)
End If
End Try
Else
myConnection.Close()
myConnection.Open()
End If myCommand.CommandTimeout = _ExecTimeout
myTrans = myConnection.BeginTransaction()
myCommand.Connection = myConnection
myCommand.Transaction = myTrans
myCommand.CommandText = sql
ExecSql = myCommand.ExecuteNonQuery()
myTrans.Commit()
'数据库操作完成!
If ExecSql = -1 Then
ExecSql = 0
End If
Catch ex As Exception
'数据库操作未完成!
myTrans.Rollback() If SystemConfig.IsShowDebugInfo = True Then
Throw New Exception(Def.ConstPrompt.C系统异常 & ex.Message)
Else
ExecSql = -1
End If
Finally
If myTrans IsNot Nothing Then
myTrans.Dispose()
End If
myTrans = Nothing
If myConnection.State = ConnectionState.Open Then myConnection.Close()
End Try
End Function居然没有vb的代码。放C#里面了。。这个返回受影响行数,报错就返回-1.
调用 ExecSql(sql,con)
C++代码
AnsiString SqlText,ErrText;
//开始一个事务处理
ADOCommand->Connection->BeginTrans();
try
{
//清除现有数据库连接中的异常信息
ADOCommand->Connection->Errors->Clear();
//执行SQL语句
ADOCommand->CommandText=SqlText;
ADOCommand->Execute();
//如果数据库执行过程产生异常(错误记录数大于0)
if(ADOCommand->Connection->Errors->Count>0)
{
//获取错误信息文本
ErrText=ADOCommand->Connection->Errors->Item[0]->Description;
//清除现有数据库连接中的异常信息
ADOCommand->Connection->Errors->Clear();
//主动抛出异常
throw Exception(ErrText);
}
//如果没有异常,而且事务处理仍在进行,则提交事务
if(ADOCommand->Connection->InTransaction)
ADOCommand->Connection->CommitTrans();
}
catch(Exception &e)
{
//如果有异常,而且事务处理仍在进行,则回滚事务
if(ADOCommand->Connection->InTransaction)
ADOCommand->Connection->RollbackTrans();
//显示异常信息
ShowMessage(e.Message);
}
描述:这是数据库操作过程经常用到的一段代码,往往Insert 或者是Update语句执行过程中产生的错误无法被捕获!这段代码告诉我们其实执行异常信息存放在ADOConnection的异常信息列表中,我们只需要把它抓出来处理并抛出异常即可!