automatic id generation which is a primary key

Question

in vb.net while entering a new entry i want to assign a record a unique id like in case of numeric i do this way

Dim ItemID As Integer
KAYAReqConn.Open()
SQLCmd = New SqlCommand("SELECT ISNULL(MAX(ItemID),0) AS ItemID from MstItem", ReqConn)
Dim dr As SqlDataReader

dr = SQLCmd.ExecuteReader
If dr.HasRows Then
    dr.Read()
    ItemID = dr("ItemID") + 1
End If
dr.Close()

in this case m using itemid as a unique id and the format is 1,2,3... and m finding out the max and assigning to a new record but how to assign if the previous id is of the a00001,a00002,a00003,a00004...so on. how i do i produce a unique id in this case

Answer 1

You'd be better using an auto incrementing int identity field in the database for both these cases.

For the 'a0001', you'd still be better storing this as an integer, and if the 'a0000' is for display, then appending this in your code. Or possibly having a prefix field to also store the 'a' part - it depends on your requirements.

Answer 2

You can fetch all ids, access it one by one, break the id and take only integer part, find maximum from them, generate next id and concate it with prefix..store it..

This might be a time consuming process, but I don't find any other way.