Which is the preferred method

[Andy Flowers]

Josa,

Which method is the best way to update records in an sql database:-

Using the "Udate <table name> Set <field name> = <value> Where ......" or
using the ADO.Update method?

[José Roca]

 
Updating a single record you won't notice differences. If you have to update many records at the same time, then batch updates, parametized queries or stored procedures (if the server supports them) are faster.

[Andy Flowers]

When you say Batch update, do you mean using "recordset.update" with a locktype = adlLockBatchOptimistic? or using the SQL update as part of an sql string?

I've tried using Stored procedures with parameters but with no sucess. Do you happen to have any good examples of using SP using paremeters to and from the SP?

[José Roca]

 

When you say Batch update, do you mean using "recordset.update" with a locktype = adlLockBatchOptimistic? or using the SQL update as part of an sql string?

You have to open the recordset using adLockBatchOptimistic and the updates will be done in the local recordset, without making multiple round trips to the database. When you finish the updates, you have to call pRecordset.UpdateBatch %adAffectAll.

See: http://www.jose.it-berater.org/smfforum/index.php?topic=2605.0

I've tried using Stored procedures with parameters but with no sucess. Do you happen to have any good examples of using SP using paremeters to and from the SP?

Sorry, I don't have any examples.

[Andy Flowers]

Thank for your suggestions and sorry for mispelling your name.