记录集中只有1行，但表中的所有行都已更新 [英] Only 1 row in recordset but all rows in table get updated

问题描述

查询检索到一条记录，该记录已由recordcount确认，但表中的每一行都已更新

The query retrieves a single record as is confirmed by the recordcount but every single row in the table gets updated

我正在使用vb6和ms ado 2.8

I am using vb6 and ms ado 2.8

Firebird版本为2.5.4.26856（x64）。

The Firebird version is 2.5.4.26856 (x64).

Firebird ODBC驱动程序2.0.3.154

Firebird ODBC driver 2.0.3.154

计算机是Windows 7家庭版64位

The computer is windows 7 home edition 64 bit

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim cs As String
Dim dbPath As String

dbPath = "c:\Parkes\Parkes.fdb"
cs = "DRIVER={Firebird/Interbase(r) Driver}; DBNAME=localhost:" & dbPath & "; UID=SYSDBA; PWD=masterkey;"
cn.ConnectionString = cs
cn.Open

Dim sQuery As String

sQuery = "select memo from clients where clientID = 10021 "
rs.Open sQuery, cn, adOpenStatic, adLockOptimistic

If rs.BOF <> True Or rs.EOF <> True Then
'putting msgbox rs.recordcount here confirms only 1 record in recordset
    rs.Movefirst
    rs.Fields("memo") = "blah"
    rs.Update

End If

Set rs = Nothing
Set cn = Nothing

如果我也通过选择第二列来稍微更改查询，则客户端姓氏将仅编辑姓氏列中与clientid为10021的行具有相同值的行。 / p>

If I alter the query slightly by also selecting a second column, the client surname then only rows with the same value in the surname column as that of of the row where the clientid is 10021 get edited.

sQuery = "select memo, surname from clients where clientID = 10021 "

当记录集仅包含一行时，我不明白应编辑多于一行的内容

I cannot understand how more than one row should be edited when the recordset contains only a single row

编辑：已经在网上阅读了一些，这是我对正在发生的事情的理解。
似乎update方法基于记录集中的选定列来标识要更新的记录。
因此，如果选择字段a，b，c，d并正在更新字段a，它将仅更新数据库中a，b，c，d的值与记录集中的值匹配的记录。
确保仅更新一条记录的最佳方法是在所选字段中包括主键。
因此，如果我按如下所示编写查询，则由于clientID列包含唯一值，因此仅更新了一条记录。

Having read around the web a bit this is my understanding of what is happening. It seems that the update method identifies which records to update based on the selected columns in the recordset. So if you select fields a,b,c,d and are updating field a, it will only update records in the database whose values for a,b,c,d match those in the recordset. The best way to ensure that you only update a single record is to include the primary key in the selected fields. So if I had written my query as in the line below, only a single record would have been updated because the clientID column contains unique values.

sQuery = "select memo, clientID from clients where clientID = 10021 "

考虑一下是有道理的，但是根据我的经验，我最初编写查询的方式似乎可以与其他数据库配合使用，还是我做错了？

It makes sense thinking about it but the way I wrote the query originally seems to work fine, in my experience, with other databases or am I wrong?

推荐答案

我测试了您的代码，一切都很好，只更新了一行。我只想建议您一种简单的方法来检查记录是否存在。可能是这样的：

i tested your code everything was fine and only update one row. i only want to suggest you a simple way to check whether record exist or not. that could be like this :

if rs.rows.count > 0 then
   ' no need to move recordset to first by default its on the first row
end if

这篇关于记录集中只有1行，但表中的所有行都已更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！