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?

+3


source to share


1 answer


I checked your code, everything was fine and only one line was updated. I just want to offer you an easy way to check if a record exists or not. it 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

      

+2


source







All Articles