By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,979 Members | 1,452 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,979 IT Pros & Developers. It's quick & easy.

Inserting table rows as unique records in a form

P: 87
Greetings,

I have an ASP page with a 5x5 table embedded inside an Insert Record Form. This table contains several fields (mostly drop down list menus) and is used for corporate timekeeping (users record their daily hours by making selections from the list menus).

My challenge is to figure out how the user can complete all rows of the table and click one button to submit the entire form AND have each row inserted as a unique record. In other words, all fields in row 1 are inserted into the table as record 1, row 2 is inserted as record 2, and so on.

All fields within a row are uniquely named (e.g., every list/menu in row 1 is named with "..01" at its end, and so on for other rows). I can easily make the table submit row 1 into the table as record 1, but haven't figured out how to submit the entire table as five unique records.

Others more advanced than me have used ColdFusion to code this functionality but that's beyond my knowledge base. They use "mini-submits" utilizing the onchange event to submit the form when a list/menu selection is made (thereby refreshing the values in subsequent list/menus), but I haven't been able to replicate this in ASP.

I can post code for what I have thus far should anyone want to see it, but thought I'd pose the question first to see if anyone has any advice or URL links/examples to point me toward.

Regards,

JM
Jun 24 '08 #1
Share this Question
Share on Google+
10 Replies


DrBunchman
Expert 100+
P: 979
JM,

If you put your database insert code into a function then you can call it once for each row of data. You could even create a loop which appends the row number to the end of the control name which then passes the request value to the function. Here's a quick example of what I mean:

Expand|Select|Wrap|Line Numbers
  1.  
  2. <form name="form1" action="InsertDifferentRows.asp">
  3.  <input type="text" name="txtBox1" />
  4.  <input type="text" name="txtBox2" />
  5.  <input type="text" name="txtBox3" />
  6.  <input type="submit" />
  7. </form>
  8. <% 
  9. Sub InsertToDB(txtbox)
  10.      'Put your insert to DB code here
  11. End Sub
  12.  
  13. 'Define the first part of the names that you've called each of the controls
  14. Dim Control1, Control2 etc
  15. Control1 = "txtBox"
  16.  
  17. 'Create a loop, appending the loop counter to the end of the controlname to get the correct one for the row and pass the variables to the function
  18. For i = 1 to 5
  19.  InsertToDB Request(Control1 & i)
  20. Next
  21. %>
  22.  
Does this make any sense to you? Does it help with your problem?

Let me know,

Dr B
Jun 25 '08 #2

P: 87
It makes sense in concept, but to understand it better I need to build out the code and see how it works. I'll work on it in the coming week and post any questions or problems should I have them.

Thanks, Dr B.

JM
Jun 25 '08 #3

P: 87
Well, I've tried coding this and have run into a problem.

First, here's the code (Note I'm testing this on a 3x3 table):

