# Help with sql delete from selection

**URL:** <https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219>\
**Category:** Ignition\
**Created:** [April 8, 2022, 1:03pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219 "2022-04-08T13:03:48Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [April 8, 2022, 1:03pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/1 "2022-04-08T13:03:48Z")

</div>

Usually these work right away, no issue.

But in this case it doesn’t work.

I am not sure how best to test it either.

Script on a button:

```auto
parameters = {

"Record" : self.parent.parent.getChild("flex").getChild("flex_0").props.selection.data[0].Record,
"Machine" : self.parent.parent.getChild("flex").getChild("flex_0").props.selection.data[0].Machine,
"Shift" : self.parent.parent.getChild("flex").getChild("flex_0").props.selection.data[0].Shift,
"Hours" : self.parent.parent.getChild("flex").getChild("flex_0").props.selection.data[0].Hours,
"Comment" : self.parent.parent.getChild("flex").getChild("flex_0").props.selection.data[0].Comment,
"Count" : self.parent.parent.getChild("flex").getChild("flex_0").props.selection.data[0]['Count']

}

system.db.runNamedQuery("DeleteRowMyTable",parameters)

```

then I have a query

```auto
Delete from mytable
where Record =:Record and Machine=:Machine and 
Shift=:Shift and Hours=:Hours and Comment=:Comment and Count=:Count
and Record > DATEADD(minute, -120,cast(GETDATE()as datetime ))

```

error:

```auto
.SQLServerException: The statement did not return a result set.

```

the selection, I used the names from the columns  
I copied a working code, and Counts was written that way. I don’t know why that worked in the other.  
I am going to add to the where clause: a check that record is within the last hour.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 8, 2022, 1:05pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/2 "2022-04-08T13:05:19Z")

</div>

I think you probably need to change it from `Query` to `Update Query` under the Authoring tab of the named query for the query type

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/d/9d00e41644f65a0917a621668449dfd38a4807de.png)

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [April 8, 2022, 1:06pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/3 "2022-04-08T13:06:01Z")

</div>

you are the best

thanks

Still doesn’t work, but I needed to do that for sure.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 8, 2022, 1:07pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/4 "2022-04-08T13:07:30Z")

</div>

Ok interesting. Is the error the same then? Or a different error?

---

<div class="post-metadata">

**Author:** ![ajdewhirst1](https://avatars.discourse-cdn.com/v4/letter/a/bbe5ce/32.png) [@ajdewhirst1](https://forum.inductiveautomation.com/u/ajdewhirst1)\
**Post date:** [April 8, 2022, 2:09pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/5 "2022-04-08T14:09:51Z")

</div>

Your query has an :Hours parameter, but I don’t see it in your parameter dictionary?  
Also, the items in your parameter dictionary need separating with commas?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 8, 2022, 2:18pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/6 "2022-04-08T14:18:01Z")

</div>

In addition, you feed a “tableName” parameter but tableName seems hardcoded in your named query when it should be {tableName} (assuming you are using tableName as a Query String).

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [April 8, 2022, 2:24pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/7 "2022-04-08T14:24:27Z")

</div>

Thanks

I edited the top post rather than repost it here.

It now has commas, which my script does. (company side, so it isn’t an exact copy)  
Removed the tableName parameter.  
One of the working scripts has tablename parameter, but doesn’t use it.

I did switch to updatequery type, and now no error shows up

So I think I need to test the way that the selection sees the Record, which supposed to be a timestamp, but in the table there is formatting so maybe that needs to be formatted.

Is there a way to format that easily back to t\_stamp format?

looks like datetime.datetime

this selection from the table on perspective page shows:

04/08/2022 09:00:07

the table I am trying to remove it from says the same

so those should match right?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [April 8, 2022, 5:28pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/8 "2022-04-08T17:28:53Z")

</div>

```auto
system.date.toMillis()

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [April 8, 2022, 5:49pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/9 "2022-04-08T17:49:05Z")

</div>

I have been removing things

query

```auto
Delete from myTable
where machine=:machinepar and comment=:commentpar
and Record > DATEADD(minute,-120,cast(getdate() as datetime))

```

* * *

fixed it

so I needed update query  
and I had misspelled Comment

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [April 11, 2022, 6:57pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/10 "2022-04-11T18:57:20Z")

</div>

I got the delete button to work with the datetime too

I set the datatype of the param to datetime  
in the sql query for where, I also said

`Where Record = convert(datetime,:RecordedParameter) and`  
This resets the formatting for the parameter after the formatting was changed in the table since the param is somehow both a datetime and yet still not formatted as one.  
Thought it was kind of interesting and wanted to note it to help myself or others later.

I think system.date.toMillis() would have worked on the script side. Then also in my query, I would have to get the millis.

Thanks very much for helping me.

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [June 5, 2023, 7:49pm UTC](https://forum.inductiveautomation.com/t/help-with-sql-delete-from-selection/58219/11 "2023-06-05T19:49:58Z")

</div>

I am not sure how I had got that working last April, but today I used:

```auto
DATEADD(ms, -DATEPART(ms, RecordedDate), RecordedDate)
= convert(datetime,:parRecordedDate)

```

I check the query, and the one from last year is working still.  
I don't understand how, can only think the inserts are trimming the milliseconds.  
My new query, I could not get working without trimming milliseconds in the where clause on both parameter and data side of the comparison.