Hello. I'm trying to build an INSERT with auto numbering in Access, but I'm not getting it. In SQL Server, MySql and PostGres use the COALESCE function, but I do not find this function in Access.
I usually use it as follows:
INSERT INTO CONTA(COD_CONTA, DESCRICAO, SALDO_INICIAL)
VALUES ((SELECT COALESCE((MAX(COD_CONTA), 0) + 1) FROM CONTA), @Descricao, @SaldoInicial);
The query receives two parameters: Description and Initial Balance. For the column COD_CONTA the value to be entered would be "Greater Code + 1". The COALESCE function is used to test if the return value is NULL, will take the second argument "0" and add it with "1" otherwise it will be the value found in the table.
I've used Iif and even worked in SELECT:
SELECT IIF(ISNULL(MAX(COD_CONTA)), 0, MAX(COD_CONTA)) + 1 FROM CONTA;
It worked perfect, but when I put it in the previous query it gave error:
INSERT INTO CONTA(COD_CONTA, DESCRICAO, SALDO_INICIAL)
VALUES ((SELECT IIF(ISNULL(MAX(COD_CONTA)), 0, MAX(COD_CONTA)) + 1 FROM CONTA), @Descricao, @SaldoInicial)
ErrorCode: -2147467259 [Microsoft JET Database Engine] NativeError: 0, SQLState: ErrorMessage: Unspecified error
You could use another query to get the largest code and add up. But everything in a query is only more practical. I do not know what to do.
I'm using DataBase.Net to write the database and test the queries. I created an Access.mdb file.