For each item in Request.Form

[Drew]

I need to iterate through a submitted form, inserting data on each pass. In
the past, I have always used form elements that were named with numbers at
the end, like this,

name1 relationship1
name2 relationship2
name3 relationship3

With the above formatting, I could write a For... Next loop to insert all
the data into my database,

For i = 1 to 3
If Request.Form("n ame" & i) <> "" Then
sql = INSERT INTO Table (Name, Relationship)
sql = sql & " VALUES"
sql = sql & "('" Request.Form("n ame" & i) "',"
sql = sql & "('" Request.Form("r elationship" & i) "')"

'Execute SQL
End If
Next

Now I am working on a new loop, but do not want to go back and change the
way the submitting page works. I saw on while researching that I can use a
For each item in Request.Form, but tried this and it throws off my SQL
statement. It adds all of the request.form values to the sql statement,
which then fails.

How can I make this loop?

Thanks,
Drew

[Evertjan.]

Drew wrote on 24 jan 2006 in microsoft.publi c.inetserver.as p.general:

"Evertjan." <ex************ **@interxnl.net > wrote in message
news:Xn******** ************@19 4.109.133.242.. .

Drew wrote on 24 jan 2006 in microsoft.publi c.inetserver.as p.general:

For Each item In Request.Form
'Run Insert Code
sql="INSERT INTO People (Name,Relations hip)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("N ame") & "',"
sql=sql & "'" & Request.Form("R elationship") & "')"

I don't think this will work.

Do you expect multiple
Request.Form("N ame") and Request.Form("R elationship") groups
that will listen to "Each item In Request.Form" ???

[please do not toppost on usenet]
Yep... as I say, I have never used the "For each item in
Request.Form", I thought it would iterate through each form element,
instead it just comma delimits them and that is it. I guess I will
have to build an array in the For... Next loop and insert the array
values?

No, no array.

Just have individual numbered names for the form values,
the below works for any number of entries:

<input name='Name1'> <input name='Relations hip1'><br>
<input name='Name2'> <input name='Relations hip2'><br>
<input name='Name3'> <input name='Relations hip3'><br>
............... ..
=============== =============== =====
debug = true
' debug = false
n=1
while Request.Form("N ame" & n) <> ""
sql = "INSERT INTO People (Name,Relations hip) VALUES "&_
"('" & Request.Form("N ame" & n) & "','" &_
Request.Form("R elationship" & n) & "')"
if debug then
response.write sql & "<br>"
else
CONN.execute(sq l)
end if
n=n+1
wend
if debug then response.end
.....

=============== =============== ======

not tested.

btw: You will have to do proper validation against hacker injection,
if this is an open internet job!!!!
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Larry Bud]

Drew wrote:

If Request.Form("S ubmitted") = "Yes" Then
'Start Loop
For Each item In Request.Form
'Run Insert Code
sql="INSERT INTO People (Name,Relations hip)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("N ame") & "',"
sql=sql & "'" & Request.Form("R elationship") & "')"

'execute the insert to SQL Server
Set MM_editCmd = Server.CreateOb ject("ADODB.Com mand")
MM_editCmd.Acti veConnection = MM_lcnn_STRING
MM_editCmd.Comm andText = sql
Response.Write( sql)
MM_editCmd.Exec ute
MM_editCmd.Acti veConnection.Cl ose
Next
End If

I would rather use an ado.recordset object, which will handle the
possible SQL injection attacks.

For example, let's say each form name you want to save into the
database begins with name_ and relationship_

so
name_1, relationship_1
name_2, relationship_2

etc.

for each item in request.form
if left(item,5)="n ame_" then
itemcount=right (item,len(item)-5)
rs.addnew
rs("name")=requ est.form(item)
rs("relationshi p")=request.for m("relationship _" & itemcount)
rs.update
end if
next

[Drew]

Thanks for all the replies. I finally got this fixed by just renaming the
form elements to name1, name2, name3, relationship1, etc... SQL injection is
not one of my worries, since these apps reside on an intranet. If someone
does do a injection attack, we will know who it was, take administrative
action and restore our data from the tapes...

It would be better to prevent them in the first place, but then how do you
take administrative action? We want to get any employee who is trying to
damage our network/database out of this organization.

Thanks,
Drew

"Larry Bud" <la**********@y ahoo.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

Drew wrote:

If Request.Form("S ubmitted") = "Yes" Then
'Start Loop
For Each item In Request.Form
'Run Insert Code
sql="INSERT INTO People (Name,Relations hip)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("N ame") & "',"
sql=sql & "'" & Request.Form("R elationship") & "')"

'execute the insert to SQL Server
Set MM_editCmd = Server.CreateOb ject("ADODB.Com mand")
MM_editCmd.Acti veConnection = MM_lcnn_STRING
MM_editCmd.Comm andText = sql
Response.Write( sql)
MM_editCmd.Exec ute
MM_editCmd.Acti veConnection.Cl ose
Next
End If

I would rather use an ado.recordset object, which will handle the
possible SQL injection attacks.

For example, let's say each form name you want to save into the
database begins with name_ and relationship_

so
name_1, relationship_1
name_2, relationship_2

etc.

for each item in request.form
if left(item,5)="n ame_" then
itemcount=right (item,len(item)-5)
rs.addnew
rs("name")=requ est.form(item)
rs("relationshi p")=request.for m("relationship _" & itemcount)
rs.update
end if
next

[Bob Barrows [MVP]]

Drew wrote:

Thanks for all the replies. I finally got this fixed by just
renaming the form elements to name1, name2, name3, relationship1,
etc... SQL injection is not one of my worries, since these apps
reside on an intranet.
A majority of hacks are done by disgruntled employees
If someone does do a injection attack, we
will know who it was, take administrative action and restore our data
from the tapes...
It would be better to prevent them in the first place, but then how
do you take administrative action?

Validate the data in server-side code before using it, logging the
suspicious data and the user's name.

SAL Injection is not the only reason to avoid dynamic sql. It's a powerful
reason, but not the only one. Performance and code maintainability also
enter into the equation.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Larry Bud wrote:

I would rather use an ado.recordset object, which will handle the
possible SQL injection attacks.

But that adds its own overhead. Cursors are very inefficient when it comes
to maintaining data. It is much better to use DML, and SQL Injection is
easily prevented by the use of parameters as I illustrated in my two posts.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"