Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Jan 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL insert statement multiple tables/multiple records

    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.

  • #2
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Not exactly sure of ur question but here's summin.....
    Do some reading here.... onGetRows.

    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 for this method.

  • #3
    New to the CF scene
    Join Date
    Jan 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    current code

    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

  • #4
    New to the CF scene
    Join Date
    Jan 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #5
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts

    I could be wrong!!!!!

    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?

    I don't know if there is anyway around the Conn.execute?
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #6
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    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

  • #7
    New to the CF scene
    Join Date
    Jan 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Code:
    <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>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •