对视图中的记录进行增加/修改/删除操作时出现一些疑问
可在界面上拉一个ADODC控件,一个DATAGRID,一个按钮
输入如下代码,可以看出它能正常反映出更新后的内容
Option Explicit
Dim cn As New ADODB.Connection
Dim Rs As New ADODB.RecordsetPrivate Sub Form_Load()
cn.Open "Provider=SQLOLEDB;Server=yzx;uid=sa;pwd=;database=northwind"
Rs.CursorLocation = adUseClient
Rs.Open "SELECT Orders.*, Customers.CompanyName " & _
"FROM Orders INNER JOIN Customers " & _
"ON Orders.CustomerID = Customers.CustomerID", cn, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = Rs
End SubPrivate Sub Form_Unload(Cancel As Integer)
Rs.Close
cn.Close
End SubPrivate Sub Command1_Click()
Dim Rs1 As ADODB.Recordset
'注释1
'Set Rs1 = cn.Execute("select * from Products") ' '注释2
'Rs.AddNew
'Rs!CustomerID = "WOLZA"
'Rs.Update
'注释3
Rs!CustomerID = "WOLZA"
Rs.Update Rs.Properties("Unique Table") = "Orders"
Rs.Properties("Resync Command") = "SELECT Orders.*, Customers.CompanyName " & _
"FROM Orders INNER JOIN Customers " & _
"ON Orders.CustomerID = Customers.CustomerID where Orders.OrderID=?"
Rs.Resync adAffectCurrent
Debug.Print Rs!CompanyName
End Sub
这时把注释2部分取消注释(注释3就可以把它们注释掉了),也可以看出能正常增加记录再把注释1取消注释,结果就无法增加记录了.弹出错误
实时错误 80040E23
数据存储中此行的键值已被更改或删除.现在本地行已被删除.但数据是增加进去了.测试后的经验是:如果用CONN去操作过其它表格记录后,再执行该内容就会出现如上错误.而我的程序原来所有的连接都是共用一个连接的,因此根本没法正常保存.
可在界面上拉一个ADODC控件,一个DATAGRID,一个按钮
输入如下代码,可以看出它能正常反映出更新后的内容
Option Explicit
Dim cn As New ADODB.Connection
Dim Rs As New ADODB.RecordsetPrivate Sub Form_Load()
cn.Open "Provider=SQLOLEDB;Server=yzx;uid=sa;pwd=;database=northwind"
Rs.CursorLocation = adUseClient
Rs.Open "SELECT Orders.*, Customers.CompanyName " & _
"FROM Orders INNER JOIN Customers " & _
"ON Orders.CustomerID = Customers.CustomerID", cn, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = Rs
End SubPrivate Sub Form_Unload(Cancel As Integer)
Rs.Close
cn.Close
End SubPrivate Sub Command1_Click()
Dim Rs1 As ADODB.Recordset
'注释1
'Set Rs1 = cn.Execute("select * from Products") ' '注释2
'Rs.AddNew
'Rs!CustomerID = "WOLZA"
'Rs.Update
'注释3
Rs!CustomerID = "WOLZA"
Rs.Update Rs.Properties("Unique Table") = "Orders"
Rs.Properties("Resync Command") = "SELECT Orders.*, Customers.CompanyName " & _
"FROM Orders INNER JOIN Customers " & _
"ON Orders.CustomerID = Customers.CustomerID where Orders.OrderID=?"
Rs.Resync adAffectCurrent
Debug.Print Rs!CompanyName
End Sub
这时把注释2部分取消注释(注释3就可以把它们注释掉了),也可以看出能正常增加记录再把注释1取消注释,结果就无法增加记录了.弹出错误
实时错误 80040E23
数据存储中此行的键值已被更改或删除.现在本地行已被删除.但数据是增加进去了.测试后的经验是:如果用CONN去操作过其它表格记录后,再执行该内容就会出现如上错误.而我的程序原来所有的连接都是共用一个连接的,因此根本没法正常保存.
'注释1
Rs1.CursorLocation = adUseClient
Rs1.Open "select * from Products", cn
你的rs在load 事件里面已经连接,在rs没有断开前,rs1的连接不允许?
建议在 cn 连接前判断cn是否是close
如果不是就close 再open
用OPEN的方法也是会出错的对:ZOU_SEAFARER(国际海员)
一个用户可以连接多次,只要打开RS1的连接不是原来的CN,则不会出错,我暂时也是这样处理的.
提出只想用一个连接来完成任务,主要目的是想继续用以前的一个连接走天下的程序体系.另一个目的是想求证是否是微软对Unique Table并没有完美的支持,还是我们理解不完整,不知道如何处理.
做成客户端处理数据 这个Modest(塞北雪貂 -- 偶最欣赏楼主的分) 已经说过了,呵呵
俺也不晓得了
你可以试试这个
Rs1.CursorLocation = adUseClient Rs1.Open "select * from Products", cn,3,2
Option Explicit
Dim cn As New ADODB.Connection
Dim Rs As New ADODB.RecordsetPrivate Sub Form_Load()
cn.Open "Provider=SQLOLEDB;Server=yzx;uid=sa;pwd=;database=northwind"
Rs.CursorLocation = adUseClient
Rs.Open "SELECT Orders.*, Customers.CompanyName " & _
"FROM Orders INNER JOIN Customers " & _
"ON Orders.CustomerID = Customers.CustomerID", cn, adOpenKeyset, adLockOptimistic
Set DataGrid1.DataSource = Rs
End SubPrivate Sub Form_Unload(Cancel As Integer)
Rs.Close
cn.Close
End SubPrivate Sub Command1_Click()
Dim Rs1 As New ADODB.Recordset
Rs1.CursorLocation = adUseClient
Rs1.Open "select * from Products", cn, adOpenKeyset, adLockOptimistic
' Set Rs1 = cn.Execute("select * from Products") 该行用以上内容代替
Rs.AddNew
Rs!CustomerID = "WOLZA"
Rs.Update
' Rs!CustomerID = "WOLZA"
' Rs.Update Rs.Properties("Unique Table") = "Orders"
Rs.Properties("Resync Command") = "SELECT Orders.*, Customers.CompanyName " & _
"FROM Orders INNER JOIN Customers " & _
"ON Orders.CustomerID = Customers.CustomerID where Orders.OrderID=?"
Rs.Resync adAffectCurrent
Debug.Print Rs!CompanyName
End Sub