Edit different rows in a table

I Have a table with 4 columns ID Name Address and Phone. I want to be able to edit each cell and have it change in the database. So far I have it coded to change the Name side and it works. I have some experience with programming but not a lot. The script I have is

print event.row, event.column, event.newValue
data = event.source.data
id = data.getValueAt(event.source.selectedRow, 0)
name = event.newValue
system.db.runPrepStmt(“UPDATE Customers SET Name = ? WHERE ID = ?”, [name, id])

How can I make it so I can edit the address and phone side and have it update in the database? I have tried a couple ways but it ends up changing the name in the row as well.

Try something like this:idCol = "id" cols = ["id", "col1", "col2"] row = event.row col = event.column column = cols[col] value = event.newValue data = event.source.data id = data.getValueAt(row, idCol) system.db.runPrepUpdate("UPDATE table SET %s = ? WHERE id = ?" % column, [value, id])Here we set the id column so we know which row to update. We also set the columns that could be editable (line 2 cols = …). Hope this helps. If you have any questions on it let me know.

That helped thanks a bunch. :prayer:

How might you make it so you could add a row/cell or delete a row/cell with buttons?

Take a look at our editable table example here:

files.inductiveautomation.com/ex … Table.vwin

It is a window backup. So you can import it in the designer through the right click menu on All Windows in the Project Browser. It shows how to add/delete rows of information.

How do I save that file to my computer so I can access it through the Designer?

Nevermind I got it.

This is all quite confusing. I figured the OrderBy to tag with DB Browse so my table showed up but I don’t really know what to do with TableName. In the database the name of the table is Customers so I typed that in in TableName and the red goes away. But when I run it and click the add row it comes up with this error

Traceback (innermost last):

File “event:actionPerformed”, line 16, in ?

java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO Customers () VALUES (NULL), , , false)

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Gateway Error 301: SQL error for “INSERT INTO Customers () VALUES (NULL)”: Parameters supplied for object ‘Customers’ which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Parameters supplied for object ‘Customers’ which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

java.lang.Exception: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO Customers () VALUES (NULL), , , false)

Ignition v7.2.8 (b178)
Java: Sun Microsystems Inc. 1.6.0_26

You need to set the columns to be editable in the table customizer. It doesn’t know which columns to include in the insert query.

They are set to editable

Oh ok, you need to set the PrimaryKey property on the EditableTable container to the table’s primary key. Typically this is just the id column. If that is empty we don’t know how to insert. Your database needs to have default values specified for every other column.

Well I got the add row to work but the delete row only deletes the already made rows but not the empty rows so now I have a bunch of empty rows that I cant seem to get rid of. Also it adds the new row at the top and not on the bottom and when i put data into a row it doesn’t stick now for some reason. If I got to the Microsoft SQL server manager and try and delete or edit the blank rows there it gives me an error and doesn’t delete them or let me edit them.

If you are getting an error trying to delete the row from the database management software, then you won’t be able to delete from Ignition. Most likely your error is because of a foreign key constraint. Can you post the error here?

No rows were deleted.

A problem occurred attempting to delete row 3.
Error Source: Microsoft.SqlServer.Management.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(10 rows).

Correct the errors and attempt to delete the row again or press ESC to cancel the change(s).

It looks like you have the same key for each of those rows somehow. See if you can set the primary key for each row (it is most likely an auto incrementing integer), then delete them. Is the ID column blank?

No there are 2 rows and the ID column is 1 and 2 then like 5 other blank ones. I can’t make them keys because they only show up in the edit top 200 rows. If I go to design I only have my 4 main columns. ID Name Address and Phone

Exactly! On the 5 blank rows, set the ID column values to 3, 4, 5, 6, and 7. That should get rid of your key issue and you will be able to delete the rows.

I tried that and it just deleted the number not the row. Also ID isn’t set up as a primary key on this table because it wont let me save it. On my other table I made that has the ID as the key I can’t add rows because the ID key doesn’t allow nulls.

Can you add your delete query here? It should be something like:DELETE FROM tablename WHERE IDcolumnname = 5What is the primary key on the table?

There is no primary key because for some reason I can’t make anything in my database the primary key it wont save.

If you have no primary key, then I suggest that you drop the table and create a new one with a primary key.