Member Avatar for haqayyum

Dear friends

I am using vb 2008 with access 2007 at the backend. I want to make custome invoice id like Inv0001. when make next invoice the id should be Inv002 and so on. add 1 numeric value in the previous one. Please help me

any help will greatly appriciated

  • 6 Contributors
  • 11 Replies
  • 2K Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

Good tips above. A couple of gotchas though: counting rows (your Post #4) or doing MAX(ID)+1 (as suggested) will break if rows are deleted and can collide under concurrency. Also, storing a text key like INV0001 as the primary key hurts indexing, as noted. A simple, safe pattern with Access is: keep a numeric primary key (Autonumber/Long), and derive the display code when you save or when you show it. That gives you uniqueness without races, and sorting stays numeric.

Example workflow (VB 2008 + Access, OleDb). The invoice row is inserted first; then we read the new Autonumber using the same connection/transaction and format your code. You can store the pretty code in a separate column, or compute it on the fly for display.

Public Function SaveInvoice(connString As String, customerId As Integer, transDate As Date) As String
    Using con As New OleDb.OleDbConnection(connString)
        con.Open()
        Using tx As OleDb.OleDbTransaction = con.BeginTransaction()
            '1) Insert the invoice (PK InvoiceID is Autonumber)
            Using ins As New OleDb.OleDbCommand("INSERT INTO Invoices(CustomerID, TransDate) VALUES (?,?)", con, tx)
                ins.Parameters.AddWithValue("@p1", customerId)
                ins.Parameters.AddWithValue("@p2", transDate)
                ins.ExecuteNonQuery()
            End Using

            '2) Get the new numeric ID generated by Access on this connection
            Dim getId As New OleDb.OleDbCommand("SELECT @@IDENTITY", con, tx)
            Dim newId As Integer = CInt(getId.ExecuteScalar())

            '3) Build your display code (leading zeros, INV prefix)
            Dim displayCode As String = "INV" & newId.ToString("D4")

            'Optional: persist the display code in a separate text column
            Using up As New OleDb.OleDbCommand("UPDATE Invoices SET InvoiceCode=? WHERE InvoiceID=?", con, tx)
                up.Parameters.AddWithValue("@p1", displayCode)
                up.Parameters.AddWithValue("@p2", newId)
                up.ExecuteNonQuery()
            End Using

            tx.Commit()
            Return displayCode
        End Using
    End Using
End Function

Notes:

  • With OleDb, parameter order matters; keep Adds in the same order as the ? placeholders.
  • If you only need to show the ID in the UI, skip storing InvoiceCode and compose it when binding: "INV" & invoiceId.ToString("D4").
  • This approach also sidesteps the leading-zero issue mentioned by and avoids the string-sorting woes ran into after DOC10.

Recommended Answers

All 11 Replies

Member Avatar for GAiXz

get the max value of your ID in your database then add one

like this

SELECT MAX(ID) as MAXID from tblID

Member Avatar for Reverend Jim

to add the leading zeroes you can pad the number out by

nextid.ToString("0000")

once you have maxid. You can generate the next id in the select by

select max(ID)+1 as nextid from tblID

Edited by Reverend Jim because: n/a
Member Avatar for haqayyum

my following code will only show 1 numeric value in TransIDTextBox however i want to showing this format like 00001 or 00002

Private Sub transIDload()
ds.Clear()
da = New OleDbDataAdapter("Select * FROM Transactions", dbcon)
da.Fill(ds)
da.Dispose()
'dbcon.Close()
If ds.Tables(0).Rows.Count > 0 Then
txtTransID.Text = ds.Tables(0).Rows.Count + 1
txtTransID.ReadOnly = True
txtTransDate.Text = Format(Now, "short date")
txtTransNo.Text = txtTransID.Text
txtDep.Text = "0.00"
txtWda.Text = "0.00"
Else
txtTransID.Text = 1
txtTransID.ReadOnly = True

End If
'DataGridView1.DataSource = ds.Tables(0)

End Sub

Member Avatar for Pgmer

Get the MAX(ID) from table and then append 0000 to it and show.

Member Avatar for mani-hellboy

Get the MAX(ID) from table and then append 0000 to it and show.

Forgot the one thing to find the length of ID there only easy when you add 10,11...,100,101,....,1000,1001...

temp_id=string.Empty;
for(int i=0;i<(5-id.lenth);i++)
{
 temp_id+="0";
}
temp_id+=id;
Member Avatar for poojavb

Forgot the one thing to find the length of ID there only easy when you add 10,11...,100,101,....,1000,1001...

temp_id=string.Empty;
for(int i=0;i<(5-id.lenth);i++)
{
 temp_id+="0";
}
temp_id+=id;

I have done the following to get the custom ids like DOC1, DOC2, DOC3 and so on....

I have create a table which will store the number in it....

and the master table will just save the value in database with string

eg.

I have a table as IDvalues with one field that is DOCValue and datatype number with value 1

in the DOctormaster table I have the ID field as the primary key and datatype as text....

when I click on save I first take the max value from IDvalues table and the value is appended with "DOC" string and saved in Doctormaster table as DOC1.....

Soon after saving I have incremented the value in IDvalue by 1.....

I have done it this way since when I had created a field as ID in doctormaster table with datatype as Text it used to get appended correctly till DOC10 but after that it showed problem....

So I opted for this long but safe method....

U can give it a try....even if u dont get it....just post again....

Edited by poojavb because: n/a
Member Avatar for Pgmer

But i prefer to have primary key as integer not as text or varchar. indexing will be done on primary key if it is text performance will go down. U can have one more column in whci u can save the data Doc1,Doc2 etc..

Member Avatar for poojavb

But i prefer to have primary key as integer not as text or varchar. indexing will be done on primary key if it is text performance will go down. U can have one more column in whci u can save the data Doc1,Doc2 etc..

I have posted for getting the string Doc1 and Doc2......and so on....

Edited by poojavb because: n/a
Member Avatar for Pgmer

in the DOctormaster table I have the ID field as the primary key and datatype as text....

Data type u mentioned is of varchar which i said not preferable.

Member Avatar for mani-hellboy

I have done the following to get the custom ids like DOC1, DOC2, DOC3 and so on....

I have create a table which will store the number in it....

and the master table will just save the value in database with string

eg.

I have a table as IDvalues with one field that is DOCValue and datatype number with value 1

in the DOctormaster table I have the ID field as the primary key and datatype as text....

when I click on save I first take the max value from IDvalues table and the value is appended with "DOC" string and saved in Doctormaster table as DOC1.....

Soon after saving I have incremented the value in IDvalue by 1.....

I have done it this way since when I had created a field as ID in doctormaster table with datatype as Text it used to get appended correctly till DOC10 but after that it showed problem....

So I opted for this long but safe method....

U can give it a try....even if u dont get it....just post again....

You can try manually increment the numeric value for example get the string after first 3 letter (ie., Doc10 => "10") convert to integer then increment one then concatenate (ie., "doc"+"11")

Member Avatar for Reverend Jim

But that doesn't get you leading zeroes. For that you need to apply formatting such as

nextid.ToString("0000")

By the way, while it is true that there is a performance hit when you use a varchar as a primary key, it will likely be negligible unless the database is very large.

Edited by Reverend Jim because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.