×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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.

Students Click Here

Dreamweaver and ASP

Add multiple records from one table to another by jedel
Posted: 16 Jun 08 (Edited 17 Jun 08)

I was stuck with this problem for a number of months trying to figure out how to do this. I searched forum after forum and posted numerous questions and never really got a straight answer for what I wanted.

Before I start though, I's like to thank johnwm and lebisol for their suggestions on this issue as they put me on the right track. I would be unfair not to recognise their efforts.

OK,

I started of this successful method by first following the steps at the Dr Dev page located below:

http://www.drdev.net/article11.asp

So, to begin

AS the page above states, Create a records set on the page where you have the data that you want to transfer across to the new table.
Then create the form and the fields for each of the records, the same as you would when you create and update form, placing the record data into the form fields. Make sure that this is in one row of a table as we will be making a repeating region for these records.

When you have all of that setup, place the repeating region over the row where you have the records displayed so that you will see all of the records. I haven't tried setting up the table for paging, but hey, try it and see. The worst that could happen is that you will have to add records from each page before you proceed to the next.

OK, Now go into the code area of the page and set up a variable to identify each record and place just below the repeat region code:

CODE

<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<% intRecID =(Recordset1.Fields.Item("RecID").Value) ' Store the current RecordID in a variable %>

Now, place this variable in front of each form name:

CODE

<tr><td><% =intRecID%><input name="HidRecID"<% =intRecID%>" type="hidden" value="<% =intRecID%>" /></td><td><label>
              <input name="Surname<% =intRecID%>" type="text" id="Surname" value="<%=(knectMbr.Fields.Item("mbr_Surname").Value)%>" size="15" />
                        </label></td>
            <td bgcolor="#E2E2E2" class="normalTxtnobox"><input name="Firstname<% =intRecID%>" type="text" id="Firstname" value="<%=(knectMbr.Fields.Item("mbr_Firstname").Value)%>" size="15" /></td>
[/color]

AS you can see, I placed the variable and showed it in the first cell of each row. You don't have to do this, but You will need to place the hidden field in each row (highlighted in green)

Once this is done, place another hidden field at the bottom of your form. This is explained in Dr Devs page and it holds the records for later on when we create our array. outside of the repeat region.

CODE

<%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  knectMbr.MoveNext()
Wend
%>
<tr>
            <td colspan="7" align="left" bgcolor="#E2E2E2" class="normalTxtnobox"><label>
              <input name="hidRecID" type="hidden" id="hidRecID" size="80" />
            </label></td>

You can show this field during the development of your page for now to confirm that you code is working. Once you are happy with that is works, just hide it again.

OK, That's it for the form for now. Now we add a chunk of code from the Dr Dev site into the header section of our page. Here it is:

CODE

<script type="text/javascript">
function RecUpdate(RecID){
var ThisID = "*" + (RecID) + "*"
if (document.AddMbrFRM.hidRecID.value == ""){
document.AddMbrFRM.hidRecID.value = (ThisID)
}
if (document.AddMbrFRM.hidRecID.value != ""){
var str = document.AddMbrFRM.hidRecID.value;
var pos = str.indexOf(ThisID);
if (pos == -1) {
document.AddMbrFRM.hidRecID.value = document.AddMbrFRM.hidRecID.value + ", " + (ThisID)
}
}
}
</script>

You can read about this code in the Dr Dev page I linked to above.

Just make sure that if you changed the variable names for you page, you need to match the variable name in this code as well.

This code will identify which record you have modified and only add those records. In my situation, I was going to modify every record by thye use of a select field, so I place the "Onchange" event in that field like so:

CODE

<select name="Attended<% =intRecID%>" id="Attended<% =intRecID%>" onchange="RecUpdate('<%= intRecID %>')">
                <option>Select</option>
                <option value="Yes">Yes</option>
                <option value="No">No</option>
              </select>

So, when the user selects yes or no, the javascript function will record the change into the hidden field we placed at the bottom of the form. You don't need to use a select field. you can use pretty much anything you like as long as you place the "onChange" event shown above in the field. Dr Devs page has the onChange event on every field to record any changes to the data. The most common method is to use a check box.

NOW, the last chunk of code I had to modify from Dr Devs page as his was to update records while I wanted to add records to a new table. The principle is the same though.

I'll place the entire chunk here, you should be able to see how it all works;

CODE

<%
If Request("button")<>"" Then
intRecID = Replace(Request("hidRecID"), "*", "")
   arrRecIDs = Split(intRecID, ", ")
   For i = 0 to Ubound(arrRecIDs)
   SN=Replace(Request("Surname"&arrRecIDs(i)),"'","''")
   FN = Replace(Request("Firstname"&arrRecIDs(i)),"'","''")
   EM = Replace(Request("Email"&arrRecIDs(i)),"'","''")
   PH = Replace(Request("Ph" & arrRecIDs(i)), "'", "''")
   MOB = Replace(Request("Mob" &arrRecIDs(i)),"'","''")
   GP = Replace(Request("GroupID" & arrRecIDs(i)),"'","''")

   DTE=Replace(Request("Meet_Date"&arrRecIDs(i)),"'","''")

   ATT = Replace(Request("Attended"&arrRecIDs(i)),"'","''")
       TDTE=Replace(Request("txtDate"&arrRecIDs(i)),"'","''")       MBRID=Replace(Request("MbrID"&arrRecIDs(i)),"'","''")
   GPN = Replace(Request("Group"&arrRecIDs(i)),"'",''")
    
Set MbrIns = Server.CreateObject ("ADODB.Command")
MbrIns.ActiveConnection = MM_connect_STRING
MbrIns.CommandText = "INSERT INTO attendanceTBL (Surname, Firstname, Email, Ph, Mob, GroupID, Meet_Date, Attended, txtDate, GpName, MbrID)  VALUES ('"_
 &SN&"','"&FN&"','"&EM&"','"&PH&"','"&MOB&"','"&GP&"','"&DTE&"','"&ATT&"','"&TDTE&"','"&GPN&"','"&MBRID&"')"
 
MbrIns.CommandType = 1
MbrIns.CommandTimeout = 0
MbrIns.Prepared = true
MbrIns. Execute()
Next
Response.Redirect("Index.asp")
End IF
%>

So, at the top is an if then statement to activate the code when the user hits the submit button the next section of code strips the asterix from the number and generates the array. You an then make variables for as many fields as you want to transfer across to the new table. The last 4 or 5 in my example were actually hidden fields storing Session data so I can sort the records later.
After that is the SQL Code. This is where I became unstuck in a big way until I got some help, but this code works nicely. Just remember, you need to place the order of the values in the same order as the fields.

After that we redirected the user and closed off the process.

And that is it! all from the one page. I hope that this helps you. If anybody reading this (meaning those millions of people that have far more experience than I) see fundamental flaws in this process, or can improve on it in any way. Feel free.

I wish to stress however, that when you give your input, write it in such a manner that people with only a little knowledge can understand and use you code. No "skeleton" code. This is an FAQ and as such it should allow people to copy code and paste it into their projects in order to learn how to do these tasks.

Thanks

Dean  

Back to Adobe FAQ Index
Back to Adobe Forum

My Archive

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