Insert does not return an ID, Immediate Deleting fails on Editable Grid

Applications Forms
,
#1

If I insert a new record using an Editable Grid, the data inserts correctly. The application does not get the new primary key ID assigned by MSSQL. So if I try to delete this record immediately after inserting, the delete cannot happen because an id of 0 is used in the delete script. If I close and re-open the application, the data is loaded from the database with the id’s so I am then able to delete the record. Is this a bug or something I am doing wrong? I have set the field to “Calculated by the database” and it is an auto incrementing field in MSSQL.
The Form is linked to a Search Form. I am not sure if this is part of the problem.

#2

When you set auto increment in the database you have to set scriptcase to ‘auto-increment (AUTO)’ . But if you do not set in the database you have to set scriptcase to ‘auto-increment (MANUAL)’ . If you enable the app’s debug mode you will see that scriptcase will do auto increment the field with a select with max function.

#3

I would advise to use the auto increment database option as the implementation of the Max function by scriptcase allows corruption due to timing issues if you have more users on the system. Haven’t checked the latest updates but I wouldn’t take the chance and go for the autoinc field.

#4

I don’t think my problem is related to which option I use because “Calculated by the database” inserts the data correctly. Scriptcase is not getting the new records ID after the insert. Because this is the PK, Scriptcase cannot do anything with the new record directly after insertion. I believe SC should. I suppose I could put something in the onAfterInsert event but I am not sure what to put in there.

#5

You can obtain the last key by a mysql statement in the onafterinsert: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

#6

My word that was painful. Surely this whole work around should not be necessary? I finally fixed it.

Thanks Albert, but that would be for MySQL and I am using MSSQL which unfortunately is a little different.

The solution I adopted is as follows, not that I find it acceptable to need to do this but here goes:

In the onAfterInsert Event you need to get the last ID and assign it to the PK field. For MSSQL you can do the following:

// Check for record
$sqlid = “SELECT max(id) id”
. " FROM <Table Name>"
. " WHERE <Field Name 1> = ‘" . {Field Name 1} . "’ AND <Field Name 2> = ".{Field Name 2};

sc_lookup(rs, $sqlid);

if (isset({rs[0][0]})) // Row found
{
{id} = {rs[0][0]};

}
else // No row found
{
{id} = 0;

}

  • Field Name 1 and Field Name 2 in the WHERE clause gets replaced with the relevant fields and is there to ensure that you get the right id for the right row. In other words, use your own WHERE clause to ensure you are returning the correct row ID.

  • {id} is my Primary Key field name and should be replaced with the field you are trying to get the value for.

I could not use the MSSQL equivalent of mysql_insert_id() which is SELECT @@IDENTITY because ScriptCase kept falling over. Maybe someone else can advise a better query to use but mine worked for me.

At the end of the day, I do not know if this is a bug but I feel it is.

I really appreciate the contributions, thank you.

#7

There are 3 methods do get the last id inserted in sql server see this article:
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/​

#8

Thanks Paulo. I did try those methods beforeI just tested using SELECT @@IDENTITY again and this time it worked. The error I got before must have been something else.

#9

You are welcome Sardius. I like to change experience and development tips, because we help and learn too.

#10

Absolutely