我在程序中写了一个执行SQL语句的函数,每次要执行SQL语句时我调用此函数即可,但是由于直接用函数中的数据库连接(CN),请问在事件中该如何加上事务.
procuder command1_click()
dim strsql as string
strsql=" ...table1... "
executesql(strsql) '对table1进行操作,
strsql="...table2..."
executesql(strsql) '对table2进行操作
strsql=" ...table..."
executesql(strsql) '对table3进行操作,
end
function executesql(byval strsql as string) as recordset
dim cn as new adodb.connetino
.
.
.
end
请问该把事务写到事件中去.
procuder command1_click()
dim strsql as string
strsql=" ...table1... "
executesql(strsql) '对table1进行操作,
strsql="...table2..."
executesql(strsql) '对table2进行操作
strsql=" ...table..."
executesql(strsql) '对table3进行操作,
end
function executesql(byval strsql as string) as recordset
dim cn as new adodb.connetino
.
.
.
end
请问该把事务写到事件中去.
解决方案 »
- vb 如何改变DataGrid选中行的颜色
- 界面问题
- ====WebBrowser如何打印文档的第一页?====
- 急!!order by和批量更新的问题!!
- 在MDI窗口中,treeview如何点击右键弹出子菜单?急,,马上结贴
- 遇到难题了?解决立马给分!
- 请教如何模拟点击VB控件listview的记录,用什么API?
- 寻找三个高级控件.谢谢!
- 要在一行中放许多的输入控件,比如textbox,页面不够宽了,如何使用滚动条?有好的容器吗?
- 在VB中如何实现用printer打印的预览效果
- 紧急求助,显示实际连接波特率高于设定的,造成通讯异常怎么办?
- 如何对一个2×2的16进制数据的矩阵进行水平和垂直校验??
on Error Goto Errhandle
cn.BeginTrans
dim strsql as string
strsql=" ...table1... "
executesql(strsql) '对table1进行操作,
strsql="...table2..."
executesql(strsql) '对table2进行操作
strsql=" ...table..."
executesql(strsql) '对table3进行操作,
cn.CommitTrans
Exit sub
Errhandle:
cn.RollbackTrans
end Sub
exesql("insert into ..")
..
exesql("committrans")
As String) _
As ADODB.Recordset Dim Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim sTokens() As String
Dim Msgstring As String
On Error GoTo ExecuteSQL_Error sTokens = Split(SQL)
Set Cnn = New ADODB.Connection
Cnn.CursorLocation = adUseClient
Cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
On Error Resume Next
Cnn.BeginTrans
Cnn.Execute SQL
If Err.Number > 0 Then
Cnn.RollbackTrans
Else
Cnn.CommandTimeout
End If
Msgstring = sTokens(0) & _
" query successful"
Else
Set Rst = New ADODB.Recordset
Rst.Open Trim$(SQL), Cnn, _
adOpenKeyset, _
adLockOptimistic Set ExecuteSQL = Rst
Msgstring = "查询到" & Rst.RecordCount & _
" 条记录 "
End If
ExecuteSQL_Exit:
Set Rst = Nothing
Set Cnn = NothingExit FunctionExecuteSQL_Error:
Msgstring = "查询错误: " & _
Err.Description
Debug.Print Msgstring
Resume ExecuteSQL_Exit
End Function
UCase$(sTokens(0))) Then
On Error Resume Next
err.clear
Cnn.BeginTrans
Cnn.Execute SQL
If Err.Number > 0 Then
Cnn.RollbackTrans
Else
Cnn.CommandTimeout
End If
on error goto 0
Msgstring = sTokens(0) & _
" query successful"
我用你的方法试了,不行。调试时提示要求对象。(应该是他认不出CN).后来我把函数中的CN声明为全局变量,在事件中再调用此CN时还是报“要求对象”错误.
如果我在事件中再声明一个CN并给此CN赋值的(在此CN下进行事务处理)话,我想在执行函数时和函数中的CN冲突。故没有另行声明。
把我的问题说明白点:一个事件中要对几个表进行修改。而对每个表的修改都调用executesql()函数,且此函数中有一个数据库连接(CN)。为了做到一改全改,不改则全不改。做到数据统一.该怎么处理此事件。诚求朋友解答!!
Public Function executesql(ByVal Sql As String) As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Dim stokens() As String
On Error GoTo executesql_error
stokens = Split(Sql)
'ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=device"
cnn.Open connectstring
If InStr("INSERT,DELETE,UPDATE", UCase$(stokens(0))) Then
cnn.Execute Sql
Else
rst.Open Trim$(Sql), cnn, adOpenKeyset, adLockOptimistic
Set executesql = rst
End If
executesql_exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
executesql_error:
MsgBox "查询错误:" & Err.Description
Resume executesql_exit
End Function
Public Function connectstring() As String
connectstring = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=device"
End Function
Private Sub Command1_Click()
Dim txtsql As String
Dim mrc As New ADODB.Recordset
txtsql = "select id,bpart,state,storepart,usecount from devicetable where id= '" & Text1.Text & " ' and bpart='" & Combo3.Text & "'"
Set mrc = executesql(txtsql)
If mrc.EOF Then
MsgBox "此部门无此产品,请重新输入", vbOKOnly, "提示"
Text1.SetFocus
Text1.Text = ""
Else
If Trim(Label12.Caption) <> "可供使用" Then
MsgBox "此部门该产品不可借出,可能已被使用或者在修理", vbOKOnly + vbInformation, "提示"
Combo3.SetFocus
Else
mrc.Fields("usecount") = mrc.Fields("usecount") + 1
mrc.Fields("state") = "已借出"
mrc.Update
txtsql = "update devicetable set storepart='" & Combo4.Text & "' where id='" & Text1.Text & "'"
Set mrc = executesql(txtsql)
txtsql = "insert into borrowrecord(id,name,memo,bood,boot,boop,boip,boipl,boopl) values ('"
txtsql = txtsql & Text1.Text & "','"
txtsql = txtsql & Text2.Text & "','"
txtsql = txtsql & Text5.Text & "','"
txtsql = txtsql & Format(DTPicker1.Value, "yyyy-mm-dd") & "','"
txtsql = txtsql & Text4.Text & "','"
txtsql = txtsql & Combo3.Text & "','"
txtsql = txtsql & Combo4.Text & "','"
txtsql = txtsql & Combo1.Text & "','"
txtsql = txtsql & Combo2.Text & "')"
Set mrc = executesql(txtsql)
MsgBox "资产已成功借出.", vbOKOnly, "提示"
If MsgBox("是否现在打印借出凭条??", vbOKCancel, "提示信息") = vbOK Then
borrowcheckform.Show
End If
End If
End If
End Sub
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
On Error Resume Next
err.clear
Cnn.BeginTrans
Cnn.Execute SQL
If Err.Number > 0 Then
Cnn.RollbackTrans
Else
Cnn.CommandTimeout
End If
on error goto 0
Msgstring = sTokens(0) & _
" query successful"这样做啊.关键问题是,如果你插入,更新都用这个的话,我建意你改一下函数的参数,再加一个Boolean变量,在更新的插入时,如果成功,就为真,不成功为假.