Submit Button Scripting

I have a submit button that is taking the information I am putting into the Pass/Fail table and dumping it into a SQL database. but when I go back to the form the first names are still in the FirstName Column even though, the other columns are empty as they should be. Can someone tell me what I am doing wrong? I am including a screen shot along with code from the submit button. I am taking information from the left table and clicking/dragging to the right table.

Blockquote

data = event.source.parent.getComponent('PassFailTable').data
columnList = list(data.getColumnNames())
pyData = system.dataset.toPyDataSet(data)
t_stamp = system.date.now()

Create column string for the query

columnString = '(t_stamp,' + ','.join(columnList) + ')'

Create question marks for the prepQuery insert, adding one to each set of values for the timestamp

questionMarkString = ','.join(['(' + ','.join('?' * (len(columnList)+1)) + ')'] * len(pyData))

query = "INSERT INTO Quality.dbo.Defecting_WasteBeltAudits" + columnString + ' VALUES ' + questionMarkString

print query

Make a flat list out of the pyData

values =
for row in pyData:
# Add tStamp and row data for each row
values += [t_stamp]+list(row)
print values

system.db.runPrepUpdate(query, values)

tags = ["[Client]Defecting/Shift_1", "[Client]Defecting/Shift_2"]
Shift1values =[0,0]
system.tag.writeAll(tags,Shift1values)

table = event.source.parent.getComponent('PassFailTable')
dataIn = table.data
headers = list(dataIn.getColumnNames())
positionList = dataIn.getColumnAsList(0)
padding = len(headers)-1
dataOut =
for row in positionList:
newRow = [row] + [''] * padding
dataOut.append(newRow)

table.data = system.dataset.toDataSet(headers, dataOut)

window = system.nav.openWindow('Departments')
system.nav.centerWindow(window)

Blockquote

It’s a little difficult to read your code. Use three backticks ``` to start a coding block, past in your code, then another three to end it.

ah… ok. Thank you…

data = event.source.parent.getComponent('PassFailTable').data
columnList = list(data.getColumnNames())
pyData = system.dataset.toPyDataSet(data)
t_stamp = system.date.now()

# Create column string for the query
columnString = '(t_stamp,' + ','.join(columnList) + ')'
# Create question marks for the prepQuery insert, adding one to each set of values for the timestamp
questionMarkString = ','.join(['(' + ','.join('?' * (len(columnList)+1)) + ')'] * len(pyData))

query = "INSERT INTO Quality.dbo.Defecting_WasteBeltAudits" + columnString + ' VALUES ' + questionMarkString

print query

# Make a flat list out of the pyData
values = []
for row in pyData:
	# Add tStamp and row data for each row
	values += [t_stamp]+list(row)
print values

system.db.runPrepUpdate(query, values)


table = event.source.parent.getComponent('PassFailTable')
dataIn = table.data
headers = list(dataIn.getColumnNames())
positionList = dataIn.getColumnAsList(0)
padding = len(headers)-1
dataOut = []
for row in positionList:
	newRow = [row] +  [''] * padding
	dataOut.append(newRow)

table.data = system.dataset.toDataSet(headers, dataOut)



window = system.nav.openWindow('Departments')
system.nav.centerWindow(window)

A few things:

  1. You have chosen to cast things to a list which are already a list. Not a big deal really, but doesn't help in performance.
  2. I believe you have misunderstood what getColumnAsList() does.
  3. I would suggest moving away from getColumnAsList() as it isn't always available.

In this code columnList = list(data.getColumnNames()) getColumnNames() already returns a list so there is no need to cast it to a list. columnList = data.getColumnNames() is better.

Same thing in values += [t_stamp] + list(row), however in this case you would be better off using the append and extend functions of the list.

values = []
for row in pyData:
    values.append([t_stamp].extend(row))

Of course you can also achieve the same thing with a list comprehension

values = [[t_stamp].extend(row) for row in pyData]

Finally, getColumnAsList(0) returns the row values of column 0 as a list. So, when you loop through it, you are purposefully adding the value of the first column to your new row.

If your intention is to clear the dataset then the best method would be to call clearDataset. So, your new code can be simplified to the following:

data = event.source.parent.getComponent('PassFailTable').data
columnList = data.getColumnNames()
pyData = system.dataset.toPyDataSet(data)
t_stamp = system.date.now()

#Create column string for the query
columnString = '(t_stamp,' + ','.join(columnList) + ')'

#Create question marks for the prepQuery insert, adding one to each set of values for the timestamp
questionMarkString = ','.join(['('+','.join('?' * (len(columnList)+1)) + ')'] * len(pyData))

query = 'INSERT INTO Qualitydbo.Defecting_WasteBeltAudits %s VALUES %s' % (columnString,questionMarkString)

print query
#make a flat list out of the pyData
values = [[t_stamp].extend(row) for row in pyData]

system.db.runPrepUpdate(query,values)

tags = ['[Client]Defecting/Shift_1','[Client]Defecting/Shift_2']
Shift1values = [0,0]
system.tag.writeAll(tags,Shift1values)

#Clear the PassFailTable dataset
event.source.parent.getComponent('PassFailTable').data = system.dataset.clearDataset(data)

window = sysetm.nav.openWindow('Departments')
system.nav.centerWindow(window)

When I try this code I get:

Traceback (most recent call last):

  File "<event:actionPerformed>", line 18, in <module>



java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO Qualitydbo.Defecting_WasteBeltAudits (t_stamp,FirstName,LastName,Shift,Status) VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?), [, , , , , , , , , , , , , , , , , ], , , false, false)


	caused by Exception: Error executing system.db.runPrepUpdate(INSERT INTO Qualitydbo.Defecting_WasteBeltAudits (t_stamp,FirstName,LastName,Shift,Status) VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?), [, , , , , , , , , , , , , , , , , ], , , false, false)
	caused by GatewayException: SQL error for "INSERT INTO Qualitydbo.Defecting_WasteBeltAudits (t_stamp,FirstName,LastName,Shift,Status) VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)": The value is not set for the parameter number 19.
	caused by SQLServerException: The value is not set for the parameter number 19.

Ignition v7.9.13 (b2019120915)
Java: Oracle Corporation 1.8.0_261

Ineteresting, as I didn’t touch that section of your code.

I tried using just this line of code at the end and it literally cleared everything out like there were no rows or anything.

#Clear the PassFailTable dataset
event.source.parent.getComponent('PassFailTable').data = system.dataset.clearDataset(data)

Yeah, that is what the function does.

What do you want to end up with?

Also, I think I found the error from my script potentially.

I missed a period.

This:

query = 'INSERT INTO Qualitydbo.Defecting_WasteBeltAudits %s VALUES %s' % (columnString,questionMarkString)

should be:

query = 'INSERT INTO Quality.dbo.Defecting_WasteBeltAudits %s VALUES %s' % (columnString,questionMarkString)

I was just wanting the information in the table to be gone, but the rows still be there. Just like it is when you first open it. Right now once you hit the submit button the information goes into the SQL db and the ignition goes back to the main screen but if I go back into this page without closing the program completely the first names are still there but not the other columns of information. But now if I close the program entirely and go back into it, the right table is empty as it should be.

So, you want to keep the rows.

#Clear the PassFailTable dataset
dataOut = [[''] * len(row) for row in pyData]
event.source.parent.getComponent('PassFailTable').data = system.dataset.toDataSet(columnList,dataOut)

That worked flawlessly!!!!

THANK YOU SOOO MUCH! I have been racking my brain for 3 days trying to get this to work!!!!!