467,875 Members | 1,479 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,875 developers. It's quick & easy.

Multiple form values dimensioned by userID, passed to an array then writing to dB

Hello all,

I have a form which is generated using a database query - the recordset is filled with agent details, login time and the like, and there are as many rows in the table as records in the set (plus table header).

My problem is that I do not know how to pass the details of each record to an array session variable, once the form is submitted, and subsequently write all of this info to a dB.

I have no problem doing this when there are pre-defined named fields on the form e.g.

Expand|Select|Wrap|Line Numbers
  1. ...
  2. <INPUT id="shift_start" />
  3. ...
  4.  
which is then picked up on the next page by:

Expand|Select|Wrap|Line Numbers
  1. Session("ShiftLength") = Request.Form("shift_start") 
  2. ..
  3.  
and written to the dB by:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Agent_shifts (Shift_Start) VALUES (Session("ShiftStart"))
  2.  
Here however, the form is made by:

Expand|Select|Wrap|Line Numbers
  1. <FORM name="Init_Form" method="post" action="agent_attendance_submit.asp" <!--onsubmit="submit_details()" --> <!--onkeypress="return event.keyCode!=13"--> >
  2.  
  3. <table border=0 id="table1">
  4.  
  5. <tr>
  6.     <TH style="display: none">ID</TH>
  7.     <TH>Selected</TH>
  8.     <TH>Name</TH>
  9.     <TH>Login-Time</TH>
  10.     <TH>Logout-Time</TH>
  11.     <TH>Lunch Yes/No</TH>
  12.     <TH>Lunch Start</TH>
  13.     <TH>Lunch End</TH>
  14. </TR>
  15.  
  16. <%
  17. Set db02 = Server.CreateObject( "ADODB.Connection" )
  18. OdbcConStr = Session("AdvanceConnection") & ""
  19. db02.Open OdbcConStr
  20. SQLQuery = "EXEC Daily_Agent_Attendance"
  21.  
  22. Set MyData02 = db02.Execute( SQLQuery )
  23. Do While Not MyData02.EOF 
  24. %>
  25.  
  26. <TR id="<%=MyData02("UserID")%>" name="<%=MyData02("UserID")%>">
  27.  
  28.     <TD align=Default valign=Default style="display: none">
  29.         <P><%= MyData02("UserID")%> </P>
  30.     </TD>
  31.     <TD>
  32.         <INPUT type="checkbox" id="dB_write_flag_<%=MyData02("UserID")%>" name="dB_write_flag_<%=MyData02("UserID")%>" value="yes" onclick="agentselected(<%=MyData02("UserID")%>)"/>
  33.     <TD>
  34.         <P><%= MyData02("UserName")%> </P>
  35.     </TD>
  36.     <TD>
  37.         <INPUT id="loginTime_<%=MyData02("UserID")%>" value="<%= MyData02("R_LoginTime_OnlyTime")%>" size=5/>
  38.     </TD>
  39.     <TD>
  40.         <INPUT id="logoutTime_<%=MyData02("UserID")%>" value="<%= MyData02("R_LogoutTime_OnlyTime")%>" size=5/>
  41.     </TD>
  42.     <TD>
  43.         <INPUT type="checkbox" name="checkbox_<%= MyData02("UserID")%>" value="yes" onclick="showlunch(<%=MyData02("UserID")%>)"/>
  44.     </TD>
  45.     <TD>
  46.         <INPUT size=5 id="lunchstart_<%=MyData02("UserID")%>" name="lunchstart_<%=MyData02("UserID")%>" style="display: none"/>
  47.     </TD>
  48.     <TD>
  49.         <INPUT size=5 id="lunchend_<%=MyData02("UserID")%>" name="lunchend_<%=MyData02("UserID")%>" style="display: none"/>
  50.     </TD>    
  51. </TR>
  52.  
  53.  
  54. <%
  55. MyData02.MoveNext
  56. Loop
  57. %>
  58.  
  59. </table>
  60.  
The above will look like a table of agent names and their login / logout times and lunch, which will then be written to a dB en masse (only those that have the first box set as 'checked' i.e. we don't want to write them all to the dB.

..as you can see the names of the fields are made 'on the fly' based on the UserID (with a naming convention for the field in question i.e. lname = "loginTime_<%=MyData02("UserID")%>...I am aware that the details should be passed to an array, though I don't know, concisely, nor precisely how to do this and the subsequent method of dropping this array into an SQL query.

I would be grateful for any push, shove, nudge, or prod in the right direction!

Thanks

J
Dec 11 '07 #1
  • viewed: 1379
Share:
4 Replies
jhardman
Expert 2GB
J,

The first part, putting all these data in session variables is simplicity itself:
Expand|Select|Wrap|Line Numbers
  1. for each x in request.form
  2.    session(x) = request.form(x)
  3. next
that is the beauty of using for each loops, you don't need to know what each of the items are all called.

There are a couple ways to access this data once you are ready to use it, but basically it all boils down to using the name of the array index as if it was a any string. For example, if you had inputs "user1", "user2", "user3" etc, you might try something like this:
Expand|Select|Wrap|Line Numbers
  1. for y = 1 to 100
  2.    response.write session("user" & i)
  3. next
or if you wanted to pull the numbers out of the db again:
Expand|Select|Wrap|Line Numbers
  1. do until objRS.eof
  2.    response.write session("user" & objRS("userID"))
  3.    objRS.moveNext
  4. loop
Let me know if this helps.

Jared
Dec 13 '07 #2
Hello,

The above explanation is pretty much what I picked up in the reply interval - fortunately I have now got the concept of 'for' loops down - I hope!

In the interests of others that are searching around for a solution to a similar problem here is how I solved this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Set db03 = Server.CreateObject( "ADODB.Connection" )
  3. OdbcConStr = Session("AdvanceConnection") & ""
  4. db03.Open OdbcConStr
  5.  
  6. For counter=1 to Request.Form("dbWrite").Count
  7.     loginDateTime = ""
  8.     logoutDateTime = ""
  9.     'Create a string which is in the proper datetime SQL Server format
  10.     loginDateTime = Request.Form("SmallDate")(counter) & " " & Request.Form("login_Time")(counter)
  11.     logoutDateTime = Request.Form("SmallDate")(counter) & " " & Request.Form("logout_Time")(counter)
  12.  
  13.     'Create the SQL INSERT query
  14.     SQLInsert01 = "INSERT INTO T_Agent_Shifts_Worked_temp (userid, shiftstart, shiftend, shiftlength) VALUES "
  15.     SQLInsert01 = SQLInsert01 & "(" & Request.Form("UserID")(counter) & ", '" & loginDateTime & "'"
  16.     SQLInsert01 = SQLInsert01 & ",'" & logoutDateTime & "', DATEDIFF(s,'" & loginDateTime & "', '" & logoutDateTime & "'))"
  17.  
  18.     'Just a check during testing
  19. Response.Write SQLInsert01 & "<BR><BR>"
  20.  
  21.     db03.Execute( SQLInsert01)
  22. next
  23.  
  24.  
...etc, etc. Close to the heart of this problem was my miscomprehension of for loops and the Request object. The form on the first page showed lots of rows, but I only wanted those rows for which a check-box was checked to be written. Key to getting this data to the dB in the right order i.e. as it was on the form, was, essentially, pivoting the data about the checkbox flag in the 'for' loop. This organised the data by around this field (For counter=1 to Request.Form("dbWrite").Count) and provided the data row by row in the right order (shout if that does not make sense, but if for instance I had used Request.Form("UserID").Count I wouldn't have got the right dataset. Come to think of it (excuse the conciousness stream), the second key part to cracking this was writing a javascript function that disabled rows on the first form depending on whether the "dbWrite" flag was checked i.e. selected. By disabling all the rows at first, allowing the user to select rows, this meant that only the which had been checked pushed information to the Request object, whereas with all rows enabled (default), the Request object picked up everything active on the form. Silly oversight, but you know us novices, that's part of the learning right?!

Thanks for your reply anyway Jared, is helpful to know that I went in the right direction and the solution is as someone else would have done.

I can call this one closed, though feel free if anyone thinks this could be done still better :-)!
Dec 13 '07 #3
ONE more thing that will help learners like myself and could have saved me a lot of time here:

Javascript document.getElementById, uses, obviously, a tags ID, whereas, and this for me was crucial, Request.Form goes on the name of the tags. Therefore, in my Javascript function to switch rows on and off I could write:

Expand|Select|Wrap|Line Numbers
  1.  
  2. function activate_row(parameter){ 
  3. var dbWriteFlag = document.getElementById('dbWrite_' + parameter);
  4. var name = document.getElementById('name_' + parameter);
  5. var loginTime = document.getElementById('login_Time_' + parameter);
  6. var logoutTime = document.getElementById('logout_Time_' + parameter);
  7. var lunchStart = document.getElementById('lunch_Start_' + parameter);
  8. var lunchEnd = document.getElementById('lunch_End_' + parameter);
  9. var lunchFalse = document.getElementById('lunch_False_' + parameter);
  10. var lunchTrue = document.getElementById('lunch_True_' + parameter);
  11.  
  12. if(dbWriteFlag.checked) {
  13.     name.disabled = false;
  14.     loginTime.disabled = false;
  15.     logoutTime.disabled = false;
  16.     lunchStart.disabled = false;
  17.     lunchEnd.disabled = false;
  18.     lunchTrue.disabled = false;
  19.     lunchFalse.disabled = false;
  20. }
  21. else {
  22.     name.disabled = true;
  23.     loginTime.disabled = true;
  24.     logoutTime.disabled = true;
  25.     lunchStart.disabled = true;
  26.     lunchEnd.disabled = true;
  27.     lunchTrue.disabled = true;
  28.     lunchFalse.disabled = true;
  29. }
  30. }
  31.  
which ran against this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. <%
  3.  
  4. Set db02 = Server.CreateObject( "ADODB.Connection" )
  5. OdbcConStr = Session("AdvanceConnection") & ""
  6. db02.Open OdbcConStr
  7. SQLQuery = "EXEC Daily_Agent_Attendance"
  8. Set MyData02 = db02.Execute( SQLQuery )
  9. %>
  10.  
  11. <%Do While Not MyData02.EOF %>
  12.  
  13. <TR id="<%=MyData02("UserID")%>" name="<%=MyData02("UserID")%>">
  14.  
  15.     <TD style="display: none">
  16.         <INPUT name="userid" type="hidden" id="userid" value="<%=MyData02("UserID")%>" />
  17.     </TD>
  18.     <TD style="display: none">
  19.         <INPUT name="smalldate" type="hidden" id="smalldate" value="<%=MyData02("SmallDate")%>"/>
  20.     </TD>
  21.     <TD>
  22.         <INPUT name="dbWrite" type="checkbox" id="dBwrite_<%=MyData02("UserID")%>" onclick="activate_row(<%=MyData02("UserID")%>)" value="yes"/>
  23.     </TD>
  24.     <TD>
  25.         <INPUT name="name" id="name_<%=MyData02("UserID")%>" value="<%= MyData02("UserName")%>"/ disabled>
  26.     </TD>
  27.     <TD>
  28.         <INPUT name="login_Time" id="login_Time_<%=MyData02("UserID")%>" value="<%= MyData02("R_LoginTime_OnlyTime")%>" size=5/ disabled>
  29.     </TD>
  30.     <TD>
  31.         <INPUT name="logout_Time" id="logout_Time_<%=MyData02("UserID")%>" value="<%= MyData02("R_LogoutTime_OnlyTime")%>" size=5/ disabled>
  32.     </TD>
  33.     <TD>
  34.         <INPUT name="lunch_checkbox" id="lunch_True_<%=MyData02("UserID")%>" type="checkbox" onclick="showlunch(<%=MyData02("UserID")%>)" disabled value="True"/>
  35.         <INPUT name="lunch_checkbox" id="lunch_False_<%=MyData02("UserID")%>" type="checkbox" onclick="showlunch(<%=MyData02("UserID")%>)" disabled value="False"/>
  36.     </TD>
  37.     <TD>
  38.         <INPUT size=5 name="lunchStart" id="lunch_Start_<%=MyData02("UserID")%>" disabled style="display: none">
  39.     </TD>
  40.     <TD>
  41.         <INPUT size=5 name="lunchEnd" id="lunch_End_<%=MyData02("UserID")%>" disabled style="display: none">
  42.     </TD>    
  43. </TR>
  44.  
  45.  
...as you can see, the ID for the tags are named, for instance, "login_time_" & the UserID. This same UserID is written server-side into the parameter section of the onclick event for the dB write flag for each row generated: onclick="activate_row(<%=MyData02("UserID")%>)"

..consequently passing a unique parameter for that row to the function and allowing each row to be individually disabled (see previous post re disabling fields meaning data is not passed to Request.Form).

Shout if this is unclear, or you would like me to explain this more
Dec 13 '07 #4
jhardman
Expert 2GB
Thanks for posting your solution, and glad you got it working!

Jared
Dec 14 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by gregory.sharrow | last post: by
4 posts views Thread by Matt Ratliff | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.