Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


for loop code needed for asp insert

for loop code needed for asp insert

I need help with a loop to save change on page 2 that where selected on page 1
Page 1 - lists all games owned by a users, a check box shows which games the user has.

page 2 - is called when the user clicks the save button.

the following code works, it addes the user to the user table, and one static record to the gamesOwners table.

what I need it how do I loop thru and read the GameIds on page 1.

I've only be code asp for the last three days.
so if there is a way to only have 1 page, great!
otherwise what do I need to finish this code.


Page 2 code---

<title>ADO - Submit DataBase Record</title>
<h2>Submit to Database</h2>

<%on error resume next
    dim conn,rs ,x , objErr
    set conn=Server.CreateObject("ADODB.Connection")

    ' add new owner to table
    sql="INSERT INTO tblGameOwners ( Name ) Values ('" & Request.Form("fname") & "')"
    'sql="INSERT INTO tblGameOwners ( Name ) Values ('test')"

    'on error resume next
    conn.Execute sql
    if err.number<>0 then
        Response.Write("No update permissions!" & err.number & " " & sql)
        Response.Write("<h3>" & recaffected & " record added (name)</h3>")
    end if
    ' get autonumber id for new owner
    set rs=Server.CreateObject("ADODB.Recordset") "SELECT ID FROM tblGameOwners where name = '" & Request.Form("fname") & "'",conn
    ' add games to ownerGames for new owner  
    for each x in rs.Fields
      if"ID" then
        ' for x = 1 to totalRowsIn Screen1 ( need loop code )
            sql="INSERT INTO tblOwnerGames ( OwnerID , GameId) Values (" & x.value & " , 1)"
        conn.Execute sql
        if err.number<>0 then
            Response.Write("No update permissions!" & err.number & " " & sql)
            Response.Write("<h3>" & recaffected & " record added (games)</h3>")
        end if
     end if
set Conn = nothing %>

<h6>AdoInsertNew </h6>

page 1 code ----


if id="" then response.end
set conn=Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT tblGamesList.gameID, tblGamesList.gameName, tblGamesList.version, " & _
   " (SELECT IIf(Not IsNull([OwnerID]),'1','0')  " & _
   " FROM tblOwnerGames   " & _
   " WHERE tblOwnerGames.OwnerID = " & id & " and  tblGamesList.gameid =tblOwnerGames.gameID ) AS Owns " & _
   " FROM tblGamesList ", conn
set rs2 = Server.CreateObject("ADODB.Recordset")
rs2.Open "SELECT Name " & _
   " FROM tblGameOwners  " & _
   " WHERE ID = " & id , conn

<h2>List Games</h2>

