Generate SQL with a loop...

8 Mar 2005 7:45 AM

Øyvind Isaksen

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&"'); "
%>

8 Mar 2005 3:16 PM

Mark Schupp

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

Show quote

"Øyvind Isaksen" <oyv***@sorenso.no> wrote in message
news:eeLShL7IFHA.2784@TK2MSFTNGP09.phx.gbl...
> 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&"');
> "
> %>
>