How to set a custom property to None/Null?

Ignition
1

Using 7.9.9.

I have a window setup with a table and filters above it to search through records.

One part of this is two calendars that provide a startDate and endDate parameter to a named query.

There is also a checkbox that is Enable Date Filters so if its checked I do want to use these startDate and endDate filters in my query, otherwise I do not.

One the two calendar components each has a custom property outValue of Date type which is what is fed to the NamedQuery populating the table.

The expression for outVaule currently is

if({Root Container.Group.Group.enableDateFilter.selected},
{Root Container.Group.Group.startDateFilter.date},
null)

and similar for the endDateFilter component. This gives me the following error -

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting character string to smalldatetime data type.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:894)
	at com.inductiveautomation.factorypmi.application.db.namedqueries.NamedQueryClientRpc.execute(NamedQueryClientRpc.java:19)
	at com.inductiveautomation.factorypmi.application.gateway.NQuery.execute(NQuery.java:58)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:657)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.access$500(QueryManager.java:630)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:306)
	... 1 common frames omitted
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayResponse$GatewayThrowable: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting character string to smalldatetime data type.

I have also tried it with None and Null instead of null as the second argument in my if statement and the same results. I suspect it's because the custom property is actually feeding <No Date> as the property value to the NQ that is what always appears in the designer as the value of the outValue when it's supposed to be nothing
image

I suspected this is why I am getting `Conversion failed when converting character string to smalldatetime data type. however I also tried this directly in script console like

params = {'userId':'_Show All Entries',
'assetType':'_Show All Entries',
'materialOptions':'_Show All Entries',
'presentation':'_Show All Entries',
'hotTopic':'_Show All Entries',
'project':'_Show All Entries',
'startDate':None,
'endDate':None}
d = system.db.runNamedQuery('Table/projectStatusTable_dropdownFilters', params)
print d

and got the same Conversion failed when converting character string to smalldatetime data type. error.

I've been scratching my head on this for about an hour and change now so I've come for fresh eyes. I've used the short circuiting before with no problems. I don't know why I am having an issue with this one. Any thoughts?

Full NQ:
Params:

image
image1227×208 2.93 KB 

SELECT 
DPD_Project,
presentation,
materialOptions,
Project_Status,
Hot_Topic,
assetType,
userId,
timeStamp,
idx
FROM DPD.project_status_table
WHERE
(:userId='_Show All Entries' OR :userId IS NULL OR userid=:userId)
AND
(:assetType='_Show All Entries' OR :assetType IS NULL OR assetType=:assetType)
AND
(:materialOption='_Show All Entries' OR :materialOption IS NULL OR materialOptions=:materialOption)
AND
(:presentation='_Show All Entries' OR :presentation IS NULL OR presentation=:presentation)
AND
(:hotTopic='_Show All Entries' OR :hotTopic IS NULL OR Hot_Topic=:hotTopic)
AND
(:project='_Show All Entries' OR :project IS NULL OR DPD_Project=:project)
AND
(:startDate IS NULL OR timeStamp >= :startDate)
AND
(:endDate IS NULL OR timeStamp <= :endDate)
ORDER BY timeStamp DESC
2

I have a similar setup in production. Instead of using an expression on the dates, I always pull from the date property on the picker. In my script, I default start and end dates to None and override them if the date pickers should be used. When I am not using the pickers, IE check box unchecked, they are disabled which greys them out to the user.

Why you still get that error when you hard code the None values in for the dates seems strange. If it continues to throw that error, I would consider upgrading to the latest v7.9 minor release if possible.

1 Like