For each item in Request.Form

Discussion in 'ASP General' started by Drew, Jan 24, 2006.

  1. Drew

    Drew Guest

    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("name" & i) <> "" Then
    sql = INSERT INTO Table (Name, Relationship)
    sql = sql & " VALUES"
    sql = sql & "('" Request.Form("name" & i) "',"
    sql = sql & "('" Request.Form("relationship" & 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
     
    Drew, Jan 24, 2006
    #1
    1. Advertising

  2. 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("name" & i) <> "" Then
    > sql = INSERT INTO Table (Name, Relationship)
    > sql = sql & " VALUES"
    > sql = sql & "('" Request.Form("name" & i) "',"
    > sql = sql & "('" Request.Form("relationship" & 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], Jan 24, 2006
    #2
    1. Advertising

  3. Drew

    Drew Guest

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

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

    I need it to look like this,

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

    Thanks,
    Drew


    "Bob Barrows [MVP]" <> wrote in message
    news:ug%...
    > 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("name" & i) <> "" Then
    >> sql = INSERT INTO Table (Name, Relationship)
    >> sql = sql & " VALUES"
    >> sql = sql & "('" Request.Form("name" & i) "',"
    >> sql = sql & "('" Request.Form("relationship" & 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, Jan 24, 2006
    #3
  4. Drew wrote:
    > The SQL Statement will look like this, if there are 3 textboxes for
    > name and relationship,
    >
    > INSERT INTO Table (Name,Relationship) 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.
     
    Bob Barrows [MVP], Jan 24, 2006
    #4
  5. Drew

    Drew Guest

    If Request.Form("Submitted") = "Yes" Then
    'Start Loop
    For Each item In Request.Form
    'Run Insert Code
    sql="INSERT INTO People (Name,Relationship)"
    sql=sql & " VALUES "
    sql=sql & "('" & Request.Form("Name") & "',"
    sql=sql & "'" & Request.Form("Relationship") & "')"

    'execute the insert to SQL Server
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_lcnn_STRING
    MM_editCmd.CommandText = sql
    Response.Write(sql)
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close
    Next
    End If

    Thanks,
    Drew


    "Bob Barrows [MVP]" <> wrote in message
    news:u7%...
    > Drew wrote:
    >> The SQL Statement will look like this, if there are 3 textboxes for
    >> name and relationship,
    >>
    >> INSERT INTO Table (Name,Relationship) 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, Jan 24, 2006
    #5
  6. Drew

    Evertjan. Guest

    Drew wrote on 24 jan 2006 in microsoft.public.inetserver.asp.general:

    > For Each item In Request.Form
    > 'Run Insert Code
    > sql="INSERT INTO People (Name,Relationship)"
    > sql=sql & " VALUES "
    > sql=sql & "('" & Request.Form("Name") & "',"
    > sql=sql & "'" & Request.Form("Relationship") & "')"
    >


    I don't think this will work.

    Do you expect multiple
    Request.Form("Name") and Request.Form("Relationship") groups
    that will listen to "Each item In Request.Form" ???


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jan 24, 2006
    #6
  7. Drew

    Drew Guest

    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." <> wrote in message
    news:Xns9755A4D92C798eejj99@194.109.133.242...
    > Drew wrote on 24 jan 2006 in microsoft.public.inetserver.asp.general:
    >
    >> For Each item In Request.Form
    >> 'Run Insert Code
    >> sql="INSERT INTO People (Name,Relationship)"
    >> sql=sql & " VALUES "
    >> sql=sql & "('" & Request.Form("Name") & "',"
    >> sql=sql & "'" & Request.Form("Relationship") & "')"
    >>

    >
    > I don't think this will work.
    >
    > Do you expect multiple
    > Request.Form("Name") and Request.Form("Relationship") groups
    > that will listen to "Each item In Request.Form" ???
    >
    >
    > --
    > Evertjan.
    > The Netherlands.
    > (Please change the x'es to dots in my emailaddress)
     
    Drew, Jan 24, 2006
    #7
  8. Drew wrote:
    > If Request.Form("Submitted") = "Yes" Then
    > 'Start Loop
    > For Each item In Request.Form
    > 'Run Insert Code
    > sql="INSERT INTO People (Name,Relationship)"
    > sql=sql & " VALUES "
    > sql=sql & "('" & Request.Form("Name") & "',"
    > sql=sql & "'" & Request.Form("Relationship") & "')"


    To see what is going on here, do a
    Response.Write Request.Form("Name")
    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,sRelationship, arParms
    sql="INSERT INTO People (Name,Relationship) Values(?,?)"
    dim cn

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

    Set cn = Server.CreateObject("ADODB.Connection")
    cn.open MM_lcnn_STRING
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    Set MM_editCmd.ActiveConnection = cn
    MM_editCmd.CommandText = sql
    MM_editCmd.commandtype = 1 'adCmdText
    For Each item In Request.Form
    If left(item,4) = "Name" then
    rownumber= mid(item,5)
    sName=request.form(item)
    sRelationship=request.form("Relationship" & rownumber)
    arParms=Array(sName,sRelationship)
    MM_editCmd.Execute ,arParms, 128 'adExecuteNoRecords
    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], Jan 24, 2006
    #8
  9. 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.Form("Name"),",")
    arRelationships = Split(Request.Form("Relationship"),",")

    dim sql,sName,sRelationship, arParms
    sql="INSERT INTO People (Name,Relationship) Values(?,?)"
    dim cn

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

    Set cn = Server.CreateObject("ADODB.Connection")
    cn.open MM_lcnn_STRING
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    Set MM_editCmd.ActiveConnection = cn
    MM_editCmd.CommandText = sql
    MM_editCmd.commandtype = 1 'adCmdText
    for i = 0 to ubound(arNames)
    arParms= Array(arNames(i), arRelationships(i))
    MM_editCmd.Execute ,arParms, 128 'adExecuteNoRecords
    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." <> wrote in message
    > news:Xns9755A4D92C798eejj99@194.109.133.242...
    >> Drew wrote on 24 jan 2006 in microsoft.public.inetserver.asp.general:
    >>
    >>> For Each item In Request.Form
    >>> 'Run Insert Code
    >>> sql="INSERT INTO People (Name,Relationship)"
    >>> sql=sql & " VALUES "
    >>> sql=sql & "('" & Request.Form("Name") & "',"
    >>> sql=sql & "'" & Request.Form("Relationship") & "')"
    >>>

    >>
    >> I don't think this will work.
    >>
    >> Do you expect multiple
    >> Request.Form("Name") and Request.Form("Relationship") 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.
     
    Bob Barrows [MVP], Jan 24, 2006
    #9
  10. 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." <> wrote in message
    > news:Xns9755A4D92C798eejj99@194.109.133.242...
    > > Drew wrote on 24 jan 2006 in microsoft.public.inetserver.asp.general:
    > >
    > >> For Each item In Request.Form
    > >> 'Run Insert Code
    > >> sql="INSERT INTO People (Name,Relationship)"
    > >> sql=sql & " VALUES "
    > >> sql=sql & "('" & Request.Form("Name") & "',"
    > >> sql=sql & "'" & Request.Form("Relationship") & "')"
    > >>

    > >
    > > I don't think this will work.
    > >
    > > Do you expect multiple
    > > Request.Form("Name") and Request.Form("Relationship") groups
    > > that will listen to "Each item In Request.Form" ???
    > >
    > >
    > > --
    > > Evertjan.
    > > The Netherlands.
    > > (Please change the x'es to dots in my emailaddress)

    >
    >
    >
     
    AnthonyWJones, Jan 24, 2006
    #10
  11. Drew

    Evertjan. Guest

    Drew wrote on 24 jan 2006 in microsoft.public.inetserver.asp.general:
    > "Evertjan." <> wrote in message
    > news:Xns9755A4D92C798eejj99@194.109.133.242...
    >> Drew wrote on 24 jan 2006 in microsoft.public.inetserver.asp.general:
    >>
    >>> For Each item In Request.Form
    >>> 'Run Insert Code
    >>> sql="INSERT INTO People (Name,Relationship)"
    >>> sql=sql & " VALUES "
    >>> sql=sql & "('" & Request.Form("Name") & "',"
    >>> sql=sql & "'" & Request.Form("Relationship") & "')"
    >>>

    >>
    >> I don't think this will work.
    >>
    >> Do you expect multiple
    >> Request.Form("Name") and Request.Form("Relationship") 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='Relationship1'><br>
    <input name='Name2'> <input name='Relationship2'><br>
    <input name='Name3'> <input name='Relationship3'><br>
    .................


    ===================================
    debug = true
    ' debug = false
    n=1
    while Request.Form("Name" & n) <> ""
    sql = "INSERT INTO People (Name,Relationship) VALUES "&_
    "('" & Request.Form("Name" & n) & "','" &_
    Request.Form("Relationship" & n) & "')"
    if debug then
    response.write sql & "<br>"
    else
    CONN.execute(sql)
    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)
     
    Evertjan., Jan 24, 2006
    #11
  12. Drew

    Larry Bud Guest

    Drew wrote:
    > If Request.Form("Submitted") = "Yes" Then
    > 'Start Loop
    > For Each item In Request.Form
    > 'Run Insert Code
    > sql="INSERT INTO People (Name,Relationship)"
    > sql=sql & " VALUES "
    > sql=sql & "('" & Request.Form("Name") & "',"
    > sql=sql & "'" & Request.Form("Relationship") & "')"
    >
    > 'execute the insert to SQL Server
    > Set MM_editCmd = Server.CreateObject("ADODB.Command")
    > MM_editCmd.ActiveConnection = MM_lcnn_STRING
    > MM_editCmd.CommandText = sql
    > Response.Write(sql)
    > MM_editCmd.Execute
    > MM_editCmd.ActiveConnection.Close
    > 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)="name_" then
    itemcount=right(item,len(item)-5)
    rs.addnew
    rs("name")=request.form(item)
    rs("relationship")=request.form("relationship_" & itemcount)
    rs.update
    end if
    next
     
    Larry Bud, Jan 24, 2006
    #12
  13. Drew

    Drew Guest

    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" <> wrote in message
    news:...
    >
    > Drew wrote:
    >> If Request.Form("Submitted") = "Yes" Then
    >> 'Start Loop
    >> For Each item In Request.Form
    >> 'Run Insert Code
    >> sql="INSERT INTO People (Name,Relationship)"
    >> sql=sql & " VALUES "
    >> sql=sql & "('" & Request.Form("Name") & "',"
    >> sql=sql & "'" & Request.Form("Relationship") & "')"
    >>
    >> 'execute the insert to SQL Server
    >> Set MM_editCmd = Server.CreateObject("ADODB.Command")
    >> MM_editCmd.ActiveConnection = MM_lcnn_STRING
    >> MM_editCmd.CommandText = sql
    >> Response.Write(sql)
    >> MM_editCmd.Execute
    >> MM_editCmd.ActiveConnection.Close
    >> 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)="name_" then
    > itemcount=right(item,len(item)-5)
    > rs.addnew
    > rs("name")=request.form(item)
    > rs("relationship")=request.form("relationship_" & itemcount)
    > rs.update
    > end if
    > next
    >
     
    Drew, Jan 24, 2006
    #13
  14. 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], Jan 24, 2006
    #14
  15. 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"
     
    Bob Barrows [MVP], Jan 24, 2006
    #15
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Brian Birtle
    Replies:
    2
    Views:
    2,098
    John Saunders
    Oct 16, 2003
  2. John Blair
    Replies:
    1
    Views:
    421
    Eliyahu Goldin
    Aug 3, 2005
  3. Tjerk Wolterink

    xsl:for-each for each 3 elements problem

    Tjerk Wolterink, Nov 3, 2004, in forum: XML
    Replies:
    3
    Views:
    441
    Tjerk Wolterink
    Nov 3, 2004
  4. Mike
    Replies:
    3
    Views:
    177
    Randy Rahbar
    Dec 11, 2003
  5. Michael Kujawa

    Help in writing SQL using request.form.item(i)

    Michael Kujawa, Oct 11, 2006, in forum: ASP General
    Replies:
    3
    Views:
    139
    Michael Kujawa
    Oct 11, 2006
Loading...

Share This Page