Database insert issues between parent and child table with multiple clients

Ignition
1

I have two different tables I am using in a Work Order System. I am Inserting into the parent table (EquipWorkOrder) and then using a select statement to get that ID using Top 1. Then I insert that ID with UserNm and Hrs into the child table(EquipWorkOrderHrs). The problem is when more than one client inserts at the same time. It will add the wrong users to the wrong work orders and It also skips EquipWorkOrderID numbers. I believe the issue is coming from using Top 1 but I am not sure on away to fix the issue.

        #insert in to EquipWorkOrder table
		query = "INSERT INTO EquipWorkOrder(DateTm,PlantLocID,StatusID,MaintLocationID,EquipmentID,EquipTypeID,CategoryID,CrewID,MeterReading,Description,Resolution,Comments) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"
		args = [event.source.parent.getComponent('DateTm').date,1,event.source.parent.getComponent('Status').selectedValue,event.source.parent.getComponent('MaintLocation').selectedValue,event.source.parent.getComponent('Equipment').selectedValue,event.source.parent.getComponent('EquipTypeID').intValue,event.source.parent.getComponent('Category').selectedValue,event.source.parent.getComponent('Crew').selectedValue,event.source.parent.getComponent('Meter Reading').floatValue,event.source.parent.getComponent('Description').text,event.source.parent.getComponent('Resolution').text,event.source.parent.getComponent('Comments').text]
		system.db.runPrepUpdate(query, args)
		
		#select to read last inserted ID within EquipWorkOrder Table
		query = "SELECT top 1 EquipWorkOrderID FROM EquipWorkOrder order by DateTm desc"
		results = system.db.runQuery(query)
		ID = results.getValueAt(0,0)
		
		#insert data in to EquipWorkOrderHrs for Tech 1
		query = "INSERT INTO EquipWorkOrderHrs(EquipWorkOrderID,UserID,Hrs) VALUES(?,?,?)"
		args = [ID, event.source.parent.getComponent('Tech 1').selectedValue,event.source.parent.getComponent('Tech 1 Hrs').floatValue]
		system.db.runPrepUpdate(query, args)
2

What database are you using?

with the runPrepUpdate you can use the ‘getKey=1’ to get the actual Id that has been added to the database from the update statement.

So potentially this would work:

        #insert in to EquipWorkOrder table
		query = "INSERT INTO EquipWorkOrder(DateTm,PlantLocID,StatusID,MaintLocationID,EquipmentID,EquipTypeID,CategoryID,CrewID,MeterReading,Description,Resolution,Comments) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"
		args = [event.source.parent.getComponent('DateTm').date,1,event.source.parent.getComponent('Status').selectedValue,event.source.parent.getComponent('MaintLocation').selectedValue,event.source.parent.getComponent('Equipment').selectedValue,event.source.parent.getComponent('EquipTypeID').intValue,event.source.parent.getComponent('Category').selectedValue,event.source.parent.getComponent('Crew').selectedValue,event.source.parent.getComponent('Meter Reading').floatValue,event.source.parent.getComponent('Description').text,event.source.parent.getComponent('Resolution').text,event.source.parent.getComponent('Comments').text]
		ID = system.db.runPrepUpdate(query, args,getKey=1) #Note this has been changed to include the getKey
		/*
		#select to read last inserted ID within EquipWorkOrder Table
		query = "SELECT top 1 EquipWorkOrderID FROM EquipWorkOrder order by DateTm desc"
		results = system.db.runQuery(query)
		ID = results.getValueAt(0,0)
		*/
		#insert data in to EquipWorkOrderHrs for Tech 1
		query = "INSERT INTO EquipWorkOrderHrs(EquipWorkOrderID,UserID,Hrs) VALUES(?,?,?)"
		args = [ID, event.source.parent.getComponent('Tech 1').selectedValue,event.source.parent.getComponent('Tech 1 Hrs').floatValue]
		system.db.runPrepUpdate(query, args)
3

I am using MSSql

4

OK then the code I updated should work.

getKey=1 will pull back the inserted ID and you can use that directly in your next insert statement.

5

Ok, I got it changed. Thanks for the help. I will watch it a few days to see if it has fixed it.