'************************************** ' Name: Batch Update using ADO ' Description:ADO has a great batch upda ' te feature that not many people take adv ' antage of. You can use it to update many ' records at once without making multiple ' round trips to the database. Here is how ' to use it. ' By: Found on the World Wide Web ' ' ' Inputs:None ' ' Returns:None ' 'Assumes:None ' 'Side Effects:None ' 'Warranty: 'code PRovided by Planet Source Code(tm) ' (http://www.Planet-Source-Code.com) 'as ' is', without warranties as to performanc ' e, fitness, merchantability,and any othe ' r warranty (whether expressed or implied ' ). '**************************************
<HTML> <HEAD><TITLE>Place Document Title Here</TITLE></HEAD> <BODY BGColor=ffffff Text=000000> <% Set cn = Server.CreateObject("ADODB.Connection") Set rs = Server.CreateObject("ADODB.RecordSet") cn.Open application("guestDSN") rs.ActiveConnection = cn rs.CursorType = adOpenStatic rs.LockType = adLockBatchOptimistic rs.Source = "SELECT * FROM authors" rs.Open If (rs("au_fname") = "Paul") or (rs("au_fname") = "Johnson") Then newval = "Melissa" Else newval = "Paul" End If If err <> 0 Then %> <B>Error opening RecordSet</B> <% Else %> <B>Opened Successfully</B><P> <% End If %> <H2>Before Batch Update</H2> <TABLE BORDER=1> <TR> <% For i = 0 To rs.Fields.Count - 1 %> <TD><B><%= rs(i).Name %></B></TD> <% Next %> </TR> <% For j = 1 To 5 %> <TR> <% For i = 0 To rs.Fields.Count - 1 %> <TD><%= rs(i) %></TD> <% Next %> </TR> <% rs.MoveNext Next rs.MoveFirst %> </TABLE> Move randomly In the table and perform updates To table.<BR> <% Randomize r1 = Int(rnd*3) + 1 ' n Itterations r2 = Int(rnd*2) + 1 ' n places skipped between updates For i = 1 To r1 response.write "Itteration: " & i & "<BR>" rs("au_fname") = newval For j = 1 To r2 rs.MoveNext response.write "Move Next<BR>" Next Next rs.UpdateBatch adAffectAll rs.Requery rs.MoveFirst %> <% rs.MoveFirst %> <H2>After Changes</H2> <TABLE BORDER=1> <TR> <% For i = 0 To rs.Fields.Count - 1 %> <TD><B><%= rs(i).Name %></B></TD> <% Next %> </TR> <% For j = 1 To 5 %> <TR> <% For i = 0 To rs.Fields.Count - 1 %> <TD><%= rs(i) %></TD> <% Next %> </TR> <% rs.MoveNext Next rs.Close Cn.Close %> </TABLE>
