Index Out Of Range due to Datatype mismatch?

Ignition version: 7.9.4
DB: Sql Server
I have a simple custom method in a root container script window.

Changing the date on a calendar component calls this custom method, which in turn queries a db. The db column is titled “Date” and is of datatype “datetime”.
I get the following error when I call this custom method:

which says that “index 1 is out of range”. The calendar object has a datatype “Date”, while the db uses “datetime”. I think it might be a datatype mismatch, but I’m not sure. Does anyone know how to correctly bind this calendar date to my sql query?


The various system.db.*query*() function calls deliver a PyDataset. Components use a non-python Dataset object. You must use the toDataSet() function to convert before assignment. In most cases, you can also use the .underlyingDataset property of a PyDataset to get the corresponding Dataset.

Thanks pturmel. I did add the “system.dataset.toDataset()”, but it seems the “index out of range” error was because of a syntax error in the query. You can see in the code below that the question marks used for the dynamic date entries don’t have any single or double quotes. The code below worked well for me. Thanks!

`#Load Query parameters
	startDate = self.getComponent('StartDateCalendar').date
	endDate = self.getComponent('EndDateCalendar').date
	quantity = self.getComponent('Quantity').intValue
	selectedParameter = self.parameter
	query = "SELECT Top(?) ID, Kink_Length FROM dbo.PartData WHERE Date between ? and ? AND Kink_Length > 0 Order by Date Desc"
	args = [quantity,startDate,endDate]

	pyResultSet = system.db.runPrepQuery(query,args,"IgnitionTables")
	resultSet = system.dataset.toDataSet(pyResultSet)
	self.getComponent('Table').data = resultSet`
1 Like