Hi all,
I am still a bit confused about recordsets. I have this piece of code
which is creating a recordset from a query and then trying to update
one field. The recordset is made up from two tables in the database,
but the field I am trying to update is only in one of the tables. I
thought the code below should work, but it fails silently. When I say
silently, I mean that at each point where I have a msgbox below it
displays with the expected contents and there are no exceptions. But
when I check the "newkey" field it is still empty.
Is it OK to do an update on a recordset that spans tables? I can't
see why it shouldn't work. Or is there another reason that the update
doesn't take?
Anyway, thanks for any guidance.
Ian.
--
Here's the code:
rs = db.SQLSelect("select user.username as user_username,
user.userid as user_userid, key.userid as key_userid, key.keyid as
key_keyid, key.newkey as key_newkey from user,key where
user_username='" + theUsername +"' and user_userid=key_userid")
if rs.RecordCount > 0 then
while not rs.eof
//if the newKey field is different from the tempNewKey above
(including if it is empty) then update it
rs.Edit
// ** note: msgbox on next line shows expected content, so I think I
have the expected recordset
MsgBox "rs user_username is: " + rs.Field
("user_username").Value + ": and user_userid is :" + rs.Field
("user_userid").Value + ": and key_userid is :" + rs.Field
("key_userid").Value + ":"
if rs.Field("key_newkey").Value <> tempnewKey then
// change the field's value
msgbox "key_newkey from database is different from
tempnewKey :" + rs.Field("key_newkey").Value + ":" + tempNewKey + ":"
rs.Field("key_newkey").Value = tempnewKey
msgbox "key_newkey is now :" + rs.Field
("key_newkey").Value + ":"
// update the recordset
rs.Update
msgbox "recordset updated"
else
// do nothing - the fields are the same
msgbox "No need to set a new newkey (value is> " +
tempNewKey + "<"
end if
'next record - there shouldn't actually be one
rs.movenext
wend
rs.close
msgbox "recordset closed"
end if
// Commit and close
db.Commit
msgbox "recordset update commited"
db.close
msgbox "database closed"
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
