How to Auto Generate Primary Keys in a Access Database Using VB.Net

[Gizboozle]

Hi I have the following problem:

I have a program with a Sales and Stock Table. I am adding a new record in the sales table. Which is located in Access Database called newBook.mdb. The fields are Date, IncoiceNumber, BookCode, Quantity SubTotal Vat and Total.

- The user will input all but the Invoice number.
- This must be automatically generated.
- It must look like: “I0001” then "I0002" and so on.
- So it starts with an I and the total length is 5.

I have a presentation layer and a business layer.

The business layer has the GetConnection Function and the AddSale Function and the presentation layer has the Add button click event.

Where would I insert the code for this and what would the code look like?

I am thinking a For loop to loop through all the records and a Sub String function to retrieve the 4 digits on the right as the "I" will stay the same.

Thanks,

 

[cjard]

just put an autonumber in, then write your presentation logic like:

SELECT id, 'I' & Format(id, '00000') as inv_num, blah blah.. FROM table


The user can see inv_num, the database will use ID as the primary key. tickety boo

 

[Gizboozle]

Finally got it:

DsInvoice = BusinessClass.GetSaleInvoice(cboBook.Text.Substring(0, 10))
counter = DsInvoice.Tables("INVOICE").Rows.Count - 1
InvoiceNum = DsInvoice.Tables("INVOICE").Rows(counter)!InvoiceNumber
InvoiceNum = InvoiceNum.Substring(1, 4)
inv = Convert.ToInt16(InvoiceNum)
inv = inv + 1
invNum = ("I" & InvoiceNum.PadLeft(4, "0"))
txtInvNum.Text = ("I" & invNum)