471,071 Members | 1,205 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

Generate SQL with a loop...

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&"'); "
%>
Jul 22 '05 #1
1 1356
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&"','"&varTextIma ge(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.fo rm("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" <oy****@sorenso.no> wrote in message
news:ee**************@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&"');
"
%>

Jul 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by grant | last post: by
12 posts views Thread by jaapkramer | last post: by
5 posts views Thread by Chameleon | last post: by
6 posts views Thread by Marcus Kwok | last post: by
15 posts views Thread by Orchid | last post: by
3 posts views Thread by Bill Woessner | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.