<table border="1" width="25%">
<tr bgcolor="#b0c4de">
<%for each x2 in rs2.Fields
        response.write("<th>" & ucase( & "</th>")
next %>
<tr bgcolor="#f0f0f0">
<%for each x2 in rs2.Fields
next %>

<table border="1" width="75%">
<tr bgcolor="#b0c4de">
for each x in rs.Fields
     if = "gameID" or = "gameName" or = "version" or = "Owns" then%>
           <%response.write("<th>" & ucase( & "</th>")%>
     <%end if
<%do until rs.EOF%>
<tr bgcolor="#f0f0f0">
<form method="post" action="adoEditGame.asp" target="_blank">
for each x in rs.Fields
        if"gameID" then%>
               <td><center><input type="submit" name="gameID" value="<%=x.value%>"></center></td>
         <%elseif = "Owns" Then %>
                <% if x.value=1 then %>
                    <td><center><input type="checkbox" name="Owns" value="<%=x.value%>" checked=""checked""  ></center</td>
                    <td><center><input type="checkbox" name="Owns" value="<%=x.value%>"></center></td>
                <%end if%>
         <%elseif = "gameName" then%>
               <td><%Response.Write(x.value)%> </td>
         <%elseif = "gameID" or = "version"  then%>
               <td><%Response.Write(x.value)%> </td>
         <%end if
set rs=nothing
set rs2=nothing
set conn=nothing

<form method="post" action="adoUpdate.asp" target="_blank">
  <input type="submit" name="action" value="Save">
  <input type="submit" name="action" value="Delete">

<form method="post" action="adoAddGame.asp" target="_blank">
  <input type="submit" name="action" value="Add Game">

<h6>AdoEdit </h6>



RE: for loop code needed for asp insert

if you put all the checkboxes **with the same name** on page one, eg
<input type=checkbox name=fField>
<input type=checkbox name=fField>
<input type=checkbox name=fField>

then after the submit, that will result in 1 value for request.form("fField").
Every value delimited with a ",".

So you parse that into seperate values using the split() function. then you could use a for next loop to generate the SQL instructions needed.

RE: for loop code needed for asp insert

if i understand correctly

my loop to display the values should create a set of values in Owns that would look like 1,1,0,0,1,0,1,..... (on or off)

<td><center><input type="checkbox" name="Owns" value="<%=x.value%>"

add I should be to parse them ouot in the next form, correct?

should this be the same case for the gameID? I'll need this value to match to the Owns value.

 so gameid values  1,2,3,7,8,9,12,45 ...
     Owns values   1,1,0,0,1,0,1,1 ...

RE: for loop code needed for asp insert


add I should be to parse them ouot in the next form, correct?

should be

and I should be to parse them out in the next form, correct?

RE: for loop code needed for asp insert

the trouble is to keep the order and number of variables of page 1 in line with the processing. It may be possible that in another session an extra record is added, and then there is a mismatch. Maybe you think changes are low of nil, so it could work.

If you start with a recordset based on "SELECT values FROM table WHERE condition ORDER field", then you could write all the
<input type=checkbox name=fField> 's.

After a submit you do a split( request.form("fField"))
after which you have an array with values.

Then you create a recordset with the records you want to update, eg
"SELECT ids FROM table WHERE condition ORDER field"

You could test if this select retrieves the same number of records as the number of values in the array. If not: there was a change, and you should go back to the page 1.

Then your loop is something like
i = 0
do while not rs.eof()
 cSQL = "UPDATE table SET field = " & aField(i) &_
        "WHERE id = " & rs("id")
 conn.execute cSQL
 i = i + 1
You rely on the same number of and the same order of values and fields.

Alternatively you could number the fields
<input type=checkbox name=fField1>
<input type=checkbox name=fField2>
<input type=checkbox name=fField3>

etc and store the number of fields in a hidden field
<input type=hidden name=FieldCount value=3>

After a submit you do somethng like this:

for i = 1 to request.form("FieldCount")
 cField = "fField" & i
 cSQL = "UPDATE table SET field" & i = " & _
 conn.execute cSQL

I did not test any code here, it tries to give you ideas.



RE: for loop code needed for asp insert

I think I got the basic idea.

If you start with a recordset based on "SELECT values FROM table WHERE condition ORDER field", then you could write all the <input type=checkbox name=fField> 's.

I was able to pass to the next form the values in the checkbox when loaded but I'm not sure how to get the new values.

I added the following in the for loop, when displaying the data, it captures the state of the checkboxes with the following
---- ( lganmeid is the ID of the software ( id = 1,2,55 ...)
if ...
  display software and user has it...
 <%GameNumber(lGameID) = 1   %>
 display software and user does not have it.
 <%GameNumber(lGameID) = 0   %>
end if

session("sOwns") = session("sOwns") & GameNumber(lGameID)
but it does not recapture the state after the user makes changes
it does end up looking like what you discribed
when I get to the next form, I have 110000000, which I can use to update the database but it's only what was retrieved not what was changed.

here is what I'm trying to do.
1) read current software user has. (works)
2) read/display the all software and set checkbox if user has it.
 ( ex:if we have 10 software pgms , user only has 3 of them,   all 10 names are displayed and only 3 checked)(works)
3) allow user to check/uncheck boxes (works)
4) capture what the user changed on checkbox ( does not work)  

ex of form (if it helps, user has two of 4)
user : tdr
   MS XP         x
   MS WORD       x  
(user then want to change from XP to 7 , would uncheck one then check the other) I need to figure out how to tell that box one (xp) is now off and box 3 (win7) is now on, then update the database. (if I retrieved 1001 need to see that user changed to 0011 )

Maybe I going that this code the wrong way.

I've been trying to teach myself asp (for about 5 days) and I'm guessing at how to write the app.

I've got the single line updates working.
i.e. if I need to add/delete a user or a software title.

again Thanks

RE: for loop code needed for asp insert

I finally got it to work.

using the value of software Id (gameID) and not the 1,0 (owns) from the select, will let me do what I need.

         <input type="checkbox" name="CbOwns" value="<%=(rsProducts.fields(&amp;quot;gameID&amp;quot;).Value) %>" <% if (rsProducts.fields(&amp;quot;Owns&amp;quot;).Value) = 1 then response.write &amp;quot;checked&amp;quot; end if %> >

when I click the boxes, I'll be sending the ID number not 1 or 0.


strIDs = Replace(Request.Form, &amp;quot;&amp;amp;CbOwns=&amp;quot;, &amp;quot;,&amp;quot;)
strIDs = Replace(strIDs, &amp;quot;CbOwns=&amp;quot;, &amp;quot;&amp;quot;)
arrIDs = Split(strIDs, &amp;quot;,&amp;quot;)

this would show only the ID number of the boxes that I checks.
so if 1,2,5,7 are checked that is what the update.asp sees.
which is what I need.

Thanks again for your replys they helped alot.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close