While using SQL query to write to database how to write NULL

Ignition
1

Hello all,
I am using the following script to write to sql table the data from few dropdown boxes. But if the selected value is blank it won’t write it down as NULL. Any idea how to do that? Here is the code:

[code]name = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 8’).selectedStringValue
department = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 9’).selectedStringValue
factory = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown’).selectedStringValue
offices = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 1’).selectedStringValue
type = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 2’).selectedStringValue
ppe = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 3’).selectedStringValue
risks = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 4’).selectedStringValue
housekeeping = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 5’).selectedStringValue
tools = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 6’).selectedStringValue
equipment = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 7’).selectedStringValue
comments = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Text Area’).text
date = system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Popup Calendar’).date
date2=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Popup Calendar’).datetostring
a=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown’).selectedStringValue
b=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 1’).selectedStringValue
c=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 2’).selectedStringValue
d=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 3’).selectedStringValue
e=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 4’).selectedStringValue
f=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 5’).selectedStringValue
g=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 6’).selectedStringValue
h=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 7’).selectedStringValue
i=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 8’).selectedStringValue
j=system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 9’).selectedStringValue
k=event.source.parent.getComponent(‘Text Area’).text

if (a == “” and b == “” and c == “” and d == “” and e == “” and f == “” and g == “” and h == “” and i == “” and j == “” and k == “”):
system.gui.messageBox(“Fill in the form first!”)

else:
insertQuery = “”“INSERT INTO [%s] ([Correction Action],[Department],[Date],[General Risks],[Good Housekeeping],[Location Factory Focus],[Location Offices & others],[Name],[PPE],[Safe Equipment],[Tools and Equipment],[Type])
VALUES (’%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’,’%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’)”"" % (‘SUBO’, comments,department,date2,risks,housekeeping,factory,offices,name,ppe,equipment,tools,type)
system.db.runPrepUpdate(insertQuery)
system.gui.messageBox(“Your SUBO was added!”)

system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 1’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 2’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 3’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 4’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 5’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 6’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 7’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 8’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Dropdown 9’).selectedStringValue = None
system.gui.getParentWindow(event).getComponentForPath(‘Root Container.Text Area’).text = None[/code]

2

if name == "": name = None

However I would suggest doing it like this:

[code]window = system.gui.getParentWindow(event)
name = window.getComponentForPath(‘Root Container.Dropdown 8’).selectedStringValue
department = window.getComponentForPath(‘Root Container.Dropdown 9’).selectedStringValue
factory = window.getComponentForPath(‘Root Container.Dropdown’).selectedStringValue
offices = window.getComponentForPath(‘Root Container.Dropdown 1’).selectedStringValue
type = window.getComponentForPath(‘Root Container.Dropdown 2’).selectedStringValue
ppe = window.getComponentForPath(‘Root Container.Dropdown 3’).selectedStringValue
risks = window.getComponentForPath(‘Root Container.Dropdown 4’).selectedStringValue
housekeeping = window.getComponentForPath(‘Root Container.Dropdown 5’).selectedStringValue
tools = window.getComponentForPath(‘Root Container.Dropdown 6’).selectedStringValue
equipment = window.getComponentForPath(‘Root Container.Dropdown 7’).selectedStringValue
comments = window.getComponentForPath(‘Root Container.Text Area’).text
date = window.getComponentForPath(‘Root Container.Popup Calendar’).date
date2= window.getComponentForPath(‘Root Container.Popup Calendar’).datetostring

values = [name, department, factory, offices, type, ppe, risks, housekeeping, tools, equipment, comments, date, date2]

Replace empty strings with None

values = [x if x !="" else None for x in values]

If all values are None then the form is blank

if values.count(None) == len(values):
system.gui.messageBox(“Fill in the form first!”)

else:
insertQuery = “”“INSERT INTO [%s] ([Correction Action],[Department],[Date],[General Risks],[Good Housekeeping],[Location Factory Focus],[Location Offices & others],[Name],[PPE],[Safe Equipment],[Tools and Equipment],[Type])
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”"" % ‘SUBO’
system.db.runPrepUpdate(insertQuery, values)
system.gui.messageBox(“Your SUBO was added!”)
[/code]