Navigation
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
More options
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Add multiple records from one table to another
Dreamweaver and ASP
Add multiple records from one table to another
by
jedel
Posted
(Edited
)
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:
Now, place this variable in front of each form name:
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.
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:
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:
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;
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.
[color #ff0000]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.[/color]
Thanks
Dean
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:
<%
[blue]While (([/blue]Repeat1__numRows [blue]<>[/blue] [color #ff0000]0[/color][blue]) AND (NOT[/blue] Recordset1.[color #990099]EOF[/color][blue]))[/blue]
%>
[color red yellow]<% intRecID =(Recordset1.Fields.Item("RecID").Value) ' Store the current RecordID in a variable %>[/color]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[color red yellow]<% =intRecID%>[/color]" 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>
[color red yellow]<input name="hidRecID" type="hidden" id="hidRecID" size="80" />[/color]
</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%>" [color red yellow]onchange="RecUpdate('<%= intRecID %>[/color]')">
<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.
[color #ff0000]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.[/color]
Thanks
Dean
Please Note: 1 is Bad, 10 is Good :-)
Part and Inventory Search
-
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.