UPDATE query updating whole table instead of single row

Ignition
,
1

After a confirmation, I am trying to update a single row in my database table using the following code. However, it ends up updating the entire table instead of just the intended row.

Could someone please help me figure out what might be wrong?
Note: The params i checked are have values.

def onMessageReceived(self, payload):
	# implement your handler here
	if payload["confirmed"]:		
		rowData = self.view.params.rowData
		prodid = rowData.get("PRODID", "")
		dataareaid = rowData.get("DATAAREAID", "")	
		update_query = """
			UPDATE dbo.ProdTable
			SET PROCESSED = 2
			WHERE PRODID = ?
			AND DATAAREAID = ?
		"""
		params = [prodid, dataareaid]
		system.db.runPrepUpdate(update_query, params , "MSSQLExp_LOCAL")
	
		# Compose message based on action
		msg = "Record Updated Successfully "
		iconPath = "material/"

		# Open popup
		system.perspective.openPopup(
			"Info",
			view="templates/PopupInfo",
			params={
				"message": msg,
				"icon": iconPath,
				"callerID": ""
			},
			title="Info",
			modal=True,
			draggable=False,
			resizable=False,
			showCloseIcon=True
		)
	else:
		# Do nothing, stay at previous state
		pass

Thanks in advance!

2

The query looks fine to me. Are you sure it's this query that is doing the damage?

1 Like
3

runPrepUpdate returns the number of rows affected by the query, print the return value and see what it shows. Also, your message should be qualified that the query affects at least one row.

Finally, it's better to negate your ifs to cut down on possible indention levels and else clauses.

# return immediately if action is not confirmed
if not payload["confirmed"]:
	return
...
rowsUpdated = system.db.runPrepUpdate(update_query, params , "MSSQLExp_LOCAL")
system.perspective.print('{rows updated: {}'.format(rowsUpdated))

# Compose message based on action
msg = "Record Updated Successfully "
iconPath = "material/"
if not rowsUpdated:
	msg = 'No Rows Affected By Update'
...
2 Likes
4

Hi everyone,

I finally found the bug and wanted to share the solution in case it helps someone else.

Originally, I had created a message handler inside a row-embedded view. When I triggered the handler, it ended up running once for each row, updating all of them unintentionally.

The fix was to pass the unique ID as a parameter when calling the popup, and then move the message handler to the parent table view instead of keeping it in the embedded row view.

Now the handler runs correctly and updates only the intended row. Everything is working as expected!

Thanks for your support.

2 Likes