Arrays inside Insert statement [Archive]

thunderain

06-21-02, 12:10

I have an add form sending add info to backend add.asp. The code
puts the info into field and value arrays and i must then use
an insert statement to put info into the database.
I am using ASP/VBScript with sqlServer.

This code works perfectly:

Set objRS1 = objConn.Execute("INSERT INTO tblPatrons (" & _
arrayField(0) & "," & arrayField(1) & _
") VALUES ('" & _
arrayValue(0) & "','" & arrayValue(1) & _
"')")

However, there number of fields/values may increase or decrease
as time goes on. This is the reason I am using arrays. Because of
this I need put arrays in the insert statement so that they will
handle different numbers of fields/values. I tried a couple of
ways, getting errors.

I tried putting entire arrays in:

Set objRS1 = objConn.Execute("INSERT INTO tblPatrons (" & arrayField() & ") VALUES ('" & arrayValue() & "')")

Error Message: Subscript out of range
(adding a count inside arrays - arrayField(arrayCountV) - causes other errors.)

I tried looping:

arrayCount = 1
Set objRS1 = objConn.Execute("INSERT INTO tblPatrons (" & _
Do until arrayCount = arrayCountV
arrayField(arrayCount) & "," & _
Loop
") VALUES ('" & _
Do until arrayCount = arrayCountV
arrayValue(arrayCount) & "','" & _
Loop
"')")

Error Message: Syntax error (on line: Do until arrayCount = arrayCountV)

It doesn't like the looping lines inside the insert statement.

Does anyone know how to make either of these work, or have a better way
of inserting the arrays into the insert statement.

Thank you
thunderain

rnealejr

06-21-02, 12:24

Do your loop first, creating a SQL string. Then run the objConn.Execute(strSQL). Have you thought about using programmatically created recordsets for your dynamic fields/values ?

thunderain

06-24-02, 10:17

Thanks for the tip rnealejr. I did just that, separated the loop from the statement and it works fine.

Thank you
thunderain