Private Sub delete_Click()
On Error GoTo Err_delete_Click
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim str, strWhere, sql1, sql2 As String
Dim count As Integer
Dim constr As String
Set rst = New ADODB.Recordset
With con
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "G:\毕设\Soil.mdb; Persist Security Info=False"
.CommandTimeout = 5
.Open
If con.State = adStateOpen Then
MsgBox "打开数据库"
End If
End With
rst.CursorLocation = adUseClient '设置游标类型
rst.CursorType = adOpenStatic '设置游标位置 '获取页面查询条件
strWhere = ""
If Not IsNull(Me.年份text) Then
strWhere = strWhere & "土壤养分表.年份 like '*" & Me.年份text & "*'and "
End If
If Not IsNull(Me.土壤层次text) Then
strWhere = strWhere & "土壤养分表.[土壤层次(cm)] like '" & Me.土壤层次text & "'and "
End If
If Not IsNull(Me.处理方式text) Then
strWhere = strWhere & "土壤养分表.处理方式 like '" & Me.处理方式text & "'and "
End If
If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 4)
End If If strWhere = "" Then
'没有条件
Me.土壤养分表查询_子窗体.Form.RecordSource = "select * from 土壤养分表 "
MsgBox ("请根据条件选择需要删除的信息!")
Else
'有条件
MsgBox ("删除符合条件的信息。")
sql1 = "select * from 土壤养分表 where " & strWhere & ""
Me.土壤养分表查询_子窗体.Form.RecordSource = sql1
rst.CursorLocation = adUseClient '设置游标类型
rst.Open sql1, con, adOpenStatic, adLockOptimistic
If rst.EOF And rst.BOF Then
MsgBox "记录为空! ", , "系统提示 "
Else
rst.MoveLast
rst.MoveFirst
count = rst.RecordCount
MsgBox (count)
If count <= 0 Then
MsgBox ("数据库中无该记录信息!")
Else
sql2 = "delete from 土壤养分表 where " & strWhere & ""
rst.Open sql2, con, adOpenStatic, adLockOptimistic
MsgBox ("删除成功")
End If
End If
Set rst = Nothing
Set con = Nothing
End If
Exit_delete_Click:
Exit Sub
Err_delete_Click:
MsgBox Err.Description
Resume Exit_delete_Click
End Sub
On Error GoTo Err_delete_Click
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim str, strWhere, sql1, sql2 As String
Dim count As Integer
Dim constr As String
Set rst = New ADODB.Recordset
With con
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "G:\毕设\Soil.mdb; Persist Security Info=False"
.CommandTimeout = 5
.Open
If con.State = adStateOpen Then
MsgBox "打开数据库"
End If
End With
rst.CursorLocation = adUseClient '设置游标类型
rst.CursorType = adOpenStatic '设置游标位置 '获取页面查询条件
strWhere = ""
If Not IsNull(Me.年份text) Then
strWhere = strWhere & "土壤养分表.年份 like '*" & Me.年份text & "*'and "
End If
If Not IsNull(Me.土壤层次text) Then
strWhere = strWhere & "土壤养分表.[土壤层次(cm)] like '" & Me.土壤层次text & "'and "
End If
If Not IsNull(Me.处理方式text) Then
strWhere = strWhere & "土壤养分表.处理方式 like '" & Me.处理方式text & "'and "
End If
If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 4)
End If If strWhere = "" Then
'没有条件
Me.土壤养分表查询_子窗体.Form.RecordSource = "select * from 土壤养分表 "
MsgBox ("请根据条件选择需要删除的信息!")
Else
'有条件
MsgBox ("删除符合条件的信息。")
sql1 = "select * from 土壤养分表 where " & strWhere & ""
Me.土壤养分表查询_子窗体.Form.RecordSource = sql1
rst.CursorLocation = adUseClient '设置游标类型
rst.Open sql1, con, adOpenStatic, adLockOptimistic
If rst.EOF And rst.BOF Then
MsgBox "记录为空! ", , "系统提示 "
Else
rst.MoveLast
rst.MoveFirst
count = rst.RecordCount
MsgBox (count)
If count <= 0 Then
MsgBox ("数据库中无该记录信息!")
Else
sql2 = "delete from 土壤养分表 where " & strWhere & ""
rst.Open sql2, con, adOpenStatic, adLockOptimistic
MsgBox ("删除成功")
End If
End If
Set rst = Nothing
Set con = Nothing
End If
Exit_delete_Click:
Exit Sub
Err_delete_Click:
MsgBox Err.Description
Resume Exit_delete_Click
End Sub
rst.Open sql2, con, adOpenStatic, adLockOptimistic
换成cn.Execute (sql)
你的MsgBox ("删除成功")不是掩耳盗铃、自欺欺人吗?什么都不判断就msgbox?Private Sub delete_Click()
On Error GoTo Err_delete_Click
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim str, strWhere, sql1, sql2 As String
Dim count As Integer
Dim constr As String
''''Set rst = New ADODB.Recordset''上面都new了新实例了,这里就免了吧
With con
constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "G:\毕设\Soil.mdb; Persist Security Info=False"
.CommandTimeout = 5
.Open constr
If con.State<>1 Then
MsgBox "连库错误!原因:" & err.description
set rst=nothing: set con=nothing
exit sub
End If
End With '获取页面查询条件
strWhere = ""
If len(trim(Me.年份text))>0 Then
strWhere = " WHERE 土壤养分表.年份 like '*" & Me.年份text & "*'"
End if
If len(trim(Me.土壤层次text))>0 Then
if len(strWhere)=0 then
strWhere =" WHERE 土壤养分表.[土壤层次(cm)] like '" & Me.土壤层次text & "'"
else
strWhere = strWhere & " AND 土壤养分表.[土壤层次(cm)] like '" & Me.土壤层次text & "'"
end if
End If
If len(trim(Me.处理方式text))>0 Then
if len(strWhere)=0 then
strWhere=" WHERE 土壤养分表.处理方式 like '" & Me.处理方式text & "'"
else
strWhere = strWhere & " AND 土壤养分表.处理方式 like '" & Me.处理方式text & "'"
end if
End If If strWhere = "" Then
'没有条件
Me.土壤养分表查询_子窗体.Form.RecordSource = "select * from 土壤养分表 "
MsgBox ("请根据条件选择需要删除的信息!")
Else
'有条件
MsgBox ("删除符合条件的信息。")
sql1 = "select * from 土壤养分表 " & strWhere
Me.土壤养分表查询_子窗体.Form.RecordSource = sql1
rst.Open sql1, con, 1,1
If rst.EOF And rst.BOF Then
MsgBox "记录为空! ", , "系统提示 "
rst.close
Else
count = rst.RecordCount
MsgBox count
rst.close
'If count <= 0 Then''rst.EOF And rst.BOF判断后这个判断是多余的
' MsgBox "数据库中无该记录信息!"
'Else
sql2 = "delete from 土壤养分表 " & strWhere
call con.beginTrans()'开始一个事务,防止程序出错乱删除
err.clear
con.execute sql
''''''''''''''''''rst.Open sql2, con, adOpenStatic, adLockOptimistic
if err=0 and con.errors.count=0 then
call con.commitTrans()'无错执行
MsgBox "删除成功",64,"恭喜"
else
call con.rollbacktrans()'有错回滚
MsgBox "删除失败!原因:" & err.description,16,"囧~~"
end if
'End If
End If
Set rst = Nothing
con.close: Set con = Nothing'要先关后释放
End If
Exit_delete_Click:
Exit Sub
Err_delete_Click:
MsgBox Err.Description
'''''''''Resume Exit_delete_Click'你写这句是嫌打字不累吧,哈哈
End Sub
rst.Close
sql2 = "delete from 土壤养分表 where " & strWhere & ""
con.Execute sql2
MsgBox ("删除成功")
End If1 删除、更改等 SQL 命令不要用记录集查询。
2 你原来打开 rst 查询了记录,没有关闭。再次打开引发了错误。
3 实际上,删除前不需要查询。如果想知道删除结果,可以用连接对象 Execute 方法的 AffectedRecords 参数取得删除的记录数。
:"记录为空! ", 无法对数据表中的数据进行修改,不知道哪里出了错误!(初学VB,有很多不懂的地方,还希望各位能多多指教,不胜感激!!!)
sql1 = "select * from 土壤养分表 " & strWhere
debug.print sql1'贴出来,看看SQL语句有没有错误
debug.print con.state'是不是1?不是的话,是没连库
If Not IsNull(Me.年份text) Then
strWhere = strWhere & "土壤养分表.年份 like '*" & Me.年份text & "*'and "
End If
If Not IsNull(Me.土壤层次text) Then
strWhere = strWhere & "土壤养分表.[土壤层次(cm)] like '" & Me.土壤层次text & "'and "
End If
If Not IsNull(Me.处理方式text) Then
strWhere = strWhere & "土壤养分表.处理方式 like '" & Me.处理方式text & "'"
End If If strWhere = "" Then
'没有条件
Me.土壤养分表查询_子窗体.Form.RecordSource = "select * from 土壤养分表 "
MsgBox ("请根据条件选择需要删除的信息!")
Else
'有条件
strwhere="where " & strwhere
sql1 = "select * from 土壤养分表 " & strwhere
con.Execute sql1
msgbox ("删除成功")
看下可以不?
Me.土壤养分表查询_子窗体.Form.RecordSource = sql1