Skip to main content
M
muddie1
Known Participant
May 20, 2020
Answered

Adding multiple records to SQL

  • May 20, 2020
  • 6 replies
  • 2094 views

So I can create a CFForm and add 1 record to the database, working good.

I am trying to add multiple records, but getting all sorts of issue, seached the internet but coming up empty.

I found a javascript that lets me add another row to the form/table, works great in adding the form, however when I try to add the data I am either getting "Variable not defined" or itf I have 2 rows on the form then both datafields gets added to the same cell in the db twice.

<FORM>

<cfform action="add.cfm" method="post">
<table id="dataTable">
<th>
<td></td>
<td>Activity</td>
</th>
<tr>
<td><INPUT type="checkbox" name="chk"/></td>
<td><cfinput type="text" name="activity" id="activity"></td>
</tr>
</table>
<INPUT type="button" value="Add Row" onclick="addRow('dataTable')" />
<INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" />
<!--- submit button --->
<cfinput type="Reset" name="ResetForm" value="Clear Form">
<!--- submit button --->
<cfinput type="Submit" name="SubmitForm" value="Submit">
</cfform>

 

<Action>

<cfquery datasource="AAAAA" name="activityInsert">
INSERT INTO tbl_activity (activity)
VALUES
<cfloop from="1" to="2" index="i">
<cfif i GT 1>,</cfif>
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#activity[i]#">
</cfloop>
</cfquery>
<h3>Activity added</h3>

 

Thanks

    This topic has been closed for replies.
    Correct answer John123

    If you are getting the values back in the "Activity" valriable as  a simple list then the following will insert them into a table

     

    <cfloop list="#Activity#" index="act">
        <cfquery name="qInsert" datasource="AAAAA">
        INSERT INTO tbl_activity (Activity)
        VALUES ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#act#"> )
        </cfquery>
    </cfloop>

    6 replies

    WolfShade
    WolfShade
    Legend
    May 21, 2020

    I suspect that part of the issue is you are using CFFORM.  I advise against using ANY of the CFFORM elements.  CF is using an outdated JS library.  Use FORM without CFINPUT, just use input.

     

    As far as the JS goes, yeah, it can be tough.  What I did for a similar form (granted, I'm using jQuery) is to duplicate the last existing row, rename the inputs, and insert it into the DOM.  But I also give a unique ID to the section, and use that for deleting, as well.

     

    Odd, though.. if you are getting two forms, only one should submit.  Then, again, if everything has the same name, that might explain it.

     

    V/r,

     

    ^  _ ^

    M
    muddie1Author
    Known Participant
    May 21, 2020

    thanks. I update the form tags. my bad on the dumps, I had the cfdump in a loop, sorry.

    WolfShade
    WolfShade
    Legend
    May 21, 2020

    As I suspected.. you are naming all the inputs with the same name, so you are being presented with a comma-delimited list of the form values.  You need to rename the input when a row is added.  Start with "activity0", then when a row is added and the input created for it, rename it "activity1"; if another row is added, that input should be named "activity2", etc.

     

    As you can see from your CFDUMP, you have a field of fieldnames.  This can be used to determine how long the loop needs to be in order to insert all data.

     

    HTH,

     

    ^ _ ^

    A
    Anonymous
    May 23, 2020

    I was looking for this... thanks for sharing information guys

    thanks

    WolfShade
    WolfShade
    Legend
    May 21, 2020

    I just thought of something else that might help.  Add a row or two, submit the form and have the action page CFDUMP the form scope.  That way you can see what's actually being passed,

    M
    muddie1Author
    Known Participant
    May 21, 2020

    dump above.

    So even if i 3 rows I get 2 records, and each record contain the entries from all 3 rows, also I noticed that there are 2 forms so looks like the script is adding 1 extra form instead of 2 rows as instructed.

    need a new JS or someone to help with the above JS,, don't know much about JS.

    WolfShade
    WolfShade
    Legend
    May 21, 2020

    It's difficult for me to tell exactly everything that is going on, here.. but it looks to me that one of two things are happening.  Either a) the form elements are being created with the same name as the existing ones, or b) the form elements are being created without a name.  The name is what is passed to the action page, with the value.  So if there is more than one row, either everything is being passed as a list or array, or only the first row of values are being passed.

     

    One thing to help see what is going on.  Add a row, then inspect the elements using F12 DevTools to see what they are being named, compared to what initially exists on page load.

     

    HTH,

     

    ^ _ ^

    M
    muddie1Author
    Known Participant
    May 21, 2020

    I am fifuring that something is up with the JS I copied, guess this was purposely made for php.

    Here is a dump of my form if I use 3 rows for entry. So even though I have 3 rows I am only getting 2 records and also all 3 rows are in the record.

    Any idea where I can start with something like this? need to add new row upon need and then insert the data in the db.

    struct
    ACTIVITY a1,b2,c3
    FIELDNAMES ACTIVITY,SUBMITFORM
    SUBMITFORM Submit
    struct
    ACTIVITY a1,b2,c3
    FIELDNAMES ACTIVITY,SUBMITFORM
    SUBMITFORM Submit 

    J
    John123Correct answer
    Participating Frequently
    May 21, 2020

    If you are getting the values back in the "Activity" valriable as  a simple list then the following will insert them into a table

     

    <cfloop list="#Activity#" index="act">
        <cfquery name="qInsert" datasource="AAAAA">
        INSERT INTO tbl_activity (Activity)
        VALUES ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#act#"> )
        </cfquery>
    </cfloop>

    WolfShade
    WolfShade
    Legend
    May 21, 2020

    If your "Add Row" button adds another input field with the same name, CF is seeing that the same way it sees checkboxes with the same name - all values are combined into a list or array.  I don't see your JS code for adding a row, so don't know if that is the case.

     

    V/r,

     

    ^ _ ^

    M
    muddie1Author
    Known Participant
    May 21, 2020

    ahhh, here is the JS. I only modified this "if(rowCount <= 2)" in the deleteRow section since my form has a header. Thanks

    <JS>

    <SCRIPT language="javascript">
    function addRow(tableID) {

    var table = document.getElementById(tableID);

    var rowCount = table.rows.length;
    var row = table.insertRow(rowCount);

    var colCount = table.rows[0].cells.length;

    for(var i=0; i<colCount; i++) {

    var newcell = row.insertCell(i);

    newcell.innerHTML = table.rows[1].cells[i].innerHTML;
    //alert(newcell.childNodes);
    switch(newcell.childNodes[0].type) {
    case "text":
    newcell.childNodes[0].value = "";
    break;
    case "checkbox":
    newcell.childNodes[0].checked = false;
    break;
    case "select-one":
    newcell.childNodes[0].selectedIndex = 0;
    break;
    }
    }
    }

    function deleteRow(tableID) {
    try {
    var table = document.getElementById(tableID);
    var rowCount = table.rows.length;

    for(var i=0; i<rowCount; i++) {
    var row = table.rows[i];
    var chkbox = row.cells[0].childNodes[0];
    if(null != chkbox && true == chkbox.checked) {
    if(rowCount <= 2) {
    alert("Cannot delete all the rows.");
    break;
    }
    table.deleteRow(i);
    rowCount--;
    i--;
    }


    }
    }catch(e) {
    alert(e);
    }
    }

    </SCRIPT>

     

    WolfShade
    WolfShade
    Legend
    May 20, 2020

    It depends upon what flavor db you are using.  In Oracle, you use INSERT ALL (the "SELECT * FROM DUAL" is critical).  I don't know what MS-SQL or MySQL use.

     

    HTH,

     

    ^ _ ^

    M
    muddie1Author
    Known Participant
    May 20, 2020

    Using MSSQL

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices