"not with sql server solution because this code Resets every day"

since you are using sql server,  the best way to do this is using a sequence as this is exactly what a sequence is designed to do.   Your reset requirement is also one of the many reasons why sequences are so much better than identity columns.  reset it when the day changes, or whatever your reason is to reset it.  You can simply select the value from the sequence using embedded sql.

Sequence Numbers - SQL Server | Microsoft Learn

alternatively, if you are using a next number type of table, you need to manage your locks to prevent another user from updating it.  do the update first, then select the value back (never do a select first and then update unless you are locking the table using a select for update table hint).  

The worst option is to use select max(invoice_no) from invoice table.  

Hi, Mahmoud -

This is exactly why this kind of functionality should be performed in the database. If your app is attempting this on the client side, I don't think you can guarantee uniqueness. This requires a "broker" mechanism to hand out the ID's, which a database can do relatively easily.

In most cases, a "max value" or "last value" table would have a name and an integer-type columns. Since invoice numbering resets each day to begin at one in your case, have you considered an alternative table structure that contains instead name, calendar day, and integer-type columns, with a composite primary key of the name and calendar day columns?

Our commercial app utilizes the simpler name/value structure, and we allocate numbers with a stored procedure that manages the transaction in order to keep the operation atomic (indivisible) and fast. For cases where we need an entire set or range of values, the stored procedure accepts an argument of the number of values being requested and always returns the first and last numbers in the allocated range, even if only one number was allocated.

Best regards, John

This is classic Transaction Processing. There are a couple of ways to handle this:

1. Put all transactions from users into a transaction table.
   Once every X minutes or seconds, your decision, the data in the transaction table gets written into the actual "header" table.
   This way, there is never a traffic jam when multiple users try to write at the same time.
   Note: the transaction table uses a simple 1-up primary key; like current time.
   Another advantage of this method is you can track transactions.


2. Have a separate NVO/function that issues the PK value for the "header" table.
   Any user that SAVES their data accesses this NVO, and the function has a flag to indicate if it is in use or not.
   If the function is IN USE, then the user is placed into a loop to wait.
   At the end of the WAIT cycle, the flag is checked again. If cleared, they may proceed to get the next PK value.
   Create a separate PKVALUE table that has the following fields; use AutoSave. This is the table the NVO/function will access
         IN_USE  boolean, PK_VALUE  long, MODIFIED  datetime, user string
  
// psuedocode
//
set local boolean DONE = FALSE
DO WHILE (not done)

   read IN_USE from PKVALUE t able
   IF TRUE then
      place user in loop for X units of time (X should be read from Registry HKCU or an INI file)
   ELSE
      set IN_USE to true (If AutoSave is not available SAVE the change)
      get current PK value, add +1 (or increment as required)
      set new PK  value in PKVALUE table (again, ensure data is saved)
      set ll_return = new pk value
      set local boolean DONE = TRUE
   END IF

LOOP

RETURN ll_return

Code an insert trigger that assigns the daily sequence number. The PB code would have to retrieve/reselect after update to get the value assigned by the trigger.

I don't normally share code from our ERP system but this is how we update counters.  You would have to modify to see if it is the first counter of the day and reset to 1 in your case.  This is a function where you pass the counter name to it.  This may not be perfect but it works well in our environment.  Sorry, but this doesn't show global variable declarations or related functions (like fx_display_msg).  Use it for what it is worth.

/*
This function will return a long containing the next counter number for the counter specified when the function was called.
//It will return a negative one (-1) if it loops the required number of times and cannot update.
//It will return a -2 if the select is unsuccessful.
//It will return a -3 if the commit was unsuccessful.
//It will return -4 if the connection to the database cannot be established.
*/

// Variable Declarations
boolean lb_connection_reestablished = false
long ll_counter = -1, ll_loop_counter
string ls_counter_name, ls_counter_string, ls_updated_counter_string, ls_db_error, ls_db_property
ulong lu_db_handle
uo_set_sqlca luo_set_sqlca

