Click here to Skip to main content
15,850,190 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i'm using 2008 and Access DB as backend
on this software i'm trying to generate automatically number as invoice number or ID
can you help me on doing this
note : i don't need to use ID Field (Automatic ID from Access itself )
i need it to be like this

last invoice number is 1234
i nwant to create an invoice , so one of the textboxes will be 1235 (Last Invoice Number + 1)

sorry for not clear English
i hope that you got me

Joan M 7-Aug-12 10:25am    
Shouldn't this be automated in the database?

I mean in order to avoid two clients accessing the database at the same time?

In each query you can get it done properly, of course it can happen that you get empty numbers if one of the clients cancel the addition...

Write a query to get the maximum id from the database e.g. select max(id) from invoicetable.
Store this result in a variable, increment it by 1, and then display it in the textbox.
Share this answer
You could just use a query

SELECT MAX(invoiceNum) FROM Table

However, if you want uniqueness use a guid or some other scheme
Share this answer
Share this answer
khaliloozx 16-Jul-11 14:05pm    
I think that i can easily search on google
thatraja 16-Jul-11 14:29pm    
Of course dude
bbirajdar 7-Aug-12 10:45am    
Yes that what we want.. Search on google, try to implement and come back only if you have specific problems...
I used this code
it's solved

<pre lang="vb">Private Sub auto()
        Dim cmd As New OleDbCommand
        cmd.CommandType = CommandType.Text
        cmd.Connection = conn
        cmd.CommandText = "Select Max (custno) from [cust]"
        Dim a As Integer = cmd.ExecuteScalar
        Custno.Text = a + 1
        If Custno.Text = "" Then

        End If

Thanks for all
Share this answer
Dave Kreskowiak 17-Jul-11 9:53am    
Yeah, that's nice and all, but have you thought of the case where two clients running your code execute this at the same time?? Both clients are going to come up with the exact same "Customer Number".
bbirajdar 7-Aug-12 10:46am    
Dave is right..
tanugarg 28-May-14 3:37am    
this code helpful for me but i have one query that if database is not having value means column has null value this code give an error Conversion from type 'DBNull' to type 'Integer' is not valid
Don't generate the number in advance. More than one user will generate the same invoice number. Instead generate the Invoice number at the time of inserting the record into the database

INSERT INTO [cust](InVoiceNumber,..,..,..) values (SELECT Max (custno) from [cust],..,..,..)
Share this answer
You can actually use this:
"cmd.CommandText = "Select Max (custno)+1 from [cust]"

It will select the maximum custno then it automatically incremented by 1.
Share this answer
THOUFEEK AHAMED 3-Nov-18 12:39pm    
if no record in database i will enter first record means what keyword i use instead of max because it will show error when select max when no record found in table

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900