Multiple record edit problem

[jedel]

OK,

I have spent too much time on this alone. I have tried so many ways to achieve this but to no avail. I have looked and looked for answers and have found none that I can understand.

I thought it would be a reasonable easy this to do, however I must be mistaken.

THE SCENARIO
I have three tables in a database, GroupTBL, MbrTBL, and attendTBL.

GroupTBL and MbrTBL has a field that I use as a sort query called GroupID. This field is the primary key for GroupTBL.

When a user pulls their details for the GroupTBL, they can then get the members of that group. No problems so sar.

Each Week the group leader has a meeting and enters a report online.

THE PROBLEM
when the group leader gets to the MbtTBL, they are able to select from a selection box (yes,No) if that particular member attended.

When the group leader has completed this for all of their members, I want them to click on a submit button at the bottom of the list and have all of the data in that form add new records to the attendTBL.

I cannot for love nor money get this to work!

I have tried the FAQ in this forum, no luck, I even downloaded the Dreamweaver developers toolkit. While this solves another problem I have, cannot solve this one.

Does ANYBODY out there know how to add multiple records from one table to another. Alternatively some website somewhere...anywhere that I can goto and get taken through STEP BY STEP on how to achieve this.

Regards

Completely desparate

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[johnwm]

Generate the list of checkboxes on the form from the query results for that group. Use the memberID as the value for the checkbox. Use a hidden field to contain the number of checkboxes generated.

Submit the form to a non-displayed asp page that loops through the checkboxes, using the hidden field value as a counter, and do an insert for each checkbox set.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[jedel]

johnwm

Thanks for the early reply, but your detailed answer leaves a little to be desired.

Yes I can place a checkbox in the repeating region of the recordset. but what then?

Loop through the records from another page?? Sounds very much like the explanation on the FAQ area of this forum...that did not work for this instance.

If you are willing,Can you take me through this one step at a time?

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[johnwm]

I don't understand your 'repeating region of the recordset'. I am suggesting that you generate a form in asp from the query.

Pseudocode for that:
Get recordset from query
Response.Write ("<form id='form1' name='form1' method='post' action='insert.asp'>")
Do While Not rs.EOF
Response.Write ("<input type='checkbox' name='" & rs.fields(1) & "' value= '"& rs.fields(1) & "' />"
Loop

The Action of that form would be the second asp page (insert.asp). That second page could simply be a much simplified version of faq333-6618 by gbaughma as you only have to deal with inserts, and are only using checkboxes.

When you have your code written, come back and show us what you have if there are any specific problems

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[jedel]

OK,

Here is the first problem. You said to bind the priomary key (mbr_ID) to the check box field. This gives me an error (detailed below)

the SQL from the connection is such:

<%
Dim knectMbr
Dim knectMbr_cmd
Dim knectMbr_numRows

Set knectMbr_cmd = Server.CreateObject ("ADODB.Command")
knectMbr_cmd.ActiveConnection = MM_connect_STRING
knectMbr_cmd.CommandText = "SELECT * FROM KnectMbrTBL WHERE mbr_GroupID = ?" 
knectMbr_cmd.Prepared = true
knectMbr_cmd.Parameters.Append knectMbr_cmd.CreateParameter("param1", 5, 1, -1, knectMbr__MMColParam) ' adDouble

Set knectMbr = knectMbr_cmd.Execute
knectMbr_numRows = 0
%>

This SQL selects a number of records based on the group ID number.

Here is my attempt at writing the records

<%
Response.Write("<form id='Mbrs' name='Mbrs' method='post' action='MbrAttendanceSubmit.asp'>")
Response.Write("<Table><tr class='heading'><td></td><td>Surname</td><td>First Name</td><td>Email</td><td>Phone</td><td>Mobile</td><td>Attended</td></tr>")
Do While NOT knectMbr.EOF
Response.write("<tr><td><input name='"& knectMbr.mbr_ID & "' type='checkbox' value='"& knectMbr.mbr_ID & "' /><td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_Surname").Value) & "<td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_Firstname").Value) & "<td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_email").Value) & "<td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_Ph").Value) & "<td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_Mob").Value) & "<td>")
Response.Write("<td><select name='Attended' size='6'>")
Response.Write("<option value='Yes'>Yes</option>")
Response.Write("<option value='No'>No</option>")
Response.Write("</select></td></tr>")
Loop
Response.Write("</table></form>")
	%>

the error states:

Microsoft VBScript runtime  error '800a01b6'

Object doesn't support this property or method: 'mbr_ID'

/admin/knect/mbr/MbrAttendance.asp, line 194

Line 194 is:

Response.write("<tr><td><input name='"& knectMbr.mbr_ID & "' type='checkbox' value='"& knectMbr.mbr_ID & "' /><td>")

The field your binding is an integer while checkboxes are boolean. How can you do this?

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[johnwm]

I'm not sure where knectMbr.mbr_ID comes from - it isn't part of the recordset. Assuming that mbr_ID is the ID field in your recordset you should use the same construction as you do for the other fields.

You may have misunderstood what an HTML form checkbox does. There is no 'binding' going on. A checked checkbox returns the value you assign to it, an unchecked checkbox returns nothing. See

http://www.w3schools.com/html/html_forms.asp

and the following pages for the basics of HTML forms.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[jedel]

knectMbr is the name of the record set. Like you used in your second post "rs.fields(1)".

I'm not sure what you mean by "rs.fields(1)" I was assuming that you used this code to show me that I needed to place the name of the field from my table in its place?

I'm not sure of the relevance of this checkbox anyway. You may have misunderstood my first post, or I probably didn't explain it well enough. The user will be editing every record that he sees on this page and every record that he sees needs to be added to the attendance table. We want to record all people who attended meetings as well as those that did not, hence the selection box at the end of the field.



-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jedel]

OK,
I have the first page showing the records that I want as well as the ability to check the checkboxes and edit all of the fields. The page is located at the following

http://www.calvarycc.org.au/admin/knect/mbr/MbrAttendance.asp

Here is the ASP code for the records:

<form id="AddMbrFRM" name="AddMbrFRM" method="post" action="MbrAttendanceSubmit.asp">
      <table width="642" border="0" cellspacing="2" cellpadding="0">
        <tr>
          <td width="21" class="heading">&nbsp;</td>
          <td width="91" class="heading">Surname</td>
          <td width="90" class="heading">First Name</td>
          <td width="180" class="heading">email</td>
          <td width="72" class="heading">Phone</td>
          <td width="72" class="heading">Mobile</td>
          <td width="100" class="heading">Attended</td>
        </tr>
        <% 
While ((Repeat1__numRows <> 0) AND (NOT knectMbr.EOF)) 
%>
          <tr>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><label>
              <input type="checkbox" name="<%=(knectMbr.Fields.Item("mbr_ID").Value)%>" value="<%=(knectMbr.Fields.Item("mbr_ID").Value)%>"  />
            </label></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><label class="normalTxtnobox">
              <input name="Surname" type="text" id="Surname" value="<%=(knectMbr.Fields.Item("mbr_Surname").Value)%>" size="15" />
            </label></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Firstname" type="text" id="Firstname" value="<%=(knectMbr.Fields.Item("mbr_Firstname").Value)%>" size="15" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Email" type="text" id="Email" value="<%=(knectMbr.Fields.Item("mbr_email").Value)%>" size="30" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Ph" type="text" id="Ph" value="<%=(knectMbr.Fields.Item("mbr_Ph").Value)%>" size="12" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Mob" type="text" id="Mob" value="<%=(knectMbr.Fields.Item("mbr_Mob").Value)%>" size="12" /></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><label>
              <select name="Attended" class="normalTxtnobox" id="Attended">
                <option value="Yes">Yes</option>
                <option value="No">No</option>
              </select>
              <input name="Meet_Date" type="hidden" id="Meet_Date" value="<%=Date() %>" />
              <input name="GroupID" type="hidden" id="GroupID" value="11" />
            </label></td>
          </tr>
          
          <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  knectMbr.MoveNext()
