# Edit different rows in a table **URL:** \ **Category:** Ignition\ **Created:** [October 10, 2011, 5:32pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407 "2011-10-10T17:32:53Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 10, 2011, 5:32pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/1 "2011-10-10T17:32:53Z")
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. ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [October 10, 2011, 6:34pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/2 "2011-10-10T18:34:01Z")
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. ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 10, 2011, 6:54pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/3 "2011-10-10T18:54:57Z")
That helped thanks a bunch. ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:") ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 10, 2011, 7:05pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/4 "2011-10-10T19:05:57Z")
How might you make it so you could add a row/cell or delete a row/cell with buttons? ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [October 10, 2011, 7:27pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/5 "2011-10-10T19:27:01Z")
Take a look at our editable table example here: [files.inductiveautomation.com/ex … Table.vwin](http://files.inductiveautomation.com/extras/EditableTable.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. ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 10, 2011, 7:33pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/6 "2011-10-10T19:33:47Z")
How do I save that file to my computer so I can access it through the Designer? Nevermind I got it. ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 10, 2011, 7:55pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/7 "2011-10-10T19:55:45Z")
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 ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [October 10, 2011, 8:28pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/8 "2011-10-10T20:28:46Z")
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. ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 10, 2011, 8:34pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/9 "2011-10-10T20:34:11Z")
They are set to editable ---
**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\ **Post date:** [October 10, 2011, 9:58pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/10 "2011-10-10T21:58:40Z")
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. ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 11, 2011, 2:44pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/11 "2011-10-11T14:44:32Z")
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. ---
**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\ **Post date:** [October 11, 2011, 4:23pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/12 "2011-10-11T16:23:51Z")
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? ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 11, 2011, 4:28pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/13 "2011-10-11T16:28:32Z")
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). ---
**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\ **Post date:** [October 11, 2011, 5:03pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/14 "2011-10-11T17:03:10Z")
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? ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 11, 2011, 5:05pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/15 "2011-10-11T17:05:31Z")
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 ---
**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\ **Post date:** [October 11, 2011, 5:24pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/16 "2011-10-11T17:24:11Z")
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. ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 12, 2011, 4:07pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/17 "2011-10-12T16:07:23Z")
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. ---
**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\ **Post date:** [October 12, 2011, 4:48pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/18 "2011-10-12T16:48:19Z")
Can you add your delete query here? It should be something like:`DELETE FROM tablename WHERE IDcolumnname = 5`What is the primary key on the table? ---
**Author:** ![WEControl](https://avatars.discourse-cdn.com/v4/letter/w/f14d63/32.png) [@WEControl](https://forum.inductiveautomation.com/u/WEControl)\ **Post date:** [October 12, 2011, 5:11pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/19 "2011-10-12T17:11:53Z")
There is no primary key because for some reason I can’t make anything in my database the primary key it wont save. ---
**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\ **Post date:** [October 12, 2011, 5:38pm UTC](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407/20 "2011-10-12T17:38:36Z")
If you have no primary key, then I suggest that you drop the table and create a new one with a primary key. [Next page](https://forum.inductiveautomation.com/t/edit-different-rows-in-a-table/3407.md?page=2)