cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need help navigating or using the PTC Community? Contact the community team. X

delete multiple rows in database

svisveswaraiya
17-Peridot

delete multiple rows in database

Hi,

I wanted to delete multiple rows in sql database. I have given the input as infotable which has default datashape that I have binded the grid to. The grid data selected rows I am giving as input to the multi delete service infotable ip. I tried using the grid advanced but the code does not work.

The Things["Bobcat_DBConfig"].Delete_Organization(params) is the service which has sql query to delete the row (

WITH cte AS
(
SELECT ROW_NUMBER() OVER(ORDER BY org_id) AS Row
FROM Organization
)

DELETE FROM cte
WHERE Row = [[rowNum]] )

 

multidelete.pngHow could I resolve this? 

 

Thanks in advance,

Shalini V.

9 REPLIES 9

Are you deleting data from Datatable (js service) or External Database (sql command)?

 

The Thing in your screen shot is a Datatable, but you mentioned sql database a lot.

 

If you want to delete multi-row in Datatable, use the 

DeleteDataTableEntries / DeleteDataTableEntriesWithQuery service
 

Hi @zyuan1 ,

I wanted to multi delete the data in external database using multi select option in advanced grid. 

The advanced Grid widget will send out infotable result. Check what infotable you'll get, and then use this infotable as input to trigger the SQL commands.

 

SQL command usually receive 1 row of data each execution, so you may need a Javascript to iterate each row of infotable and trigger the SQL command, make it a loop.

Hi @zyuan1 ,

As you said I have added a loop, But still I am not able to delete multiple rows in database.

var size = Input.SNo.getRowCount()
for(var i = 0; i < size; i++)
{
var params = {
rowNum: Input.SNo
/* INFOTABLE*/ };

var result = Things["Bobcat_DBConfig"].Delete_Organization(params);
}

 

Could you please help me with what I am missing.connection.pngmultidelete1.png

 

Delete_Organization is your SQL command?

 

Skip the for loop first, does this script work when size =1 (run by single row)

Hi @zyuan1 ,

 

Delete_Organization is my sql delete command & works fine when i run it:

WITH cte AS
(
SELECT ROW_NUMBER() OVER(ORDER BY org_id) AS Row
FROM Organization
)

DELETE FROM cte
WHERE Row = [[rowNum]]

 

Even when I run by single row the script doesnt work.

Input for this SQL script is one row of infotable? or separate string parameters?  

Hi @zyuan1 ,

The input for this sql script is the the selected rows of the infotable of the grid data.

@svisveswaraiya  I would suggest you to create a case for it, so that TS engineers could get a closer look into the problem.  This is your customized scripts, around the edge of support scope, but since it's a simple function so hopefully we could provide some useful suggestions.

Top Tags