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,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]

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).