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.

What is that, that your "form outputs"? Is it a list of strings? Is it a single string that is newline-character delimited? Something else? — robbpriestley, Dec 11 '19 at 00:27

score 0 · Accepted Answer · answered Dec 11 '19 at 06:54

0

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

answered Dec 11 '19 at 06:54

Working solution, what's with the thumbs down? – Dec 11 '19 at 11:23
Not sure who downed this, it is indeed the working solution. Very nice, thank you! – ohgodpleasehelpme Dec 11 '19 at 16:17
2

How many times does it have to be drilled into people that directly executing SQL from form input is the fastest way to have your data breached. This is why so many companies now are having to declare security breaches, because of shoddy approaches like this. At the very least, pass the values [parameterised using `ADODB.Command`](https://stackoverflow.com/a/58941335/692942). This may work but it is fraught with problems in the long run. – user692942 Dec 12 '19 at 08:28

How to update database with dynamic number of rows?

1 Answers1