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

[Bob Barrows [MVP]]

Drew wrote:

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.

Response.Write the statement to see why it fails. We cannot debug a sql
statement without seeing what it is.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Drew]

The SQL Statement will look like this, if there are 3 textboxes for name and
relationship,

INSERT INTO Table (Name,Relations hip) VALUES ('Jack, John, Diane','Brother ,
Brother, Sister')

I need it to look like this,

INSERT INTO Table (Name,Relations hip) VALUES ('Jack','Brothe r')
INSERT INTO Table (Name,Relations hip) VALUES ('John','Brothe r')
INSERT INTO Table (Name,Relations hip) VALUES ('Diane','Siste r')

Thanks,
Drew
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:ug******** ********@TK2MSF TNGP12.phx.gbl. ..

Drew wrote:

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.

Response.Write the statement to see why it fails. We cannot debug a sql
statement without seeing what it is.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

Drew wrote:

The SQL Statement will look like this, if there are 3 textboxes for
name and relationship,

INSERT INTO Table (Name,Relations hip) VALUES ('Jack, John,
Diane','Brother , Brother, Sister')

So this is the failing statement resulting from your code?
Let's see the code that generated this.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Drew]

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

Thanks,
Drew
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:u7******** ********@tk2msf tngp13.phx.gbl. ..

Drew wrote:

The SQL Statement will look like this, if there are 3 textboxes for
name and relationship,

INSERT INTO Table (Name,Relations hip) VALUES ('Jack, John,
Diane','Brother , Brother, Sister')

So this is the failing statement resulting from your code?
Let's see the code that generated this.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Evertjan.]

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" ???
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Drew]

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?

Thanks,
Drew

"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" ???
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

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

To see what is going on here, do a
Response.Write Request.Form("N ame")
See? the form submission process concatenates the values from all
like-minded fields into a comma-delimited string.

You need to revert to the technique of differentiating the field names with
"row" numbers. You can still use "for each":

dim sql,sName,sRela tionship, arParms
sql="INSERT INTO People (Name,Relations hip) Values(?,?)"
dim cn

'Always use an explicit connection object - implicit connections
'can impair performance by disabling session pooling

Set cn = Server.CreateOb ject("ADODB.Con nection")
cn.open MM_lcnn_STRING
Set MM_editCmd = Server.CreateOb ject("ADODB.Com mand")
Set MM_editCmd.Acti veConnection = cn
MM_editCmd.Comm andText = sql
MM_editCmd.comm andtype = 1 'adCmdText
For Each item In Request.Form
If left(item,4) = "Name" then
rownumber= mid(item,5)
sName=request.f orm(item)
sRelationship=r equest.form("Re lationship" & rownumber)
arParms=Array(s Name,sRelations hip)
MM_editCmd.Exec ute ,arParms, 128 'adExecuteNoRec ords
end if
Next
cn.close: set cn=nothing

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

If you could be sure the field values were always in the proper order (I am
not sure this is a valid assumption), you could do this:

dim arNames, arRelationships , i
arNames = Split(Request.F orm("Name"),"," )
arRelationships = Split(Request.F orm("Relationsh ip"),",")

dim sql,sName,sRela tionship, arParms
sql="INSERT INTO People (Name,Relations hip) Values(?,?)"
dim cn

'Always use an explicit connection object - implicit connections
'can impair performance by disabling session pooling

Set cn = Server.CreateOb ject("ADODB.Con nection")
cn.open MM_lcnn_STRING
Set MM_editCmd = Server.CreateOb ject("ADODB.Com mand")
Set MM_editCmd.Acti veConnection = cn
MM_editCmd.Comm andText = sql
MM_editCmd.comm andtype = 1 'adCmdText
for i = 0 to ubound(arNames)
arParms= Array(arNames(i ), arRelationships (i))
MM_editCmd.Exec ute ,arParms, 128 'adExecuteNoRec ords
Next
cn.close: set cn=nothing
Drew wrote:

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?

Thanks,
Drew

"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" ???
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[AnthonyWJones]

What heppens if the persons name happens to be O'Brian ?

Or worse yet what happens if the user enters something like:-
',''); Delete People; Other SQL code that does malicious things; --

?? Ouch.

It's called SQL Injection.


"Drew" wrote:

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?

Thanks,
Drew

"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" ???
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)