How to update database with dynamic number of rows?

Question

I have a form where the number of rows to update is not known and some may require an insert, others may require an update in the database. I've given each input name and ID, if needed.

Here is an example of what the form outputs, each row in the form has 3 inputs:

plcid_1 =
consumed_1 = 850 
runtime_1 = 20
plcid_2 = 1
consumed_2 = 500
runtime_2 = 25
plcid_3 = 
consumed_3 = 100
runtime_3 = 50
plcid_4 = 
consumed_4 = 485
runtime_4 = 20
plcid_5 = 2
consumed_5 = 400
runtime_5 = 39

What I need to happen is to loop through each set and do the following:

When plcid_n is blank:

INSERT INTO Table (consumed, runtime) VALUES (850,20)
INSERT INTO Table (consumed, runtime) VALUES (100,50)
INSERT INTO Table (consumed, runtime) VALUES (485,20)

But when plcid_n has a value:

UPDATE Table SET consumed='500', runtime='25' WHERE plcid='1'
UPDATE Table SET consumed='400', runtime='39' WHERE plcid='2'

Anything I do with a for each function just uses each individual form item instead of in sets, so I can't create the database query.

Answer 1

this should work:

dim fld, plcid, consumed, runtime

for each fld in request.form
    if left(fld, 6) = "plcid_" then
        plcid = mid(fld, 7)
        consumed = request.form("consumed_" & plcid)
        runtime = request.form("runtime_" & plcid)
        if request.form(fld) = "" then
            sql = "INSERT INTO TABLE (consumed, runtime) VALUES(" & consumed & "," & runtime & ")"
            ' execute
        else
            sql = "UPDATE TABLE SET consumed = " & consumed & ", runtime = " & runtime & " WHERE plcid = " & plcid
            ' execute
        end if
    end if
next