So, in my Invoice-table I have a column (Invoicenumber, int) which is the sequential invoice number:

PK InvoiceNumber 8382 20190001 8383 20190002 8384 20190003

When I create a new InvoiceEntity and save it, I would like the InvoiceNumber to be set to SELECT (MAX(InvoiceNumber)+1)

According to the documentation (or - from what I can gather...) regarding "Expressions in entity inserts" and "Expressions in entity updates": https://www.llblgen.com/documentation/5.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_expressionsaggregates.htm#expressions-in-entity-inserts

• I believe that I have to do something similar to the given Update-example, being in my example

var invoice = new InvoiceEntity();

// method 1 invoice.Fields[(int) InvoiceFieldIndex.InvoiceNumber].ExpressionToApply = InvoiceFields.InvoiceNumber.SetAggregateFunction(AggregateFunction.Max) + 1;

// method 2 invoice.Fields[(int) InvoiceFieldIndex.InvoiceNumber].ExpressionToApply = new Expression(InvoiceFields.InvoiceNumber.SetAggregateFunction(AggregateFunction.Max), ExOp.Add, 1);

using (var adapter = new DataAccessAdapter(connectionString)) { adapter.SaveEntity(invoice, true); }

none of these methods work, but produce SQL like this (column names removed for readbility

INSERT INTO tblinvoices (c1, c2, c3, c4, c5, invoicenumber, c7, c8, c9, c10, c11, c12, c13, c14) VALUES (@p1, @p2, @p3, @p4, @p5, (tblinvoices.invoicenumber + @p , @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16) Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p2 : String. Length: 65535. Precision: 0. Scale: 0. Direction: Input. Value: "". Parameter: @p3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 5. Parameter: @p4 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2020-02-27T00:00:00.0000000+01:00. Parameter: @p5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p8 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p9 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p10 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: @p11 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2020-02-13T00:00:00.0000000+01:00. Parameter: @p12 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p13 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: @p14 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: <undefined value>. Parameter: @p15 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: @p16 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.

so, MAX() is never used anywhere, and the resulting value in the field is "1".

These threads seem to deal with the same problem: https://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12183&HighLight=1 https://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14514&HighLight=1 - both deal with the problem as I have tried, as far as I can see

For now, I'll just use this: using (var adapter = new DataAccessAdapter(connectionString)) { var maxValue = (int)adapter.GetScalar(InvoiceFields.InvoiceNumber, AggregateFunction.Max);

            invoice.InvoiceNumber = maxValue + 1;

            adapter.SaveEntity(invoice, true);
        }

Given the load on the system it will probably never go wrong, but never say never... I would still like to utilize MAX() inside the INSERT.

Could you please provide an actual working example of how to use Max() in an INSERT?

Thanks in advance.

/Jan

MySQL version 5.6.43 LLBLGen version 5.6 (5.6.1) RTM DevArt version (latest express from their website - 8.16 apparently)

You need to use ScalarQueryExpression… as follows:

var product = new ProductEntity();
product.CategoryId = 1;
product.ProductName = "TestProduct2";
product.Fields[(int)ProductFieldIndex.UnitsInStock].ExpressionToApply = new Expression(new ScalarQueryExpression(ProductFields.UnitsInStock.SetAggregateFunction(AggregateFunction.Max)), ExOp.Add, 1);

Thanks - I re-wrote it, but then got this:

An exception was caught during the execution of an action query: You can't specify target table 'tblinvoices' for update in FROM clause.

So, it turns out that (at least on my outdated mysql db - version 5.6.43) you can't do precisely that:

INSERT INTO tblinvoices (customerid, invoicenumber) VALUES (5, ((SELECT MAX(tblinvoices.invoicenumber) AS InvoiceNumber FROM tblinvoices) + 1))

We're in the process of switching to a newer DB version, so I'll have to live with the workaround untill then.

But, thank you for your quick help. Much appreciated!

/Jan