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)