Arrays inside Insert statement 1

[thunderain]

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.

This code works perfectly:

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

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() & &quot VALUES ('" & arrayValue() & "')&quot

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
&quot VALUES ('" & _
Do until arrayCount = arrayCountV
arrayValue(arrayCount) & "','" & _
Loop
"')&quot

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

 

[FengShui1998]

thunderain,

This should help...


strFlds = ""
strVals = ""

For I = 0 to Ubound( array(i) )
if I = Ubound( array(i) ) then
delim = ""
else
delim = ","
end if
strFlds = strFlds & ArrayField(i) & delim
strVals = strVals & ArrayValue(i) & delim

Next

strSQL = "INSERT INTO tblPatrons (" & strFlds & _
&quot VALUES (" & strVals & &quot"

Set objRS1 = objConn.Execute(strSQL)


fengshui_1998

 

[FengShui1998]

thunderain,


Change this to

strVals = strVals & "'" & ArrayValue(i) & "'" & delim

This is also assuming all your values are strings, not integers.

fengshui_1998

 

[thunderain]

Your code worked perfect. The only part i had trouble with was - array(i). I wasn't sure what to do with that. It seemed to only add one field. I replaced it with the couple of lines below where finalCount was the top number in my loop gathering info for the array.

For I = 0 to finalCount
if I = finalCount then

Thank you
thunderain