Update several records

[Ryan_Hartz]

Hi. Could you take a look at my code below. Trying to update all records in the table where the Email = the selected email address in the listbox. The table can contain several instances of this same email address, and I’d like to update all. This is updating, but only the first record. I’ve also tried a Do Until rs.EOF… Loop, but I may not have set that up correctly either

[code] dim sql as String

sql = “SELECT * FROM emailresults WHERE Email LIKE '%” + lbxStudents.Cell(lbxStudents.ListIndex, 0) + “%’”

dim rs as RecordSet = mDb.SQLSelect(sql)

While not rs.EOF

rs.Edit

rs.Field("RemoveStudent").StringValue = "1"

rs.Update
mDb.Commit

rs.MoveNext

Wend

rs.Close[/code]

[Sascha_S]

Why dont you just UPDATE all records with an Email LIKE ‘%…%’ ?
1 Statement

[Tim_Parnell]

If you’re updating the database, Sascha has it.

UPDATE emailresults SET honorRoll = 'True' WHERE  email = 'goodstudent@myschool.edu';

You provided a SELECT statement, so I’m not sure what you’re really trying to do (and I can’t make you a more specific query.)
Hope this helps

[Ryan_Hartz]

OMG wow that is much easier, and it works! Thanks guys!

[Ryan_Hartz]

That’s not a swear word. Types in O-M-G

[Tim_Parnell]

I’m glad it worked for you!

The language filter has been complained about several times in the never ending thread. Don’t worry about it

[Sascha_S]

[quote=103644:@Tim Parnell]If you’re updating the database, Sascha has it.

UPDATE emailresults SET honorRoll = 'True' WHERE  email = 'goodstudent@myschool.edu';

You provided a SELECT statement, so I’m not sure what you’re really trying to do (and I can’t make you a more specific query.)
Hope this helps :)[/quote]

Thank you Tim. I was on my iPad on the go and had no time to write an example.

[Rick_Araujo]

Avoid constructions like " UPDATE … LIKE ‘%einstein@caltech.com%’ " or you can affect wrongly records like frank.einstein@caltech.com.de damaging your data unintentionally.

[Sascha_S]

@103697:@Rick Araujo

Avoid constructions like " UPDATE … LIKE ‘%einstein@caltech.com%’ " or you can affect wrongly records like frank.einstein@caltech.com.de damaging your data unintentionally.

Plus, use PreparedStatements if you allow user Input in Querries s

[Todd_Fletcher]

Can this be used to update every record with a computed value from the same row? I want to add a field what is an MD5 signature of a blob so that I don’t have to push blob data around to check for matches.

My code is something like this:

[code]

sql = "SELECT * FROM mytable"
data = mDB.SQLSelect(sql)

If mDB.Error Then
  MsgBox("DB Error: " + mDB.ErrorMessage)
End If

while not data.EOF
data.Edit
If Not mDB.Error Then
data.Field(“Signature”).StringValue = EncodeBase64(Crypto.MD5(data.Field(“sContents”).StringValue))
data.Update
If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
End If
Else
MsgBox("DB Error: " + mDB.ErrorMessage)
End If
data.MoveNext
wend

data.Close

[/code]

Replace it with something like you have suggested in SQL statement like?

    sql = "UPDATE mytable SET Signature = '" + EncodeBase64(Crypto.MD5(data.Field("sContents").StringValue)) + "'"
    mDB.SQLExecute(sql)

Would that run through all the rows and update each one?

[Tim_Hare]

No, that would set them all to the same value. Probably not what you want.

[Todd_Fletcher]

What is the right way to loop through the recordset and update each row? My above loop gets the same ‘first record only’ changed.

[Tim_Hare]

Your loop needs to have a data.Update before data.MoveNext.

[Todd_Fletcher]

That makes good theoretically sense. But my code does have the movenext and it still updates only the first record.

This statement from the help…

MySQL cannot guarantee the contents of a RecordSet after issuing an Update call (after having previously called Edit). This means you should not try to modify the contents of a RecordSet in a loop. Instead select just the single record you wish to modify.

… seems to say you can’t update more than one record at a time? Missing something.

[Peter_Truskier]

@165261:@Todd Fletcher

Can this be used to update every record with a computed value from the same row? I want to add a field what is an MD5 signature of a blob so that I don’t have to push blob data around to check for matches.

Unless I misunderstand your requirement, Todd, I’d suggest getting the database server to do as much as possible for you. It’s been a long time since I used mySQL for anything, but it looks like it has built-in support for a bunch of crypto functions. (At least the linked-to version does…)

These would permit you to execute an UPDATE statement like this: UPDATE mytable SET Signature = MD5(sContents);, which would do it in one go…

[Todd_Fletcher]

@165307:@Peter Truskier

These would permit you to execute an UPDATE statement like this: UPDATE mytable SET Signature = MD5(sContents);, which would do it in one go…

Would that update all the records in the table - setting every signature with the MD5 of contents?

[Peter_Truskier]

@165309:@Todd Fletcher

Would that update all the records in the table - setting every signature with the MD5 of contents?

Yes; I believe it would. It should be easy enough to test.

You could, of course, add a “WHERE” clause if you didn’t want to affect all of them.

[Todd_Fletcher]

OKAY. So this works:

UPDATE tblmytable SET Signature = BASE64_ENCODE(MD5(sContents))

I had to add a Base64_encode function to the database, but this updated all the records and gave me my MD5 64encode hash. sweet! THANKS!

[Peter_Truskier]

@165313:@Todd Fletcher

I had to add a Base64_encode function to the database, but this updated all the records and gave me my MD5 64encode hash.

I figured you might want to do something like that. I’m glad it worked for you.

[Todd_Fletcher]

yeah. I definitely prefer SQL statements - leveraging the power of the SQL server over brushing through loops and the like. I once knew a DB guy that could work magic with SQL statements. I should have held onto his info. I took DB classes in college for my IS degree but DB admin made me want to throw up. smile