Editable Table, cannot insert new row

Archived Topics
1

I am experimenting with the editable table goodie and I get an error when trying to insert a row. I am using MSSQL.

[quote]Gateway Error 301:
SQL error for “INSERT INTO Panels (PanelIndex) Values (NULL)”: Cannot insert explicit value for identity column in table ‘Panels’ when IDENTITY_INSERT is set to OFF.

For Query:INSERT INTO Panels (PanelIndex) VALUES (NULL)[/quote]
I was doing some reading and from what I understand this issue is from the differences between MySQL and MSSQL. Can you confirm or correct my statement? Either way I am not sure what would be the best solution. I have never tried to insert a blank row before. Usually I always have values.
Thanks,

2

Here is the action event code for the add row button.

[code]table = event.source.parent.TableName
pk = event.source.parent.PrimaryKey

This insert logic inserts Nulls for the primary keys, hoping

the database will assign them auto-incrementing values

nulls = “”
for key in pk.split(","):
nulls += “NULL,”

Remove trailing comma

nulls = nulls[:-1]
query = “INSERT INTO %s (%s) VALUES (%s)” % (table, pk, nulls)

fpmi.db.runUpdateQuery(query)
fpmi.db.refresh(event.source.parent.getComponent(“Table”),“data”)[/code]

The add row button code is trying to insert a row by inserting a null to the primary key. Since MSSQL does not like this I changed it to insert a null value to a different field. This seems to be working OK.

[code]table = event.source.parent.TableName

This insert logic inserts a record by assigning a Null to the ‘Line’ field.

MSSQL will then increment the identity field.

query = “INSERT INTO %s (%s) VALUES (%s)” % (table, “Line”, “NULL”)

fpmi.db.runUpdateQuery(query)
fpmi.db.refresh(event.source.parent.getComponent(“Table”),“data”)[/code]

3

Glad you figured out how to get it working. That is why that component is distributed as a goodie rather than built-in: it isn’t all that tolerant of different databases and may need further configuration on the designer’s part. Thanks for sharing your solution.