Help with sql delete from selection

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:

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

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:

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

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

1 Like

you are the best

thanks

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

1 Like

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

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?

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

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?

system.date.toMillis()
1 Like

I have been removing things

query

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

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.