hi,

if the table is blank , how can we get the preset auto-increment number ?

i have a try on this :

code:
--------------------------------------------------------------------------------

select case when LAST_INSERT_ID() = " " then '10001' else LAST_INSERT_ID()+1 end as running_num from tbl_invoicehdr limit 1

--------------------------------------------------------------------------------



it return nothing if the table have no records

even i try as follow but give me same blank result

code:
--------------------------------------------------------------------------------

SELECT LAST_INSERT_ID() FROM tbl_invoicehdr

--------------------------------------------------------------------------------




besides in mysql , to compare the column is contains empty string, is it like this ?

code:
--------------------------------------------------------------------------------

LAST_INSERT_ID() = " "

--------------------------------------------------------------------------------



thank you very much for helping

database used : mysql

1. you can only use LAST_INSERT_ID right after an INSERT

2. LAST_INSERT_ID returns a number, not a string

3. if you want the preset auto_increment number before it is assigned, you are doing something wrong logically

thank you r937 for guidance

just want to know, why when no records, and LAST_INSERT_ID() give me empty , but running_num still show in empty and not '10001' ?

When you specify the table, you will get an empty result set since you select rows from an empty table. Even if you specify a function (in this case, LAST_INSERT_ID()) there are no rows, so you will never get any result set. You probably meant to do

select case when LAST_INSERT_ID() IS NULL then '10001' else LAST_INSERT_ID()+1

but since it will never be NULL (but 0) if you have not inserted any rows in the current transaction, you can skip the case statement. Are you sure you aren't looking for the information returned by

SELECT CASE WHEN MAX(id) IS NULL THEN '10001' ELSE MAX(id)+1 END AS running_num FROM tbl_invoicehdr

?

The latest query returns the highest id number plus one. However, if you delete the number with the highest id number, you will not get the next auto_increment value that will be used. On the other hand, you should probably not use the primary key in the way you think you want to!

no probably about it

hi snorp and r937 ,

the reason i would like to get the initial value of max auto increment is because in my application , there have a module to create invoice, so before hand, when user keyin information for invoice, i need to show in invoice page about the invoice number, so when tbl_invoicehdr table have no records inside, i need a return value to show in invoice page as invoice number

set the invoice number after the fact.