//Initialize variables
ls_counter_name = upper(counter_name)
ls_counter_name = upper(counter_name)
Choose Case ls_counter_name
for ll_loop_counter = 1 to 1000
//Connect
lu_db_handle=sqlca_counter.dbhandle( )
if lu_db_handle = 0 then //If not already connected
CONNECT USING sqlca_counter;
CHOOSE CASE sqlca_counter.SQLCODE
CASE -1 //error
fx_display_msg("008")//Connect To Database Manager Was Not Successful !
//display database error
ls_db_error = "SQLCode Error Number " + string(SQLCA_counter.SQLCode) + ". SQLDBCode Error Number " + string(SQLCA_counter.SQLDBCode) + ". SQLErrText = " + SQLCA_counter.SQLErrText
MessageBox( "SQL Error", ls_db_error)
return -4
CASE ELSE//connection was established
//SQL Anywhere OEM database - Set Additional Parameters for SQLCA connection
if sqlca_counter.dbms='ODBC' then
luo_set_sqlca = create uo_set_sqlca
luo_set_sqlca.uf_sql_anywhere_oem(sqlca_counter)
destroy luo_set_sqlca
end if
END CHOOSE//check for connection errors
end if
//Get the current value
select counter.counter_no
into :ls_counter_string
from counter
where counter.id = :ls_counter_name
using sqlca_counter;
Choose Case sqlca_counter.sqlcode
Case 0
Case -1
//Just in case the connection somehow got lost, try once to reestablish the connection and try again
if not lb_connection_reestablished then
lb_connection_reestablished = true
disconnect using sqlca_counter;
continue
end if
//store db error text
ls_db_error = "SQLCode Error Number " + string(sqlca_counter.SQLCode) + ". SQLDBCode Error Number " + string(sqlca_counter.SQLDBCode) + ". SQLErrText = " + sqlca_counter.SQLErrText
//set return value
rollback using sqlca_counter;
disconnect using sqlca_counter;
//display database error
MessageBox( "SQL Error", ls_db_error)
return -2
Case else
//store db error text
ls_db_error = "SQLCode Error Number " + string(sqlca_counter.SQLCode) + ". SQLDBCode Error Number " + string(sqlca_counter.SQLDBCode) + ". SQLErrText = " + sqlca_counter.SQLErrText
//set return value
rollback using sqlca_counter;
disconnect using sqlca_counter;
MessageBox( 'Error', 'Counter Was Not Found!')
return -2
end choose

// Update the counter
ll_counter = long(ls_counter_string)
//increment ls_counter_string
ls_updated_counter_string = string(ll_counter + 1)
//update - Notice the old value in the where clause
update counter
set counter_no = :ls_updated_counter_string
where counter.id = :ls_counter_name and
counter_no = :ls_counter_string using sqlca_counter;
if sqlca_counter.sqlcode = 0 and sqlca_counter.sqlnrows = 1 then
commit using sqlca_counter;
//check for commit errors
if sqlca_counter.sqlcode = 0 then
//Commit was successful
return ll_counter
end if
//Commit was not successful
if ll_loop_counter < 1000 then
rollback using sqlca_counter;
continue
end if
ls_db_error = "SQLCode Error Number " + string(sqlca_counter.SQLCode) + ". SQLDBCode Error Number " + string(sqlca_counter.SQLDBCode) + ". SQLErrText = " + sqlca_counter.SQLErrText
rollback using sqlca_counter;
//only display error, if this is the last time we are running the loop
disconnect using sqlca_counter;
MessageBox( "SQL Error", ls_db_error)
return -3
else
ls_db_error = "SQLCode Error Number " + string(sqlca_counter.SQLCode) + ". SQLDBCode Error Number " + string(sqlca_counter.SQLDBCode) + ". SQLErrText = " + sqlca_counter.SQLErrText
rollback using sqlca_counter;
if ll_loop_counter < 1000 then
continue
end if
disconnect using sqlca_counter;
//display database error
MessageBox( "SQL Error", ls_db_error)
return -1
end if
Next//loop to try to get the counter