...

View Full Version : SQL insert statement multiple tables/multiple records



mwawa1
01-13-2005, 03:22 PM
I'm working on a project in which I'm going to collect data for more than 1 table and multiple records in some of the tables.

I've learned how to collect the multiple records so now my data is being posted like this: ID, Date, Misc, textbox1, textbox2, textbox3 and so on and so forth where textbox* needs to be inserted into a child-table and ID, Date, and Misc will be inserted into a main table. There could be any number of textbox fields.

I was thinking all of this data would be posted to a second page with an SQL statement to insert data into the main table and another sql statement nested in a loop to insert each textbox (along with IDs) into the child-table. I've read here that sql inside a loop isn't always the best idea, but I can't imagine another way. The data does need to be in separate tables in an access database.

jaywhy13
01-13-2005, 10:52 PM
Not exactly sure of ur question but here's summin.....
Do some reading here.... onGetRows (http://www.learnasp.com/advice/whygetrows.asp).

From what i've recently learned.... there's really nothing wrong with using sql statement.... its just that when you execute an sql statement you must bear in mind that thats a call to the server.... so if you make many callls to the server..... yeah, you get the idea? However, if your records are not that many there's no real need to worry... I was reading in the article I put the link above that, the statments can cache up to 50 records in one go so.....


In terms of navigation getRows is a method that copies all records to an array for easier navigation.

There is another method, the recordset method. Read this thead (http://www.codingforums.com/showthread.php?t=9455&highlight=rs.addnew) for this method.

mwawa1
01-17-2005, 07:34 PM
Here's what I'm using right now. My only concern is I am calling on the server a lot since I can't figure out how to write the multiple records as a batch rather than 1 at a time. I'll read through the suggested post to see what I can come up with.

RecordID = request.form("RecordID")

mySQL= "INSERT INTO Results ("

For Each x In Request.Form
response.write left((x),5) &"<BR>"
mySQL2 =""
if left((x),5) = "names" then
'if x = "names1" or x = "names2" or x = "names3" then
mySQL2="Insert into Childtable (name, NAMEID, RECORDID) VALUES ('" &request.form(x)&"','"&Mid(x,6)& "','" &RecordID &"')"
'mySQL3 = mySQL3 &mySQL2 &"; "
myConnection.Execute mySQL2
else
mySQL= mySQL & x & ","
end if

next
mySQL= left(mySQL, len(mySQL) - 1) & ") VALUES ("

For Each x In Request.Form
if left((x),5) = "names" then
'y=1
'if x = "names1" or x = "names2" or x = "names3" then
else
mySQL= mySQL & "'" & Replace(request.form(x),"'","''")& "',"
end if
next
mySQL = left(mySQL, len(mySQL) - 1) & ")"
'on error resume next


myConnection.Execute mySQL

myConnection.Close

Set myConnection = Nothing

mwawa1
01-19-2005, 03:35 PM
I was using something like this to capture the multiple sub-records but I've adapted it try to collect 2 variables per record multiple times

<SCRIPT>
//Script to add text field
var numfields = 1;
function addfield(){
numfields++
var placeholder = '<BR> Name: <input type="text" size="35" name="name'+numfields+'"><input type="text" SIZE=8 MAXLENGTH=8 name="date'+numfields+'">';
fieldplace.insertAdjacentHTML('BeforeEnd',placeholder);
return false;
};
</SCRIPT>

<div id="fieldplace">
Name: <input name="Name" type="text" size="35"><INPUT NAME="Date" SIZE=8 MAXLENGTH=8>
<EM> -- mm/dd/yy</EM></div>
<a href="#" onClick="return addfield();">Add another?</a>
</P>


Only catch now is how do I collect multiple variables since x will sometimes be a name and sometimes a date?

jaywhy13
02-04-2005, 12:19 PM
I could be soooo wrong, but from wot I now think of database operations.... I'm thinkin that the Insert's are the least taxing on the server? Is that so?

You guys out there..... am I right? :confused:

I don't know if there is anyway around the Conn.execute?

ghell
02-04-2005, 03:05 PM
im not very good with this but i think you can insert into multiple tables by just using insert into table1, table2

if, for example they both have a field called id they will be table1.id or table2.id but if only one table has it it should keep the same name.... i think

im just going on the for each loop for each field in a table, using comma separated tables, i dont know if its the same for instert or update sorry

edit: ps pelase wrap ur code in [code|[/code| for this forum, it makes it a lot easier to read

mwawa1
02-04-2005, 04:07 PM
Well, the loop posts to the same table over and over again so I'm not sure that would work, I;ll have to look it over againa nd experiment with it.



<SCRIPT>
//Script to add text field
var numfields = 1;
function addfield(){
numfields++
var placeholder = '<BR> Name: <input type="text" size="35" name="name'+numfields+'"><input type="text" SIZE=8 MAXLENGTH=8 name="date'+numfields+'">';
fieldplace.insertAdjacentHTML('BeforeEnd',placeholder);
return false;
};
</SCRIPT>

<div id="fieldplace">
Name: <input name="Name" type="text" size="35"><INPUT NAME="Date" SIZE=8 MAXLENGTH=8>
<EM> -- mm/dd/yy</EM></div>
<a href="#" onClick="return addfield();">Add another?</a>
</P>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum