I have an existing long standing database previously migrated from DBISAM to EDB using the TEDBTable components. I'm now in the process of redeveloping using TEDBQuery components and modernising it’s design and seeing a large improvement in speed as I had hoped and also been able to optimise the SQL for WAN connections.

I have an invoice form which I use for recalling existing invoice records with an items query component for which the invoiceID parameter is set in the invoice.AfterOpen event.

My question how do I successfully create a new invoice record, save it and retrieve the invoice number which is currently the primary key so I can use for adding item records. When using tables I used to invoice.post and retrieve the invoiceID directly from the invoice table. I have looked through the forums and see the use of the Params and LastAutoInc or MAX(InvoiceID) methods but have been unsuccessful with both, my controls are data bound using the datasource and fieldname and was hoping to avoid having different new and existing invoice forms.

Many thanks

Anthony


If your controls are databound then you're using the query pretty much as a table. If that's right just pop a dbedit on the form, set Visible := False and link to the autoinc field. After .Post just read the field value.

If you're using SQL INSERT to populate the table then the only way you have of getting the value is to use LASTIDENTITY, or open the table and read it directly.

Roy Lambert

Roy Lambert wrote:
If you're using SQL INSERT to populate the table then the only way you have of getting the value is to use LASTIDENTITY, or open the table and read it directly.


============================

For a multi-user environment I run a query to get the last record entered by the current user.

SELECT MAX(id) FROM Debtors
WHERE userID = intUserID

........................................................
PascalNetwork.com
pascal - the language we love

Trinione


>For a multi-user environment I run a query to get the last record entered by the current user.
>
>SELECT MAX(id) FROM Debtors
>WHERE userID = intUserID

Essentially open the table and read it but using SQL

Roy

Thank you for your replies, I was using a parameter to use the same form to recall a record and when I was posting a new invoice it disappeared from the query result. Adding the InvoiceID edit to the form identified this and I used the LastAutoInc to update the parameter and reload the record.

The idea of checking for the last InvoiceID for the current user is a safer option in a multi user environment which was a concern on mine.

Many thanks for all the replies and assistance

Anthony,

<< My question how do I successfully create a new invoice record, save it and retrieve the invoice number which is currently the primary key so I can use for adding item records. When using tables I used to invoice.post and retrieve the invoiceID directly from the invoice table. I have looked through the forums and see the use of the Params and LastAutoInc or MAX(InvoiceID) methods but have been unsuccessful with both, my controls are data bound using the datasource and fieldname and was hoping to avoid having different new and existing invoice forms. >>

The only reliable way of doing what you want (with SQL) is to use a parameterized INSERT statement.  EDB automatically creates any INSERT parameters as IN/OUT parameters, and populates any parameters for generated columns with the generated values from the insert operation:

with MyQuery do
  begin
  SQL.Text:='INSERT INTO MyTable (MyIdentityColumn) VALUES (:MyParam)';
  Prepare;
  ExecSQL;
  ShowMessage('The inserted identity value is '+IntToStr(ParamByName('MyParam').AsString);
  end;

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

The only reliable way of doing what you want (with SQL) is to use a parameterized INSERT statement.  EDB automatically creates any INSERT parameters as IN/OUT parameters, and populates any parameters for generated columns with the generated values from the insert operation:

---

Thanks Tim, so that would mean using a form with non data-bound components for insert and retrieving the primary key as a return parameter? I'm currently using a form with databound components passing a parameter with invocieID for recall and invoicequery.insert for inserting.

Anthony

Anthony,

<< Thanks Tim, so that would mean using a form with non data-bound components for insert and retrieving the primary key as a return parameter? >>

If you want to use INSERT queries, then yes.

You can also use a TEDBQuery instance that returns a sensitive result set:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity

and just use Insert..Post, and then you can simply read the identity value directly from the Delphi TField using FieldByName, etc.   The same is also true for just using TEDBTable instance with Insert..Post.

Tim Young
Elevate Software
www.elevatesoft.com