Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Unanswered: Arrays inside Insert statement

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  3. #3
    Join Date
    Jun 2002
    Posts
    2
    Thanks for the tip rnealejr. I did just that, separated the loop from the statement and it works fine.

    Thank you
    thunderain

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •