Highlighted

Adding multiple records to SQL

New Here ,
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

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>

Views

1.3K

Likes

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

Adding multiple records to SQL

New Here ,
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

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>

Views

1.3K

Likes

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
May 20, 2020 0
LEGEND ,
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,

 

^ _ ^

Likes

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
Reply
Loading...
May 20, 2020 0
New Here ,
May 20, 2020

Copy link to clipboard

Copied

Using MSSQL

Likes

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
Reply
Loading...
May 20, 2020 0
New Here ,
May 21, 2020

Copy link to clipboard

Copied

Thank you for all the guidance, issue resolved.

Likes

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
Reply
Loading...
May 21, 2020 0
LEGEND ,
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,

 

^ _ ^

Likes

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
Reply
Loading...
May 21, 2020 0
New Here ,
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>

 

Likes

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
Reply
Loading...
May 21, 2020 0
LEGEND ,
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,

 

^ _ ^

Likes

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
Reply
Loading...
May 21, 2020 0
New Here ,
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 

Likes

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
Reply
Loading...
May 21, 2020 0
Participant ,
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>

Likes

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
Reply
Loading...
May 21, 2020 0
New Here ,
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>

Likes

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
Reply
Loading...
May 21, 2020 0
New Here ,
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>

Likes

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
Reply
Loading...
May 21, 2020 0
LEGEND ,
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,

Likes

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
Reply
Loading...
May 21, 2020 0
New Here ,
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.

Likes

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
Reply
Loading...
May 21, 2020 0
LEGEND ,
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,

 

^ _ ^

Likes

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
Reply
Loading...
May 21, 2020 0
iHEALSX LATEST
New Here ,
May 22, 2020

Copy link to clipboard

Copied

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

thanks

Likes

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
Reply
Loading...
May 22, 2020 0