Wend
%><tr>
            <td colspan="7" align="right" bgcolor="#E2E2E2" class="normalTxtnobox"><label>
              <input type="submit" name="button" id="button" value="Submit" />
            </label></td>
          </tr>
            </table>
    </form>

Yes, I use dreamweaver...call me a pagan.

I read the thread link you sent me and it looked as though the code was fairly "generic", ie you only need to change the connection details for it to work. Well, I'm getting this error.

"Microsoft VBScript compilation error '800a0400'

Expected statement

/admin/knect/mbr/MbrAttendanceSubmit.asp, line 34

(Request.Form(Field),"'","''")
^

the last line in this error is line 34 in its entirety.

And now I have no idea whats next because I do not understand the code.

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[johnwm]

I misunderstood your use of a dropdown box for a yes/no choice - I assumed you meant a checkbox, which would normally be used here. The sample I pointed you to was a tutorial, not intended as production code.

And now I have no idea whats next because I do not understand the code.

Here lies the nub of the problem with DW - it generates confusing code which is usually harder to decipher than your own. DW works fine if you want a DW site - if you want a proper site you probably need to go back to those tutorials and learn how the code works.

If you need to stay with DW, then throw all this advice away and start again in forum248 or forum770 as appropriate.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[jedel]

Yes, you're right...DW does set up some confusing code. I would still like to learn the "pure ASP" way though. DW does not do all things unfortunately.

So I'll cut back to my first chunk of code and see if I can't get that page to display.

Back in an hour or so...

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jedel]

OK, Back to the ASP code. Here is the connection

<%
Dim knectMbr
Dim knectMbr_cmd
Dim knectMbr_numRows

Set knectMbr_cmd = Server.CreateObject ("ADODB.Command")
knectMbr_cmd.ActiveConnection = MM_connect_STRING
knectMbr_cmd.CommandText = "SELECT * FROM KnectMbrTBL WHERE mbr_GroupID = 11"  'When the code is working, will become a Session value
Set knectMbr = knectMbr_cmd.Execute
%>

the database is connected via dsn

Here is the recordset code:

<%
Response.Write("<form id='Mbrs' name='Mbrs' method='post' action='MbrAttendanceSubmit.asp'>")
Response.Write("<Table><tr class='heading'><td></td><td>Surname</td><td>First Name</td><td>Email</td><td>Phone</td><td>Mobile</td><td>Attended</td></tr>")
Do While NOT knectMbr.EOF
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_Surname").Value) & "<td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_Firstname").Value) & "<td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_email").Value) & "<td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_Ph").Value) & "<td>")
Response.Write("<td>" &(knectMbr.Fields.Item("mbr_Mob").Value) & "<td>")
Response.Write("<td><select name='Attended' size='6'>")
Response.Write("<option value='Yes'>Yes</option>")
Response.Write("<option value='No'>No</option>")
Response.Write("</select></td></tr>")
Loop
Response.Write("</table></form>")
    %>

it did not work. Here is the error

Response object error 'ASP 0251 : 80004005'

Response Buffer Limit Exceeded

/admin/knect/mbr/MbrAttendance.asp, line 0

Execution of the ASP page caused the Response Buffer to exceed its configured limit.


Seeing as this is at line 0, I must ask, is the code your showing me vbscript or javascript?

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[johnwm]

The only code I showed was marked as pseudocode - in other words a notional skeleton, not in any specific language. For instance, it didn't specifically say rs.Movenext before the loop. If you don't move through the recordset you have an infinite loop and will of course overflow the buffer. It's also worth noting that some hosts have their response.buffer size set low (to avoid this type of script from crashing their server). You can disable buffering by setting Response.Buffer = False at the top of the script. I would advise you reset it at the end of the script

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[wvdba]

you can insert multiple records from one table into another:

insert into table1 (field1, field2, field3)
       select table2.field1, table2.field2, table2.field3 from table2 where some_condition_is_true)