Primary key value when using Dialog to insert new record

[WindForce]

Hi
I am using a dialog component to insert and edit rows in a grid. Backend DB is Sql Server. In this specific table, i have a primary key set as identity, auto-increment.
When inserting a new record, is there a way to show the user what value has been generated by SQL ? I would like to just show an OrderID field with either the value of the OrderID we are editing or the new OrderID being generated in case of insert.

Thanks
Jaime

[WindForce]

Re: Primary key value when using Dialog to insert new record

bump!
(been bitten by this one in a different scenario) :-(

[Garry Flanigan]

Re: Primary key value when using Dialog to insert new record

You should be able to see the value of every field in an existing record. You cannot see the value of the primary key until the record is saved. When you save the record, if you stay in the dialog the primary key value will show after record is saved. Maybe I am misunderstanding your question?

[WindForce]

Re: Primary key value when using Dialog to insert new record

Thank you for your help.
Well, in my current scenario, in my dialog, i run a submit() then an action script to send the email with the newly created reservation.
I set the subject to "Thank you for your order, number {OrderID}" ( this is built using the genie when picking up a field ), and in the mail i do not see the newly generated number. I also do not see that number when staying in the dialog...Is there some type of refresh i have to manually run after submitting?

[Garry Flanigan]

Re: Primary key value when using Dialog to insert new record

What type of control is OrderID? Is it set as Updateable?

[WindForce]

Re: Primary key value when using Dialog to insert new record

OrderID is numeric, set as entity with auto-increment, and set as updateable. It is set as Primary key in the dialog . The only 'abnormal' issue here is that i set the control to disabled and invisible ( 1=2 in both show/hide and enable properties )
I just saw in another thread a post by DavidK where he mentions the javascript getKey('MYTABLEALIAS') function, which i intend to test prior to sending the email but after it's saved, i'll see if i can catch it this way..

[WindForce]

Re: Primary key value when using Dialog to insert new record

I am reposting this since something seems weird with this thread ( i see Garry's is the last post, but when i get in, i see mine...)

Jaime

OrderID is numeric, set as entity with auto-increment, and set as updateable. It is set as Primary key in the dialog . The only 'abnormal' issue here is that i set the control to disabled and invisible ( 1=2 in both show/hide and enable properties )
I just saw in another thread a post by DavidK where he mentions the javascript getKey('MYTABLEALIAS') function, which i intend to test prior to sending the email but after it's saved, i'll see if i can catch it this way..

[WindForce]

Re: Primary key value when using Dialog to insert new record

Well, i still can't get the generated new value in the OrderID primary key after submit. I put a debug(1) after submit and prior to the email send, tried to look at all the objects, and didn't find the value for orderID. The only place where i found it is deep inside some javascript object or string.
This is a very basic and common scenario, i do not understand how i got just a response from 1 guy in 3 months...

Thanks
Jaime

[eskimoavenue]

Re: Primary key value when using Dialog to insert new record

I am running into the same problem here and cant seem to find the solution.

1. In a dialog, I am using the primary key as orderid (auto increment sql table)

2. After the dialog has been submitted, I need to retrive the newly created ID in some way so I can use it in the confirmation email (also sent by the dialog).

Bassically I need to get the newly created orderid somewhere between the ExecuteServerSideAction("Save Data::") and ExecuteServerSideAction("Send E-mail Message::") scripts...

Robert

[Al Buchholz]

Re: Primary key value when using Dialog to insert new record

Usually there is an sql statement to get the last id that was entered.

I don't know what it is for a particular flavor of SQL, but the SQL vendor's help file should provide it.

So you do 2 calls to the database. One to insert the row, the next to get the id that was just assigned.

YMMV

[GGandhi]

Re: Primary key value when using Dialog to insert new record

hello

if you are using sql backend, dialog to capture the data and when you submit (if after validate has no errors and submission is successful) you should be able to get the primary key by using the connection.CallResult.LastInsertedIdentity. there is a whole article on xbasic with sql in the wiki. take a look.

thanks for reading

gandhi

version 10.5
mysql backend

[jkwrpc]

Re: Primary key value when using Dialog to insert new record

I believe you can query the last indentity field by querying '@@identity'. My recollection is this is the SQL system field that stores the last used identity number for a given table.

I have uses this many times, but I do have to query for it, so I have something like "Select @@identity from tblname" . I am not sure of the A5 query syntax... I am still working on trying to learn Xbasic and finding it much harder than it should be. (sigh)

[Jim Coltz]

Re: Primary key value when using Dialog to insert new record

The last inserted primary key value is made available in

e.rtc.lastPrimaryKeyValue

in the AfterDialogValidate server side event.

[eskimoavenue]

Re: Primary key value when using Dialog to insert new record

The last inserted primary key value is made available in

e.rtc.lastPrimaryKeyValue

in the AfterDialogValidate server side event.

thanks!!!!!!!

I could actually put this straight into the email HTML body of my confirmation email. Something like:

Thank you, your order ID is {e.rtc.lastPrimaryKeyValue}

Robert

[eskimoavenue]

Re: Primary key value when using Dialog to insert new record

The last inserted primary key value is made available in

e.rtc.lastPrimaryKeyValue

in the AfterDialogValidate server side event.

thanks!!!!!!!

I could actually put this straight into the email HTML body of my confirmation email. Something like:

Thank you, your order ID is {e.rtc.lastPrimaryKeyValue}

Robert