As a newbie to Xojo, I might be doing this all wrong, but…
I am attempting to walk through a recordset and updating some fields in each record until EOF. The code similar to that shown below works fine in Access and ooBase.
When run in Xojo the result is this:
The FIRST record in the recordset gets its field contents updated with data that was intended for the last record. All other records remain untouched (ie not updated).
Am I missing something? Is this the wrong approach?
As an aside, I have also noticed that if I use a DataControl object to perform this operation, the end result is exactly the same as above. Is this a problem with MySQL or is it me?
Any advice gratefully received!
[code] Dim varStake As Double
Dim varPercent As Double
Dim db as new MySQLCommunityServer
db.DatabaseName = “CurrencyClub”
db.UserName = “root”
db.Password = “test”
if db.Connect then
Dim rsMembers As RecordSet
rsMembers = db.SQLSelect("SELECT * FROM members")
if rsMembers <> nil then
if(rsMembers.RecordCount <> 0) then
rsMembers.MoveFirst
While not rsMembers.EOF
varStake = val(rsMembers.Field("MemStakeValue").StringValue)
varPercent = 100*(varStake / varFund)
'msgBox "Member Stake: "& varStake & "Fund Tot: " & varFund & "Member Percent: " & varPercent
rsMembers.edit
rsMembers.Field("MemStakePercent").StringValue = Format(varPercent, "##.0000")
rsMembers.Field("MemTel1").StringValue = Cstr(rsMembers.Field("ID"))
rsMembers.update
rsMembers.MoveNext
WEND
rsMembers.Close
End If
else
//problem
MsgBox("DB error? " + db.ErrorMessage)
end if
Else
[/code]
It is a bit if a guess but I wonder if some value that you set in the first update causes it to go wrong? How do you identify the first record and last record to know what has happened? Is ID the primary key (and therefore unique)? Jym’s comments are sensible observations especially about performing the update, you could achieve the whole thing in a single SQL UPDATE statement but it looks like your code should work.
Thanks for the observations guys. Firstly, Jim, the table “members” has a primary key called “ID”. The only reason I was storing this ID value in to the field “MemTel1” was so that I could confirm to myself that the update was always writing to Record No.1 of the recordset. (ie record 1’s MemTel1 field would always be changed to the ID number of the latest record in the the set) - this was just for debug purposes.
I would love to accomplish this loop with a single SQL UPDATE command, but have not yet worked out how to build the command given the maths involved require a comparison to be made between a value in the member’s record (MemStakeValue) and an external value held in a local variable (varFund), and for the result of that comparison to then be written to the member’s record (MemStakePercent).
nevertheless, my code has worked fine in the past on other platforms…
Anyway, I would greatly appreciate any SQL UPDATE command examples that I could learn from!
Thanks again!
dim checkCurrentID as string = Cstr(rsMembers.Field("ID")) // break here and check this value
varStake = val(rsMembers.Field("MemStakeValue").StringValue) ...[/code]
I am curious about what’s wrong.
Then, remove the MoveFirst() as it does not work and have unpredictable results (should raise an exception for me).
Hi guys,
Many thanks for your suggestions. DOH!! the direct SQL call is so obvious, now that Jim has shown it to me. I feel like Homer… Thanks Jim, you’re a Champ!
ps. Rick, yes I did remove the MoveFirst line, and yes I have stepped through the code with break and all variables and DB field values check out correctly at run time, until the .update command when the “save” takes place on record #1 - always!