Community Tip - Your Friends List is a way to easily have access to the community members that you interact with the most! X
I have a ms sql table called "site". to keep the question simply, say the table contains one identity column called site_id (primary key), and another column called site_name. when a record is inserted, the site_id automatically increases. I need to get the returned new site_id and then do an insert using the site_id into a few other tables in a single action. I tried to create 3 services, one is sql command using insert statement. the second one is a sql query with return type as infotable and sql statement is "select @@IDENTITY as 'Identity'"; the third service is a localscript with return type as number looks like following:
var params = {
site_name: 'abc' /* STRING */
};
// result: NUMBER
me.InsertSiteTable(params);
// result: INFOTABLE dataShape: "ProfileIdentityData"
var result = me.SelectIdentity();
however, I got the result of 1, but the actual ID is 1012. I think the reason is in order to get the new site_id value, the insert and select should be in one session. but I am not sure how to construct it in Thingworx. can you help?
Thanks,
-LuWen
LuWen,
Thank you for the timely reply! I can go with the stored procedure approach to solve one part of my problem which is insert to other tables using the site_id. however, there is another problem which I am trying to resolve by getting the new site_id back in an insert statement. Here is my problem: I am creating a new form for the user to fill out as well as allow the user to edit the form, but I have no way to tell if it is a new insert or an update. so I have a very small popup which allows the user to create a title of the form, then click "Create". The create button ties to a create service event and then I want to display the full form using the new ID which is created. I am not sure how to do that. any suggestion on that?
Is it possible to run following sql query which returns what I am looking for, but I am getting an error:
In mssql service, run following sql query:
INSERT INTO MySite (Site_Name) VALUES ('ABC');
SELECT Site_ID, Site_Name FROM MySite WHERE Site_ID = SCOPE_IDENTITY();
I am getting: Unable to Invoke Service GetSiteID on MsSQLServices : The statement did not return a result set.
Hi LuWen,
You will have to run this as a stored procedure, and use an SQL Query to execute it.