请问一下各位,这个函数不是很明白,能讲解一下吗
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
这个里面 If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimisticSet ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
这三个地方是起的什么作用呢,我不是很明白,希望赐教!!!!谢谢了
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
这个里面 If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimisticSet ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
这三个地方是起的什么作用呢,我不是很明白,希望赐教!!!!谢谢了
UCase$(sTokens(0))) Then
----------------------------------------------------
判断查询语句是否完整,只能判断是否包含关键字rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
----------------------------------------------------
打开记录集Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
----------------------------------------------------
返回记录集与记录集查询到的记录数
UCase$(sTokens(0))) Then
-------------------------------------------------------
这里是检查sql语句的第一个单词,如果是INSERT,DELETE,UPDATE中的任何一个,就直接执行sql命令,此时是没有返回结果(数据集)的,因为分别是插入/删除/更新数据库操作。sTokens数组中存放的是经过split函数数据里的SQL语句,没个要素是SQL语句中的一个单词(以空格为分割符,或者是其他的,不是很清楚) rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
---------------------------------------------------------
rst是一个数据集,这是获得数据集的一个方法。具体参数含义可以看帮助Set ExecuteSQL = rst ----此句返回是将获得的数据集,即该函数(function执行返回的结果)
MsgString = "查询到" & rst.RecordCount & _
" 条记录 " ----------此句是生成一个字符串,描述该查询结果有多少条纪录。但如果要获得纪录数,应该先执行rst.movelast这个函数的功能是,判断SQL语句类型,如果是INSERT,DELETE,UPDATE就直接执行,不返回数据集(recordset),否则就获取数据集并作为函数结果返回。但这样写有问题。如果sql语句是“alter table ...."类型的语句,也会执行到rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic部分,但是这时是没有结果返回的,因为这不是查询语句而是修改数据表结构。除非你确认你的SQL语句只有INSERT/DELETE/UPDATE/select三种形式