It fails "silently" because you are not checking for errors anywhere. Many
implementations of SQL do not allow updating a record across multiple
tables. And it is especially problematic using a recordset, due to the way
the recordset has to construct the sql statement to do the update.
You will probably have to contstruct an SQL statement to do the update
separately from the recordset.
db.SQLExecute("UPDATE key SET newkey= "+ tempnewKey+ " WHERE ...")
Tim
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of
> Ian Piper
> Sent: Saturday, April 07, 2007 12:04 PM
> To: REALbasic NUG
> Subject: Updating a recordset which is made up from multiple tables
>
>
> 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>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 268.18.26/750 - Release Date:
> 4/6/2007 9:30 PM
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.26/750 - Release Date: 4/6/2007
9:30 PM
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
