Inserting value from calendar component into a SQL table

I'm trying to use a calendar's Date value in a script that runs a named query to update a SQL table, but for some reason I keep getting an error "Cannot coerce value DateTimeIn" into type: class java.util.Data. Invalid date format."

I'm currently using the MM/dd/yyyyy hh:mm aa format, but is it just not possible to grab the date value and use it in this way?

Are you using a named query or a prepUpdate query? I recommend those first of all so you don't have to worry about the format of the date at all - as long as you are grabbing a Date (technically java.util.Date or subclass thereof) property off of the component and inserting it with a named query / prepped query into a table with a datetime typed column Ignition handles the rest.

You definitely can use it this way. Can you show your script of what is being run here?

Edit: Sorry, I see you are using a named query. Show that - how are your parameters defined in the named query, and the script that calls the named query eventually to run it.

Yeah, I'm using a named query. Here's my script

data = event.source.parent.getComponent('Table').data
RecordID = system.tag.read("[client]GuardData/RecordID.Value")

if event.source.parent.getComponent('AppointmentTimeChk').selected == False:
	AppointmentTime = data.getValueAt(0,9)
else:
	AppointmentTime = event.source.parent.getComponent('AppointmentTimeCal').date
if event.source.parent.getComponent('DateTimeInChk').selected == False:
	DateTimeIn = data.getValueAt(0,5)
else: 
	DateTimeIn = event.source.parent.getComponent('DateTimeInCal').date

if event.source.parent.getComponent('DateTimeOutChk').selected == False:
	DateTimeOut = data.getValueAt(0,6)
else:
	DateTimeOut = event.source.parent.getComponent('DateTimeOutCal').date

#system.gui.messageBox(str(DateTimeIn), "Title")
CompanyName = event.source.parent.getComponent('CompanyNameTxt').text
TruckNumber = event.source.parent.getComponent('TruckNumberTxt').text
OrderNumber = event.source.parent.getComponent('OrderNumTxt').text
NumUnits = event.source.parent.getComponent('NumUnitsTxt').intValue
NumUnitsShipped = event.source.parent.getComponent('NumUnitsShippedTxt').intValue

parameters = {"RecordID":"RecordID", "CompanyName":"CompanyName", "TruckNumber":"TruckNumber","OrderNumber":"OrderNumber","NumUnits":"NumUnits","NumUnitsShipped":"NumUnitsShipped",
"AppointmentTime":"AppointmentTime","DateTimeIn":"DateTimeIn","DateTimeOut":"DateTimeOut"}
system.db.runNamedQuery("Guard Data/UpdateProductTruckData", parameters)

system.tag.write("[client]GuardData/RecordID.Value",0)
window = system.nav.openWindow('Guard Shack/GuardHome')
system.nav.centerWindow(window)

Ok, now please show how your named query is defined. Also what you show is a vision script so I would mark your topic vision, not perspective.

EDIT: Your parameters are wrong. It should be like parameters = {"AppointmentTime":AppointmentTime,... to reference the variable. Otherwise you are referencing the string literal "AppointmentTime" which is not a valid date.

2 Likes

Your edit was the fix, thanks!

I knew that the named query worked, so I didn't think to include it, sorry.

1 Like