I need to update more than 1 record at a time in a sql server table.



I have tried a couple of different methods, niether seem to work.



Method 1:



a) SELECT all records that need updating into a recordset

b) loop through the recordset using rsUpdate.fields("field_name") = "updated" to change the value of each field in the recordset.



this produces:

ADODB.Recordset error '800a0cb3' 



Object or provider is not capable of performing requested operation. 







Method 2:



a) SELECT all records that need updating as above

b) Make an array of these records

c) loop through the array, building a SQL UPDATE statement for each array element and using an ADODB.Command to execute each SQL
statement



this produces:

Microsoft OLE DB Provider for SQL Server error '80040e31' 



Timeout expired 





Does anyone know the best way to update more than one record/field at a time?



Help much appreciated!





Jon R

stick with method 2, but don't select anything... 



i'm assuming your updating these records with some info from a previous

page...



build one sql block statement with each update in it and

command.execute(sql) once.  this will save you tons of time in network

calls..



your sql (assuming, again, sql server) should look something like this:



sql =3D "SET NOCOUNT ON " & vbcrlf

sql =3D sql & "BEGIN " & vbcrlf

sql =3D sql & "   UPDATE table SET field =3D " & request(field1) & " 

WHERE id =3D

" & request(id1) " & vbcrlf

sql =3D sql & "   UPDATE table SET field =3D " & request(field2) & " 

WHERE id =3D

" & request(id2) " & vbcrlf

sql =3D sql & "   UPDATE table SET field =3D " & request(field3) & " 

WHERE id =3D

" & request(id3) " & vbcrlf

sql =3D sql & "END " & vbcrlf

sql =3D sql & "SET NOCOUNT OFF " & vbcrlf

=09

conn.execute(sql)



shane blake



-----Original Message-----

From: Jon Ratcliffe [mailto:jonr@k...]

Sent: Monday, June 18, 2001 7:34 AM

To: ASP Databases

Subject: [asp_databases] The best way to do multiple UPDATES on a SQL

server table?







I need to update more than 1 record at a time in a sql server table.



I have tried a couple of different methods, niether seem to work.



Method 1:



a) SELECT all records that need updating into a recordset

b) loop through the recordset using rsUpdate.fields("field_name") =3D

"updated" to change the value of each field in the recordset.



this produces:

ADODB.Recordset error '800a0cb3'



Object or provider is not capable of performing requested operation.







Method 2:



a) SELECT all records that need updating as above

b) Make an array of these records

c) loop through the array, building a SQL UPDATE statement for each 

array

element and using an ADODB.Command to execute each SQL statement



this produces:

Microsoft OLE DB Provider for SQL Server error '80040e31'



Timeout expired





Does anyone know the best way to update more than one record/field at a

time?



Help much appreciated!





Jon R