Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Thu Jan 17, 2019 5:03 pm

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 

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.field("prd_id ").StringValue = vivpid(ps).Text //**(replaced old ID whit the new id taken from a textfield)

if db.Error then
MsgBox db.ErrorMessage
end if

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





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.

Reply with quote  
 Post subject: Re: MYSQL : issue with rs.edit within a rs.eof loop
PostPosted: Mon Apr 08, 2013 9:51 am 
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

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

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:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group