473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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
4 1623
jhardman
3,406 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
3,406 Expert 2GB
Thanks for posting your solution, and glad you got it working!

Jared
Dec 14 '07 #5

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

Similar topics

3
by: jason | last post by:
How does one loop through the contents of a form complicated by dynamic construction of checkboxes which are assigned a 'model' and 'listingID' to the NAME field on the fly in this syntax:...
7
by: Drew | last post by:
I have a db table like the following, UID, int auto-increment RegNo Person Relation YearsKnown Now here is some sample data from this table,
17
by: Roland Hall | last post by:
Is there a way to return multiple values from a function without using an array? Would a dictionary object work better? -- Roland Hall /* This information is distributed in the hope that it...
3
by: james.dixon | last post by:
Hi I was wondering if anyone else had had this problem before (can't find anything on the web about it). I have three select elements (list boxes - from here on I'll refer to them as 'the...
3
by: gregory.sharrow | last post by:
I need to secure a datawarehouse table at the row level based on 1 to many keys on that table. A user should only see the rows they have access to. I need to be able to figure out which rows they...
6
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing...
4
by: Matt Ratliff | last post by:
Hello, I would appreciate any assistance you have with the following problem: I have (as an example) an array of values as follows: arrayvalues=new Array("0001","0003","0005") where each is the...
5
by: Neil | last post by:
"lyle" <lyle.fairfield@gmail.comwrote in message news:48c3dde7-07bd-48b8-91c3-e157b703f92b@f3g2000hsg.googlegroups.com... Question for you. I'm doing something similar, only, instead of opening...
4
by: Chronictank | last post by:
Hi, as a bit of background (and seeing as it is my first post :)) i am a complete newbie at perl. I literally picked it up a week ago to do this project as it seemed like the best choice for a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.