delete multiple rows in database

[svisveswaraiya]

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]] )

How could I resolve this? 

Thanks in advance,

Shalini V.

[zyuan1]

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

[svisveswaraiya]

Hi @zyuan1 ,

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

[zyuan1]

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.

[svisveswaraiya]

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.

[zyuan1]

Delete_Organization is your SQL command?

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

[svisveswaraiya]

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.

[zyuan1]

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

[svisveswaraiya]

Hi @zyuan1 ,

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

[zyuan1]

@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.