• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Adding multiple records to SQL

New Here ,
May 20, 2020 May 20, 2020

Copy link to clipboard

Copied

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

Views

1.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Participant , May 21, 2020 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>

Votes

Translate

Translate
LEGEND ,
May 20, 2020 May 20, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 20, 2020 May 20, 2020

Copy link to clipboard

Copied

Using MSSQL

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

Thank you for all the guidance, issue resolved.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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>

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 22, 2020 May 22, 2020

Copy link to clipboard

Copied

LATEST

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

thanks

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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,

 

^  _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
May 21, 2020 May 21, 2020

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation