03-31-2024 08:58 AM - last edited on 04-01-2024 05:00 AM by JackLacava
I am trying to insert a row into a OneStream Application SQL Table through the SQL editor Dashboard component. I am getting the following error message.
Is this something that needs to be changed in the SQL or a component setting I need to change. At the moment I have not added anything additional to my SQL create table statement except the column names and a primary key for the Row Number.
Solved! Go to Solution.
03-31-2024 09:46 AM - edited 03-31-2024 09:52 AM
@jwagner This is a bug of sorts with versions prior to V8.2. when you created the table XFT_Division_Mapping you might have set the row number column to an Identity Column. If you are using the STE to insert the row numbers, then don't create the column as identity:
--With identity:
CREATE TABLE XFT_Division_Mapping (
RowNumber int IDENTITY(1,1) NOT NULL,
DivisionName varchar(255) NOT NULL,
PRIMARY KEY (RowNumber)
);
-Without Identity:
CREATE TABLE XFT_Division_Mapping (
RowNumber int (1,1) NOT NULL,
DivisionName varchar(255) NOT NULL,
PRIMARY KEY (RowNumber)
);
You could also try removing any reference to Row Number (or whatever column is set to IDENTITY) from the STE so that It's not trying to insert a value into the identity column.
04-01-2024 08:54 AM - edited 04-01-2024 08:56 AM
I get around this by handling all the table inserts and updates with code in a dashboard extender. I write a method to calculate the next row Id and add it to the record(s) being inserted. Then do the insert manually as well using sql. Finally update and return the SQLTableEditorTaskResult to reflect your work in the table.
03-31-2024 09:10 AM
It is a sql server option, this url might help: identity-insert
03-31-2024 09:46 AM - edited 03-31-2024 09:52 AM
@jwagner This is a bug of sorts with versions prior to V8.2. when you created the table XFT_Division_Mapping you might have set the row number column to an Identity Column. If you are using the STE to insert the row numbers, then don't create the column as identity:
--With identity:
CREATE TABLE XFT_Division_Mapping (
RowNumber int IDENTITY(1,1) NOT NULL,
DivisionName varchar(255) NOT NULL,
PRIMARY KEY (RowNumber)
);
-Without Identity:
CREATE TABLE XFT_Division_Mapping (
RowNumber int (1,1) NOT NULL,
DivisionName varchar(255) NOT NULL,
PRIMARY KEY (RowNumber)
);
You could also try removing any reference to Row Number (or whatever column is set to IDENTITY) from the STE so that It's not trying to insert a value into the identity column.
04-01-2024 08:47 AM
I would like to have the row increment on its own, you are correct I made the rownumber column an identity column. How would I go about adding a new row through the SQL Table Editor but the row number column is populated/auto incremented yet also visible to the user? Is this possible or is manual entry of the row number required?
04-01-2024 08:49 AM
For reference, when I add a new row I want the row number to be populated with 9304
04-01-2024 08:54 AM - edited 04-01-2024 08:56 AM
I get around this by handling all the table inserts and updates with code in a dashboard extender. I write a method to calculate the next row Id and add it to the record(s) being inserted. Then do the insert manually as well using sql. Finally update and return the SQLTableEditorTaskResult to reflect your work in the table.
04-01-2024 08:56 AM
Ok thank you Rob, I guess there isn't a way to accomplish this with component logic only.
04-01-2024 08:58 AM
Yeah that seems the reality. Maybe it’s better in 8.2, but I know that’s a big upgrade, you’d rather do it with the version you’re on.
start a new thread if you want help with the code. We can give you a hand.
04-01-2024 08:57 AM
Do you have any sample code I could use to get started with the dashboard extender??