updating a db with many values.

[scroce]

I have a form with about 30 items a user could potentially update, which after they hit "submit" will update a table called tblMembers. I've been using a command object with the SQL update function, but in this case it seems like the SQL will become unwieldy. For example, this mess of SQL on a similar form that updates link information:

strUpdateSQL = "UPDATE tblLinks SET tblLinks.LinkURL ='" & Request.Form ("txtLinkURL&quot & "'" & ", tblLinks.LinkName ='" & Request.Form ("txtLinkName&quot & "'" & ", tblLinks.LinkDescription = '" & Request.form ("cboLinkDescrip&quot & "'" & "WHERE tblLinks.LinkID=" & session("strLinkID&quot & ""

only updates three fields. Imagine if there were 27 more fields being requested from the form!!!

Is there a cleaner, more economical way of doing this, possibly with an array or something? How much more water would there be in the ocean if it weren't for sponges?

 

[Bastien]

try looking at <b>recordset.UpdateBatch adAffectAll</b> using the cursor type adLockBatchOptimistic...

this will update all affected records retrieved from a recordset.

hth

Bastien Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[scroce]

ok - it seems that this will work. It's still long, but I think overall it makes for slightly cleaner code in my case.

Thanks for the tip! How much more water would there be in the ocean if it weren't for sponges?