How to populate Component Input Fields From a Tables Row Selection Based on ID?
I am working on the insert statement to a database by a button click but I am not sure how to accomplish my original question. It will be a update statement to a database by selected row.
I have my insert statement working, I am now working on the row selection update statement. This is what I have so far… not much
Maybe some help for you…
table = event.source.parent.getComponent(“Table”).data
selRow = table.getSelectedRow()
selCol = table.getSelectedColumn()
data = data.getValueAt(selRow, selCol)
https://docs.inductiveautomation.com/display/DOC79/Read+a+Cell+from+a+Table
Once I get the row selection right. I need to populate my entry components. Then when I update the desired components values I am updating from a button if that helps. Here is a pic
What I usually do is create custom properties and bind using an expression like this:
try({Root Container.Table.data}[{Root Container.Table.selectedRow}, “Status”], -1)
“Status” is the column and “-1” is the failover value (value it will be if no value returned)
I get a error when I try this: Scan Error. illegal character">(Line 1, Char 69)
It's the quotes, something in the copy/paste. Delete the quotes and retype, with single or double.
Example, “Status” vs "Status" or 'Status'
Edit, looks like the forum "cleaned" up the example. If you copied into notepad you will see the difference in the quotes. Nonetheless, it's the quotes.
Yeah that worked to get the ID Column
If you don’t use formatting marks when pasting code or other plain text into the forum, the forum treats it as paragraphs of prose, and pretties it up. Among other things, it substitutes paired curly quotes for straight quote characters, for that professional “article” appearance.
Lesson: don’t copy code from this forum that isn’t in a code format block.
So in order to populate each component entry I assume I do the same thing for each entry location.
Yes, obviously referencing the specific column for each
I got each entry bound to a custom property and it populates each one by the selected row, I am working on my update statement now. When I run this I get a error for my where clause. I have the select statement similar to my insert statement. I am I going about it wrong?
query = "Update Equipment Set where EquipmentID = 'event.source.parent.getComponent('Table').EquipmentID'(DateTm,Equip,AccountCode,Descr,TypeID,DepartmentID,MaintLocationID,PlantLocID,EquipTypeID,DelFlg,Gauss,Refractory) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"
args = [event.source.parent.getComponent('DateTm').date,event.source.parent.getComponent('Equip').text,event.source.parent.getComponent('Account Code').text,event.source.parent.getComponent('Descr').text,event.source.parent.getComponent('TypeID').selectedValue,event.source.parent.getComponent('DepartmentID').selectedValue,event.source.parent.getComponent('MaintLocationID').selectedValue,event.source.parent.getComponent('PlantLocID').selectedValue,event.source.parent.getComponent('EquipTypeID').selectedValue,event.source.parent.getComponent('DelFlg').selected,event.source.parent.getComponent('Gauss').selected,event.source.parent.getComponent('Refractory').selected ]
system.db.runPrepUpdate(query, args)
Perhaps this was a copy paste error, but I am fairly certain that the query as you have it will not run in your data base.
You need to replace the reference to the equipment ID with a question mark and also include that in your args list.
I don’t know what database you are using, but if your column name is ‘Equipment Set’ with a space then you should use square brackets, quotes, or some other delineation to represent to the Engine that it is a column name and not intended to be a Keyword. This is why it is generally recommended not to use spaces, key words, or Function names in/as column names.
Something like this:
query = "Update [Equipment Set] where EquipmentID = ? (DateTm,Equip,AccountCode,Descr,TypeID,DepartmentID,MaintLocationID,PlantLocID,EquipTypeID,DelFlg,Gauss,Refractory) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"
args = [event.source.parent.getComponent('Table').EquipmentID,event.source.parent.getComponent('DateTm').date,event.source.parent.getComponent('Equip').text,event.source.parent.getComponent('Account Code').text,event.source.parent.getComponent('Descr').text,event.source.parent.getComponent('TypeID').selectedValue,event.source.parent.getComponent('DepartmentID').selectedValue,event.source.parent.getComponent('MaintLocationID').selectedValue,event.source.parent.getComponent('PlantLocID').selectedValue,event.source.parent.getComponent('EquipTypeID').selectedValue,event.source.parent.getComponent('DelFlg').selected,event.source.parent.getComponent('Gauss').selected,event.source.parent.getComponent('Refractory').selected ]
system.db.runPrepUpdate(query, args)
The Table name is Equipment not Equipment Set. I am using Sql Server 2012
Set is part of the Update statement I am trying to accomplish
I modified my update query to this. When I press the button everything seems to work, I don’t get any errors but it doesn’t actually update the selected row in the database table.
query = "UPDATE Equipment SET DateTm = ?, Equip = ?, AccountCode = ?, Descr = ?, TypeID = ?, DepartmentID = ?, MaintLocationID = ?, PlantLocID = ?, EquipTypeID = ?, DelFlg = ?, Gauss = ?, Refractory = ? WHERE EquipmentID = ?"
args = [event.source.parent.getComponent('Table').EquipmentID,event.source.parent.getComponent('DateTm').date,event.source.parent.getComponent('Equip').text,event.source.parent.getComponent('Account Code').text,event.source.parent.getComponent('Descr').text,event.source.parent.getComponent('TypeID').selectedValue,event.source.parent.getComponent('DepartmentID').selectedValue,event.source.parent.getComponent('MaintLocationID').selectedValue,event.source.parent.getComponent('PlantLocID').selectedValue,event.source.parent.getComponent('EquipTypeID').selectedValue,event.source.parent.getComponent('DelFlg').selected,event.source.parent.getComponent('Gauss').selected,event.source.parent.getComponent('Refractory').selected ]
system.db.runPrepUpdate(query, args)
You need to put the EquipmentID at the end of list, because now it is the last parameter not the first. Most likely it didn’t find an EquipmentID so no rows were modified.
Also, note that you can verify if any rows were modified by taking the return value of the runPrepUpdate funciton. It returns an integer equal to the number of rows modified by the query.
I think this is the code you’re looking for:
query = "Update Equipment SET DateTm = ?, AccountCode= ?, Descr = ?, TypeID =?, DepartmentID =?, MaintLocationID = ?, PlatnLocID=?, EquipTypeID=?,DelFlg=?,Gauss=?, Refractory=? Where EquipmentID = ?"
args = [event.source.parent.getComponent('DateTm').date,event.source.parent.getComponent('Equip').text,event.source.parent.getComponent('Account Code').text,event.source.parent.getComponent('Descr').text,event.source.parent.getComponent('TypeID').selectedValue,event.source.parent.getComponent('DepartmentID').selectedValue,event.source.parent.getComponent('MaintLocationID').selectedValue,event.source.parent.getComponent('PlantLocID').selectedValue,event.source.parent.getComponent('EquipTypeID').selectedValue,event.source.parent.getComponent('DelFlg').selected,event.source.parent.getComponent('Gauss').selected,event.source.parent.getComponent('Refractory').selected,event.source.parent.getComponent('Table').EquipmentID ]
res = system.db.runPrepUpdate(query, args)
print 'Updated ' + str(res) + ' rows'I tried your code but it acts the same as mine above. It acts like it is ok, No errors but doesn’t actually update the database table.
Did it return a value other than zero?