Only 1 row in the recordset, but all rows in the table are being updated
The query retrieves one record, which is confirmed by the record number, but every row in the table is updated
I am using vb6 and ms ado 2.8
Firebird version is 2.5.4.26856 (x64).
Firebird ODBC Driver 2.0.3.154
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
If I modify the query slightly to select the second column, the client's last name will then only contain rows with the same value in the last name column as the row where client client 10021 receives the edit.
sQuery = "select memo, surname from clients where clientID = 10021 "
I cannot figure out how to edit multiple lines if the recordset contains only one line
EDIT: After reading a bit on the net this is my understanding of what's going on. The update method seems to determine which records to update based on the selected columns in the recordset. So if you select fields a, b, c, d and update field a, it will only update the records in the database whose values for a, b, c, d match the values in the recordset. The best way to make sure you only update one record is to include the primary key in the selected fields. So if I wrote my query like in the line below, only one record would be updated because the clientID column contains unique values.
sQuery = "select memo, clientID from clients where clientID = 10021 "
It makes sense to think about it, but how I wrote the query initially seems to work fine in my experience with other databases or am I wrong?
source to share