更新计算字段的ADO记录集

I'm using the following sql for an ADO recordset against a SQLServer backend in VB6:

我在VB6中使用以下sql作为针对SQLServer后端的ADO记录集：

select c.name, taxid=
   case when exists(select 1 from sometable where fld='abc') 
   then c.SSN else null end

When I try to update the taxid field in a row within the recordset locally ADO complains with the error "Multiple-step operation generated errors. Check each status value." I assume it's bothered by the fact that the taxid field is coming from a calculated field and not a raw table column. For my purposes I'm never going to be persisting these changes back to the database so I'm looking for a way to tell ADO that have no intent to persist changes so that it will allow me to change the data locally.

当我尝试更新记录集中的行中的taxid字段时，ADO会抱怨错误“多步操作生成错误。请检查每个状态值”。我认为由于出租车字段来自计算字段而不是原始表列而受到困扰。出于我的目的，我永远不会将这些更改持久化回数据库，因此我正在寻找一种方法来告诉ADO无意保留更改，以便允许我在本地更改数据。

1 个解决方案

#1

I think that @HK1's suggestion is a good one, though I'm not sure what happens to your ability to alter any of the values in the recordset whether the column you're trying to update is computed or not. It's been a long time since I played with classic ADO but if the recordset is disconnected it may become read only at that point.

我认为@ HK1的建议很好，但是我不确定你是否能够改变记录集中的任何值，无论你要更新的列是否被计算。自从我使用经典ADO以来已经很长时间了，但如果记录集断开连接，它可能会在那时只读。

But if you have no interest in using the recordset to perform updates, and you need to alter values locally, perhaps you should consider storing the results in a local array first? That way you can minimize the locking and cursor options of the recordset, for example, and immediately close the recordset and free up those resources.

但是如果你没有兴趣使用记录集来执行更新，并且你需要在本地更改值，那么你应该考虑先将结果存储在本地数组中吗？这样，您可以最小化记录集的锁定和游标选项，例如，立即关闭记录集并释放这些资源。

rs.Open cmd, conn, adOpenForwardOnly, adLockReadOnly

Dim MyArray
MyArray = rs.GetRows()

rs.Close: set rs = nothing

Now you can manipulate MyArray however you want...

现在你可以随心所欲地操纵MyArray ......