Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Oct 23, 2018 11:59 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: MYSQL : issue with rs.edit within a rs.eof loop
PostPosted: Mon Apr 08, 2013 4:42 am 
Offline

Joined: Wed Oct 31, 2012 11:21 pm
Posts: 12
Hi all,

looking for some help here....the scenario is there is an update for a data, therefore need to scan the table for the old data, and change the value to the new data......using rs.edit for editting, and using rs.eof for the loop process

also fyi, product ID is not the index ID, just to clarify on that..

the code is :
********************************
sql = "select * from product_info where prd_id ='"+str(p_id_old)+"' " //** p_id_old is the old product ID reference.
dim rs as recordset = db.SQLSelect(sql)

while not rs.eof

rs.edit
rs.field("prd_id ").StringValue = vivpid(ps).Text //**(replaced old ID whit the new id taken from a textfield)
rs.Update

if db.Error then
MsgBox db.ErrorMessage
end if

msgbox "successful"
// msgbox "successful2" (this msgbox when enabled does something strange)

rs.MoveNext

wend

db.Commit

rs.close
********************************

so, for this example, there are 3 records, found...and when this action is triggered...the msgbox "successful" repeats 3 times, which seems to point that it was able to locate all 3 records and update it...but when the DB is checked....only the 1st record is changed....the other 2 didnt change, but no popup error for failure.even placing the db.commit in the loop doesnt help too.

also, if i enable the "msgbox "successful2"" , then strangely this time around da 1st and 2nd record is changed.....which is rather weird....i have no idea why the msgbox triggers the data editing, but if there were 100+ records, i cant place and have the msgbox repeat 100+ time too....

any suggestions? or is this code wrong somehow? appreciate any help in regards to this issue, as it is a bit strange....and once again thanks.


Top
 Profile  
Reply with quote  
 Post subject: Re: MYSQL : issue with rs.edit within a rs.eof loop
PostPosted: Mon Apr 08, 2013 9:51 am 
Offline
User avatar

Joined: Fri Sep 30, 2005 11:48 am
Posts: 3554
Location: Lenexa, KS
Hhhmm...well, anytime you have a loop like this you have to look and see if you can do the same thing via an SQL Update statement.

sql = "Update product_info set prd_id = '" + vivpid(ps).Text + "' WHERE prd_id = '" + str(p_id_old) + "';"
db.sqlexecute sql

if db.error then
msgbox db.errormessage
else
db.commit
end


The recordset.update statement is very costly in terms of speed and I don't see anything in your code that can't done in one shot in the database.

_________________
Bob K.

A blog about being a Real Studio/Xojo developer at http://www.bkeeneybriefs.com


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group