Generate SQL with a loop...

[Ø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,artI D,custID,text) values
('"&varImage_1& "','1','"&dbArt ID&"','"&varCus tID&"','"&varTe xtImage1&"'); "

if varImage_2 <> "" then SQL = SQL & "insert into tblImage
(path,sort,artI D,custID,text) values
('"&varImage_2& "','2','"&dbArt ID&"','"&varCus tID&"','"&varTe xtImage2&"'); "
%>

[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,artI D,custID,text) values " &
_
"('"&varImage(i ) &
"','1','"&dbArt ID&"','"&varCus tID&"','"&varTe xtImage(i)&"')"

dbConn.Execute strSQL,, adCmdText + adExecuteNoReco rds
end if

Next

If the values are coming from form fields you can

For i = 1 to 50

varImage = Request.Form("v arImage" & CStr(i) )
if varImage <> "" then
SQL = "insert into tblImage (path,sort,artI D,custID,text) values " &
_
"('"&varIma ge &
"','1','"&dbArt ID&"','"&varCus tID&"','"&reque st.form("varTex tImage" &
Cstr(i))&"')"

dbConn.Execute strSQL,, adCmdText + adExecuteNoReco rds
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" <oy****@sorenso .no> wrote in message
news:ee******** ******@TK2MSFTN GP09.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,artI D,custID,text) values
('"&varImage_1& "','1','"&dbArt ID&"','"&varCus tID&"','"&varTe xtImage1&"');
"

if varImage_2 <> "" then SQL = SQL & "insert into tblImage
(path,sort,artI D,custID,text) values
('"&varImage_2& "','2','"&dbArt ID&"','"&varCus tID&"','"&varTe xtImage2&"');
"
%>