Expand|Select|Wrap|Line Numbers
  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
  2. <!--#include file="Connections/EZTrackingTool.asp" -->
  3. <%
  4. Dim MM_editAction
  5. MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
  6. If (Request.QueryString <> "") Then
  7.   MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
  8. End If
  9.  
  10. ' boolean to abort record edit
  11. Dim MM_abortEdit
  12. MM_abortEdit = false
  13. %>
  14.  
  15. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  16. <html xmlns="http://www.w3.org/1999/xhtml">
  17. <head>
  18. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  19. <title>Untitled Document</title>
  20. </head>
  21.  
  22. <body>
  23. <form METHOD="POST" name="form1" action="<%=MM_editAction%>">
  24.   <table width="66%">
  25.     <tr>
  26.       <td>EZ Workstream</td>
  27.       <td>Program Name</td>
  28.       <td>Project Name</td>
  29.     </tr>
  30.     <tr>
  31.       <td><input name="txtBox1" type="text" id="txtBox1" /></td>
  32.       <td><input name="txtBox2" type="text" id="txtBox2" /></td>
  33.       <td><input name="txtBox3" type="text" id="txtBox3" /></td>
  34.     </tr>
  35.     <tr>
  36.       <td><input name="txtBox4" type="text" id="txtBox4" /></td>
  37.       <td><input name="txtBox5" type="text" id="txtBox5" /></td>
  38.       <td><input name="txtBox6" type="text" id="txtBox6" /></td>
  39.     </tr>
  40.     <tr>
  41.       <td><input name="txtBox7" type="text" id="txtBox7" /></td>
  42.       <td><input name="txtBox8" type="text" id="txtBox8" /></td>
  43.       <td><input name="txtBox9" type="text" id="txtBox9" /></td>
  44.     </tr>
  45.   </table>
  46.     <input type="submit" />
  47.     <input type="hidden" name="MM_insert" value="form1" />
  48. </form>
  49. <% 
  50. Sub InsertToDB(txtbox)
  51. If (CStr(Request("MM_insert")) = "form1") Then
  52.   If (Not MM_abortEdit) Then
  53.     ' execute the insert
  54.     Dim MM_editCmd
  55.  
  56.     Set MM_editCmd = Server.CreateObject ("ADODB.Command")
  57.     MM_editCmd.ActiveConnection = MM_EZTrackingTool_STRING
  58.     MM_editCmd.CommandText = "INSERT INTO EZPTS_Test (workstream, program_name, project_name, workstream, program_name, project_name, workstream, program_name, project_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)" 
  59.     MM_editCmd.Prepared = true
  60.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 50, Request.Form("txtBox1")) ' adVarWChar
  61.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 50, Request.Form("txtBox2")) ' adVarWChar
  62.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 50, Request.Form("txtBox3")) ' adVarWChar
  63.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 202, 1, 50, Request.Form("txtBox4")) ' adVarWChar
  64.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 50, Request.Form("txtBox5")) ' adVarWChar
  65.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 202, 1, 50, Request.Form("txtBox6")) ' adVarWChar
  66.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 202, 1, 50, Request.Form("txtBox7")) ' adVarWChar
  67.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 202, 1, 50, Request.Form("txtBox8")) ' adVarWChar
  68.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param9", 202, 1, 50, Request.Form("txtBox9")) ' adVarWChar
  69.     MM_editCmd.Execute
  70.     MM_editCmd.ActiveConnection.Close
  71.  
  72.     ' append the query string to the redirect URL
  73.     Dim MM_editRedirectUrl
  74.     MM_editRedirectUrl = "TEST_InsertMultRows.asp"
  75.     If (Request.QueryString <> "") Then
  76.       If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
  77.         MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
  78.       Else
  79.         MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
  80.       End If
  81.     End If
  82.     Response.Redirect(MM_editRedirectUrl)
  83.   End If
  84. End If
  85. End Sub
  86.  
  87. 'Define the first part of the names that you've called each of the controls
  88. Dim Control1
  89. Control1 = "txtBox"
  90.  
  91. 'Create a loop, appending the loop counter to the end of the controlname to get the correct one for the row and pass the variables to the function
  92. For i = 1 to 3
  93.  InsertToDB Request(Control1 & i)
  94. Next
  95. %>
  96. </body>
  97. </html>
Second, when I fill out the form and click 'Submit', I get the following 'Page Cannot be Displayed' error:

[HTML]Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Duplicate output destination 'workstream'.
/ez/TEST_InsertMultRows.asp, line 69[/HTML]

Any suggestions how to fix?
Jun 30 '08 #4

P: 87
Forgive me if I'm being impatient, but I would greatly appreciate anyone's help with this troubleshooting issue.

Regards,

JM
Jul 2 '08 #5

DrBunchman
Expert 100+
P: 979
Hi JM,

Sorry for not getting back to you sooner on this.

The error is caused by the fact that you are specifying multiple instances of the same column name in your insert statement. In your example above you have tried to insert data to workstream, program_name & project_name three times each in the same statement.

Dr B
Jul 3 '08 #6

P: 87
Thanks, Dr B. That makes sense and I'm getting closer, but there's still something wrong in my code.

After removing the multiple instances of the same column name in my insert statement, the InsertToDB sub still doesn't write the values for each row into a unique record. Instead, it inserts the values for each column into the same table record.

For instance, if I put the word 'test' in textboxes A,D and G (in column 1) and submit the form, the 'workstream' record values look like "test, test, test" instead of each being in its own unique record. Does that make sense?

I know there's probably something simple I'm missing but I can't figure it out. Here's the InsertToDB code...

