Dim mrc As ADODB.Recordset
Dim myBook As Variant
Dim mcclean As Boolean ---全局Private Sub Form_Load()
Dim txtSQL As String
Dim MsgText As String
txtClassno.Enabled = False
comboGrade.Enabled = False
txtDirector.Enabled = False
txtClassroom.Enabled = False
txtSQL = "select * from class_Info "
Set mrc = ExecuteSQL(txtSQL, MsgText)
mrc.MoveFirst
Call viewData
myBook = mrc.Book
mcclean = True
End Sub
然后另一个过程
Private Sub updateCommand_Click()
Dim txtSQL As String
Dim MsgText As Stringmrc.Delete txtSQL = "select * from class_info where class_No = '" & Trim(txtClassno.Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF = False Then
MsgBox "班号重复,请重新输入!", vbOKOnly + vbExclamation, "警告"
mrc.Close
txtClassno.SetFocus
Else
mrc.AddNew
mrc.Fields(0) = Trim(txtClassno.Text)
mrc.Fields(1) = Trim(comboGrade.Text)
mrc.Fields(2) = Trim(txtDirector.Text)
mrc.Fields(3) = Trim(txtClassroom.Text)
mrc.Update
MsgBox "修改班级信息成功!", vbOKOnly + vbExclamation, "警告"
....
为什么表里有一条记录时,程序有时可以通过,有时不行。多条记录没有通过Public Function ConnectString() _
As String
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\数据库毕设\db1.mdb;Persist Security Info=False"
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.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
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
Dim myBook As Variant
Dim mcclean As Boolean ---全局Private Sub Form_Load()
Dim txtSQL As String
Dim MsgText As String
txtClassno.Enabled = False
comboGrade.Enabled = False
txtDirector.Enabled = False
txtClassroom.Enabled = False
txtSQL = "select * from class_Info "
Set mrc = ExecuteSQL(txtSQL, MsgText)
mrc.MoveFirst
Call viewData
myBook = mrc.Book
mcclean = True
End Sub
然后另一个过程
Private Sub updateCommand_Click()
Dim txtSQL As String
Dim MsgText As Stringmrc.Delete txtSQL = "select * from class_info where class_No = '" & Trim(txtClassno.Text) & "'"
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF = False Then
MsgBox "班号重复,请重新输入!", vbOKOnly + vbExclamation, "警告"
mrc.Close
txtClassno.SetFocus
Else
mrc.AddNew
mrc.Fields(0) = Trim(txtClassno.Text)
mrc.Fields(1) = Trim(comboGrade.Text)
mrc.Fields(2) = Trim(txtDirector.Text)
mrc.Fields(3) = Trim(txtClassroom.Text)
mrc.Update
MsgBox "修改班级信息成功!", vbOKOnly + vbExclamation, "警告"
....
为什么表里有一条记录时,程序有时可以通过,有时不行。多条记录没有通过Public Function ConnectString() _
As String
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\数据库毕设\db1.mdb;Persist Security Info=False"
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.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
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
据说ado对数据库的操作很不稳定
-----------------------------------------------
AdOpenForwardOnly (默认值)打开仅向前类型游标。
AdOpenKeyset 打开键集类型游标。
AdOpenDynamic 打开动态类型游标。
AdOpenStatic 打开静态类型游标。
-----------------------------------------------LockType 可选
-------------------------------------------------
AdLockReadOnly (默认值)只读 — 不能改变数据。
AdLockPessimistic 保守式锁定(逐个) — 提供者完成确保成功编辑记录所需的工作,通常通过在编辑时立即锁定数据源的记录。
AdLockOptimistic 开放式锁定(逐个) — 提供者使用开放式锁定,只在调用Update 方法时才锁定记录。
AdLockBatchOptimistic 开放式批更新—用于批更新模式(与立即更新模式相对)。
-------------------------------------------------
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
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function'可以看出InStr("INSERT,DELETE,UPDATE",UCase$(sTokens(0)))为真时,反而查不到任何结果;为假时倒能查出结果,这样导致整个Function ExecuteSQL模块都有问题。又,哪一个SQL语会在前面写上“INSERT,DELETE,UPDATE”三个词(这是上面的stokens(0))?
InStr("INSERT,DELETE,UPDATE",UCase$(sTokens(0)))为真时,执行的是更新数据,为假时,执行的是查询数据
不理解的是 sTokens = Split(SQL) 什么意思
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
Trim$()什么意思
的问题不用时把它清除
再给楼主指出问题所在!
Public Function executesql(ByVal sql As String, ByVal msgstring As String) As ADODB.Recordset
'传递参数SQL传递查询语句,MSGBOX传递查询信息
'自身以一个数据集对象的形式返回
Dim cnn As ADODB.Connection
'定义连接
Dim rst As ADODB.Recordset
'定义字符串
Dim stokens() As String
'错误处理
On Error GoTo executesql_error
stokens = Split(sql) '用SPLIT函数产生一个包含各个子串的数组
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.CursorLocation = adUseClient
rst.Open Trim$(sql), cnn, adOpenStatic, adLockOptimistic, adCmdText
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 FunctionPrivate Sub updateCommand_Click()
Dim txtSQL As String
Dim MsgText As String
dim mrcc as adodb.recordset——————再定义一个记录集mrc.Delete txtSQL = "select * from class_info where class_No = '" & Trim(txtClassno.Text) & "'"
Set mrcc = ExecuteSQL(txtSQL, MsgText)
If mrcc.EOF = False Then
MsgBox "班号重复,请重新输入!", vbOKOnly + vbExclamation, "警告"
mrcc.Close ——————————————问题就在此,你在这里把记录集"mrc"关闭了。请问后面的操作又如何进行,又怎么样能把数据写进去呢?
txtClassno.SetFocus
Else
mrc.AddNew
mrc.Fields(0) = Trim(txtClassno.Text)
mrc.Fields(1) = Trim(comboGrade.Text)
mrc.Fields(2) = Trim(txtDirector.Text)
mrc.Fields(3) = Trim(txtClassroom.Text)
mrc.Update
MsgBox "修改班级信息成功!", vbOKOnly + vbExclamation, "警告"
....
为什么表里有一条记录时,程序有时可以通过,有时不行。多条记录没有通过
if instr("INSERT",UCase$(SQL) or instr("DELETE",UCase$(SQL) or instr("UPDATE",UCase$(SQL) then
cnn.Execute SQL
MsgString = " query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
[email protected]
你再仔细看一下我写给你的函数。会发现与你上面的不同的。我在在线等你发源代码过来
bopite(落泪的戏子) 怎么不在
'传递参数SQL传递查询语句,MSGBOX传递查询信息
'自身以一个数据集对象的形式返回
Dim cnn As ADODB.Connection
'定义连接
Dim rst As ADODB.Recordset
'定义字符串
Dim stokens() As String
'错误处理
On Error GoTo executesql_error
stokens = Split(sql) '用SPLIT函数产生一个包含各个子串的数组
Set cnn = New ADODB.Connection '创建连接
cnn.Open connectstring '打开连接 if instr("INSERT",UCase$(SQL) or instr("DELETE",UCase$(SQL) or instr ("UPDATE",UCase$(SQL) then
cnn.Execute SQL
MsgString = " query successful"
executesql =False '指明执行不是下述的查询,而且没有查到记录集
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
Set rs = rst
executesql =true '指明执行是查询,而且查到了记录集
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End Ifexecutesql_exit:
Set rst = Nothing '清空数据对象
Set cnn = Nothing '中断连接
Exit Function
executesql_error:
msgstring = "查询错误:" & Err.Description '错误类型判断
executesql =False
Resume executesql_exit
End Function