Scripting help

I’m trying to insert date from pop up calendar using the following code on mouse click

queryValue2 =event.source.parent.getComponent('Popup Calendar').date,

system.db.runUpdateQuery("INSERT INTO wo_records (t_start) VALUES ([b]'%s'[/b])" % (queryValue2),getKey=1)

I believe I’m not using the correct modifier, any hints on what modifier I should use for data format?

the error code

[code]java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO wo_records (t_start) VALUES (‘Wed Oct 14 15:41:24 CDT 2015’), , , true)

caused by Exception: Error executing system.db.runUpdateQuery(INSERT INTO wo_records (t_start) VALUES ('Wed Oct 14 15:41:24 CDT 2015'), , , true)
caused by GatewayException: SQL error for "INSERT INTO wo_records (t_start) VALUES ('Wed Oct 14 15:41:24 CDT 2015')": Conversion failed when converting date and/or time from character string.
caused by SQLServerException: Conversion failed when converting date and/or time from character string.

[/code]

Thank you

How about this:

queryValue2 =event.source.parent.getComponent('Popup Calendar').date system.db.runPrepUpdate("INSERT INTO wo_records (t_start) VALUES (?)", [queryValue2],getKey=1) Best,

[quote=“nmudge”]How about this:

queryValue2 =event.source.parent.getComponent('Popup Calendar').date
system.db.runPrepUpdate("INSERT INTO wo_records (t_start) VALUES (?)", [queryValue2],getKey=1)
[/code]Best,[/quote]
 Changing the .date to .text  for the pop up calendar worked with what you suggested ,

Now I have 3 separate statement, that work independently , but Im not able to combine into one statement.
[code]queryValue1 = event.source.parent.getComponent('Numeric Text Field').intValue
queryValue2 =event.source.parent.getComponent('Popup Calendar').text
queryValue3 =event.source.parent.getComponent('Dropdown').selectedValue

system.db.runUpdateQuery("INSERT INTO wo_records (wo) VALUES ('%d')" % (queryValue1),getKey=1)
system.db.runUpdateQuery("INSERT INTO wo_records (get_address) VALUES ('%d')" % (queryValue3),getKey=1)
system.db.runPrepUpdate("INSERT INTO wo_records (t_start) VALUES (?)", [queryValue2],getKey=1)

when I combine the 2 INT it works fine

system.db.runUpdateQuery("INSERT INTO wo_records (wo,get_address) VALUES ('%d','%d')" % ((queryValue1),(queryValue3)),getKey=1)

and if add the time I get errorr (TypeError: int argument required)

system.db.runUpdateQuery("INSERT INTO wo_records (wo,t_start,get_address) VALUES ('%d','?','%d')" % ((queryValue1),(queryValue2),(queryValue3)),getKey=1)

any suggestions on this one?

Thanks!

Yes, don’t use the system.db.runUpdateQuery function. Instead use system.db.runPrepUpdate.

Example:

[code]
queryValue1 = event.source.parent.getComponent(‘Numeric Text Field’).intValue
queryValue2 =event.source.parent.getComponent(‘Popup Calendar’).text
queryValue3 =event.source.parent.getComponent(‘Dropdown’).selectedValue

system.db.runPrepUpdate(“INSERT INTO wo_records (wo,t_start,get_address) VALUES (?,?,?)”, [queryValue1,queryValue2,queryValue3],getKey=1)
[/code]Best,

I was going to suggest changing the ‘?’ to ‘%s’ for a string. The problem is that you were trying to use two different syntaxes. Either Nick’s or my suggestion should work for you.

system.db.runUpdateQuery("INSERT INTO wo_records (wo,t_start,get_address) VALUES ('%d','%s','%d')" % (queryValue1, queryValue2 , queryValue3), getKey=1)

Thank you both, Im and electrical engineer by degree and I’m still new to coding and ignition. both are helpful answers and I see where my errors are, Thank you!

Me too! Can’t spell ‘geek’ without a EE, I always say! :laughing:

1 Like

I haven’t heard of that one! I’m sure I’ll use it soon :laughing: