Generate SQL with a loop...

Discussion in 'ASP General' started by Øyvind Isaksen, Mar 8, 2005.

  1. Hello!

    I need to dynamic generate a SQL statement based on how many images a user
    select to upload.

    Here you see an example with 2 images. It can be up to 50 images and I dont
    want to write this lines 50 times since they are almost identical (Example,
    first line has "varImage_1" , the second has "varImage_2"... and It shall go
    up to varImage_50...).

    How can I "generate" these lines so I only need to write it once, by using a
    loop?


    <%
    if varImage_1 <> "" then SQL = SQL & "insert into tblImage
    (path,sort,artID,custID,text) values
    ('"&varImage_1&"','1','"&dbArtID&"','"&varCustID&"','"&varTextImage1&"'); "

    if varImage_2 <> "" then SQL = SQL & "insert into tblImage
    (path,sort,artID,custID,text) values
    ('"&varImage_2&"','2','"&dbArtID&"','"&varCustID&"','"&varTextImage2&"'); "
    %>
    Øyvind Isaksen, Mar 8, 2005
    #1
    1. Advertising

  2. Øyvind Isaksen

    Mark Schupp Guest

    how are the varImage_n variables being set? If you can get them into an
    array you can

    For i = 1 to 50

    if varImage(i) <> "" then
    SQL = "insert into tblImage (path,sort,artID,custID,text) values " &
    _
    "('"&varImage(i) &
    "','1','"&dbArtID&"','"&varCustID&"','"&varTextImage(i)&"')"

    dbConn.Execute strSQL,, adCmdText + adExecuteNoRecords
    end if

    Next

    If the values are coming from form fields you can

    For i = 1 to 50

    varImage = Request.Form("varImage" & CStr(i) )
    if varImage <> "" then
    SQL = "insert into tblImage (path,sort,artID,custID,text) values " &
    _
    "('"&varImage &
    "','1','"&dbArtID&"','"&varCustID&"','"&request.form("varTextImage" &
    Cstr(i))&"')"

    dbConn.Execute strSQL,, adCmdText + adExecuteNoRecords
    end if

    Next

    I believe that you could also use the Eval function if you cannot change the
    variables to an array. But you'll have to check the docs for that. I had my
    fill off executable data a long time ago.

    Also, be sure to replace any single quotes in the input data with two single
    quotes in the SQL statement (or use parameterized command objects). See
    recent posts about the subject ( by Bob Barrows I think).

    --
    --Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com

    "Øyvind Isaksen" <> wrote in message
    news:...
    > Hello!
    >
    > I need to dynamic generate a SQL statement based on how many images a user
    > select to upload.
    >
    > Here you see an example with 2 images. It can be up to 50 images and I
    > dont want to write this lines 50 times since they are almost identical
    > (Example, first line has "varImage_1" , the second has "varImage_2"... and
    > It shall go up to varImage_50...).
    >
    > How can I "generate" these lines so I only need to write it once, by using
    > a loop?
    >
    >
    > <%
    > if varImage_1 <> "" then SQL = SQL & "insert into tblImage
    > (path,sort,artID,custID,text) values
    > ('"&varImage_1&"','1','"&dbArtID&"','"&varCustID&"','"&varTextImage1&"');
    > "
    >
    > if varImage_2 <> "" then SQL = SQL & "insert into tblImage
    > (path,sort,artID,custID,text) values
    > ('"&varImage_2&"','2','"&dbArtID&"','"&varCustID&"','"&varTextImage2&"');
    > "
    > %>
    >
    Mark Schupp, Mar 8, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Weng Tianxiang
    Replies:
    5
    Views:
    1,285
    Christophe
    Feb 16, 2006
  2. Replies:
    3
    Views:
    386
    red floyd
    Apr 7, 2006
  3. ecoolone
    Replies:
    0
    Views:
    731
    ecoolone
    Jan 3, 2008
  4. Harlan Messinger
    Replies:
    2
    Views:
    2,271
    John Bell
    Mar 28, 2010
  5. Isaac Won
    Replies:
    9
    Views:
    342
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page