Expand|Select|Wrap|Line Numbers
  1. <% 
  2. Sub InsertToDB(txtbox)
  3.  
  4. Dim MM_editAction
  5. MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
  6. If (Request.QueryString <> "") Then
  7.   MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
  8. End If
  9.  
  10. ' boolean to abort record edit
  11. Dim MM_abortEdit
  12. MM_abortEdit = false
  13.  
  14. If (CStr(Request("MM_insert")) = "form1") Then
  15.   If (Not MM_abortEdit) Then
  16.     ' execute the insert
  17.     Dim MM_editCmd
  18.  
  19.     Set MM_editCmd = Server.CreateObject ("ADODB.Command")
  20.     MM_editCmd.ActiveConnection = MM_EZTrackingTool_STRING
  21.     MM_editCmd.CommandText = "INSERT INTO EZPTS_Test (workstream, program_name, project_name) VALUES (?, ?, ?)" 
  22.     MM_editCmd.Prepared = true
  23.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 50, Request.Form("txtBoxA")) ' adVarWChar
  24.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 50, Request.Form("txtBoxB")) ' adVarWChar
  25.     MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 50, Request.Form("txtBoxC")) ' adVarWChar
  26.     MM_editCmd.Execute
  27.     MM_editCmd.ActiveConnection.Close
  28.  
  29.     ' append the query string to the redirect URL
  30.     Dim MM_editRedirectUrl
  31.     MM_editRedirectUrl = "TEST_InsertMultRows.asp"
  32.     If (Request.QueryString <> "") Then
  33.       If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
  34.         MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
  35.       Else
  36.         MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
  37.       End If
  38.     End If
  39.     Response.Redirect(MM_editRedirectUrl)
  40.   End If
  41. End If
  42. End Sub
  43.  
  44. 'Define the first part of the names that you've called each of the controls
  45. Dim Control1
  46. Control1 = "txtBox"
  47.  
  48. 'Create a loop, appending the loop counter to the end of the controlname to get the correct one for the row and pass the variables to the function
  49. For i = 1 to 3
  50.  InsertToDB Request(Control1 & i)
  51. Next
  52. %>
Here also is the code for the form...

Expand|Select|Wrap|Line Numbers
  1. <form METHOD="POST" name="form1" action="<%=MM_editAction%>">
  2.   <table width="66%">
  3.     <tr>
  4.       <td>EZ Workstream</td>
  5.       <td>Program Name</td>
  6.       <td>Project Name</td>
  7.     </tr>
  8.     <tr>
  9.       <td><input name="txtBoxA" type="text" id="txtBoxA" /></td>
  10.       <td><input name="txtBoxB" type="text" id="txtBoxB" /></td>
  11.       <td><input name="txtBoxC" type="text" id="txtBoxC" /></td>
  12.     </tr>
  13.     <tr>
  14.       <td><input name="txtBoxA" type="text" id="txtBoxD" /></td>
  15.       <td><input name="txtBoxB" type="text" id="txtBoxE" /></td>
  16.       <td><input name="txtBoxC" type="text" id="txtBoxF" /></td>
  17.     </tr>
  18.     <tr>
  19.       <td><input name="txtBoxA" type="text" id="txtBoxG" /></td>
  20.       <td><input name="txtBoxB" type="text" id="txtBoxH" /></td>
  21.       <td><input name="txtBoxC" type="text" id="txtBoxI" /></td>
  22.     </tr>
  23.   </table>
  24.     <input type="submit" />
  25.     <input type="hidden" name="MM_insert" value="form1" />
  26. </form>
Please advise if you can figure out what I'm doing wrong.

Thanks, Dr B

- JM
Jul 3 '08 #7

P: 87
Can someone please help me troubleshoot the above problem? Whatever advice anyone can offer would be supremely appreciated!

JM
Jul 10 '08 #8

DrBunchman
Expert 100+
P: 979
Hi JM,

Sorry for the delay in getting back to you on this (again!).

The problem is that you are passing the name of the text box control from your loop into your InsertToDB() Sub but then not using it. You have used txtBoxA, txtBoxB & txtBoxC in the insert which is why it is always inserting these values each time you call it.

Perhaps it would be easier to not bother with the loop but to call the InsertToDB sub three times like this:
Expand|Select|Wrap|Line Numbers
  1.  InsertToDB(txtBoxA, txtBoxB , txtBoxC) 
  2. InsertToDB(txtBoxD, txtBoxE , txtBoxF)
  3. InsertToDB(txtBoxG, txtBoxH , txtBoxI)
  4.  
Then in your sub you would change the first line to:
Expand|Select|Wrap|Line Numbers
  1.  Sub InsertToDB(Var1, Var2, Var3)
And then your insert lines to:
Expand|Select|Wrap|Line Numbers
  1.  
  2. MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 50, Request.Form(Var1)) ' adVarWChar
  3. MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 50, Request.Form(Var2)) ' adVarWChar
  4. MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 50, Request.Form(Var3)) ' adVarWChar
  5.  
On each call to the sub you are passing the names of three controls that you wish to request the values of and inserting them into the db. Does this make sense?

Let me know how you get on,

Dr B
Jul 11 '08 #9

P: 87
Don't I have to DIM the three variables? If so, how would you suggest I do that?
Jul 14 '08 #10

DrBunchman
Expert 100+
P: 979
Do you mean Var1, Var2 & Var3? If so then you've already done it: they are declared by naming them as the Sub's parameters.
Jul 15 '08 #11

Post your reply

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