Record Update in a loop

[larryman]

Hi,

Could you please help figure out what's wrong with this code, it doesn't update the records in the loop aside from the very first one and at times i get EOF/BOF error.

The code is below
<%
Set dcnDB = Server.CreateObject(&quot;ADODB.Connection&quot
dcnDB.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot;_
& &quot;Data Source=f:\Aspdatabase\Database\userrequisition.mdb&quot;
dcnDB.Open

'Create the recordset object

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot

'Create the recordset object
Set rs1 = Server.CreateObject(&quot;ADODB.Recordset&quot
rs1.Open &quot;SELECT * FROM TempMinMax &quot; _
& &quot;Where Max = &quot; _
& Request.Form(&quot;order_id&quot&&quot; &quot;, _
dcnDB, 2, 2

MinHolder = rs1(&quot;Min&quot
MaxHolder = rs1(&quot;Max&quot

rs.Open &quot;SELECT * FROM airbooking &quot; _
& &quot;Where Request_id = &quot; _
& rs1(&quot;Min&quot&&quot; &quot;, _
dcnDB, adOpenKeyset, adLockOptimistic


Response.Write MinHolder
Response.Write MaxHolder
diff = MaxHolder - MinHolder




For i = MinHolder to Maxholder


If Request.Form(&quot;txt_Approval&quot= &quot;Approved&quot; then
Do While Not rs.EOF
rs(&quot;Head_Approve&quot = Request.Form(&quot;txt_Approval&quot
rs(&quot;Head_Approval_Date&quot = Request.Form(&quot;txt_Head_Date&quot
rs.Update
rs.MoveNext
Loop

Else

Do While Not rs.EOF
rs(&quot;Head_Approve&quot = Request.Form(&quot;txt_Approval&quot
rs(&quot;Head_Approval_Date&quot = Request.Form(&quot;txt_Head_Date&quot
rs.Update
rs.MoveNext
Loop

End if
Next
%>
Oysterbar ride to success. Keep Riding

 

[onpnt]

I'm not seeing where you are initializing the AddNew.
try adding rs.AddNew before adding the form values to the DB I dare to learn more

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[larryman]

Sorry,

I'm not adding a new record to the database, and i wouldn't know whether i need the addnew method. I'm only updating the rows. Oysterbar ride to success. Keep Riding

 

[onpnt]

I'm not adding a new record to the database
looks like it here
you are giving the value of the passed form to the rs
rs(&quot;Head_Approve&quot = Request.Form(&quot;txt_Approval&quot
rs(&quot;Head_Approval_Date&quot = Request.Form(&quot;txt_Head_Date&quot
rs.Update

which should be
rs.AddNew
rs(&quot;Head_Approve&quot = Request.Form(&quot;txt_Approval&quot
rs(&quot;Head_Approval_Date&quot = Request.Form(&quot;txt_Head_Date&quot
rs.Update

or use the edit method if you are editing the DB records I dare to learn more

http://www.onpntwebdesigns.com

admin@onpntwebdesigns.com

 

[larryman]

Thanks onpnt,

I finally sorted this out yesterday, it was the recordset that was the problem, i should have made the condition &quot;>=&quot; instead of just &quot;=&quot; which reduces the recordset using adopenkeyset.

rs.Open &quot;SELECT * FROM airbooking &quot; _
& &quot;Where Request_id = &quot; _
& rs1(&quot;Min&quot&&quot; &quot;, _
dcnDB, adOpenKeyset, adLockOptimistic

Anyway thanks a lot. Oysterbar ride to success. Keep Riding