Hello All. I am setting up a dynamic checklist and am running into some issues getting the MSSQL queries to pan out. The broad scope is to update initials from a text-box into a database that my checklist table reads its values from; then to store a snapshot of current data in an unseen logbook; then pull the checklist's currently selected row, add a row, and update that value into an unseen row-storage table (to drill to the next step). The intent is to display only one checklist at once, but give the ability to change a Reactor Selection drop-down and allow the table to update with the correct information for that reactor (current step, past initials). My entire script may be found here:
# Define variables
initial = event.source.parent.getComponent('Initials').text
id = event.source.parent.SlctRow
cID = event.source.parent.getComponent('WashNum').intValue
logtime = system.date.now()
rxr = event.source.parent.getComponent('Reactor Select').selectedLabel
prod = event.source.parent.getComponent('PrevProduct').selectedLabel
step = event.source.parent.getComponent('CurrentStep').text
temp = event.source.parent.getComponent('TT').value
press = event.source.parent.getComponent('PT').value
elapsedt = event.source.parent.getComponent('TimeElapsed').value
# Insert operator initials into checklist
query1 = "UPDATE rcp_CleaningSteps SET ConfBy = ? WHERE sID = ? AND reactor = ?"
arg1 = [initial, id, rxr]
system.db.runPrepUpdate(query1,arg1)
# Log data upon step completion
query2 = "INSERT INTO rcp_CleaningLog VALUES (?,?,?,?,?,?,?,?,?,?);"
arg2 = [cID, logtime, rxr, prod, id, step, initial, temp, press, elapsedt]
system.db.runPrepQuery(query2,arg2)
# pull the current row from the checklist and add one row.
table = event.source.parent.getComponent('Checklist')
CrntRow = table.selectedRow
value = CrntRow + 1
# Update row for current checklist
query3 = "UPDATE rcp_RowStorage SET StoredRow = ? WHERE Reactor = ?"
arg3 = [value, rxr]
system.db.runPrepQuery(query3,arg3)
Query1 works entirely as intended. Query2 also works as intended, functionally, but will always return a "The statement did not return a result set" error (it is simply an INSERT INTO, I'm not sure why it expects results). Query3 is new and has not been tested because the Query2 error has yet to be resolved. I have tried SET NOCOUNT ON but am unsure if this works with my flavor of SQL, as it does not fix the issue.
At present, these are all in one actionPerformed script event. My next step for troubleshooting is to split the queries apart into 3 separate events:
- Query1 executes on actionPerformed
- Query2 is rewritten as a named query, and executes on mouseClicked
- Query3 executes on mouseReleased
I am open to any and all advice. my only real issue is the "no results" error so if there is a simple fix that I'm unaware of, I would greatly a nudge in the right direction.