Adding multiple records to SQL

[muddie1]

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

[WolfShade]

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,

^ _ ^

[muddie1]

Using MSSQL

[muddie1]

Thank you for all the guidance, issue resolved.

[WolfShade]

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,

^ _ ^

[muddie1]

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]

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,

^ _ ^

[muddie1]

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 

[John123 · Accepted solution]

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>

[muddie1]

thank you so much this works great.

So this poses another question.

I was just doing a test with the above codes, so what if I have multiple fields in the same record. How do I list them for entry?

like my fields no will be

<tr>

<td><input type="text" name="activity" id="activity"></td>
<td><input type="text" name="test" id="test"></td>
</tr>

[muddie1]

got it, thank you so much.

<cfloop from="1" to="#ListLen(form.activity)#" index="i">
<cfif i GT 1>,</cfif>
(
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#listGetAt(form.activity,i)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#listGetAt(form.test,i)#">
)
</cfloop>

[WolfShade]

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,

[muddie1]

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]

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,

^ _ ^

[iHEALSX]

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

thanks

[WolfShade]

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,

^  _ ^

[muddie1]

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