Generating Auto ID in Database

Question

i have a table that captures the details of Products. I want to be able to generate an Auto ID that contains special characters

I have been able to write code that generates the auto ID and also add a new one. But the issue arises when it gets to the 10th and 11th record.

It seems to be seeing the 9th record as the MAX in the database.

this makes it to throw error that there will be duplicate. For example, record 9 generates CAT009, record 10 generates CAT0010 but instead to generate record 11 as CAT0011, it seems to keep generating CAT0010 because of the MAX function i used in the SELECT statement.

  Sub auto()
    Try
        Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\inventory1.accdb")
        cn.Open()
        Dim dr As OleDbDataReader
        Dim cmddr As New OleDbCommand
        cmddr.CommandText = "SELECT MAX(category_id) as max_id FROM tblcategory "
        cmddr.Connection = cn
        dr = cmddr.ExecuteReader
        dr.Read()
        If IsDBNull(dr("max_id")) Then
        autonumber = "CAT00" & 1
        Else
            Dim str As String
            str = dr.Item("max_id").ToString()
            Dim P As Double
            Dim N As Double
            N = Len(str)
            P = N - 5
            autonumber = "CAT00" & Convert.ToInt32(str.Substring(5, P))+ 1               
        End If
        cn.Close()
    Catch Ex As Exception
        MsgBox(Ex.Message)
        Console.WriteLine(Ex.Message)
    End Try
End Sub

category_id is a string? Max works more logically on numbers. — LarsTech, Apr 09 '19 at 17:35
The code produces **string** values which will be compared as **strings** and not according to numeric values. `CAT009` is always sorted / compared last to `CAT0010` or `CAT0011` or `CAT00123423432`, since it compares character by character. (You call these "autonumber", but they are certainly not Autonumber in the sense that you are having to manually assign them. Access already has an Autonumber column ability. If desired, the true AutoNumber can be *formatted* with prefix characters like "CAT001" for presentation, all the while letting the DB manage the actual numbers appropriately.) — C Perkins, Apr 09 '19 at 17:42
That's because CAT0010 should instead be CAT010. This also limit your unique to 999 entries only. It would be easier if you just store an auto increment number and format that number properly on the screen. — the_lotus, Apr 09 '19 at 17:55
@C Perkins, if I format in access, to reference the database will only recognise the numbers and not recognize the formatted characters CAT00. I want to be able to reference a particular record from the database through code using the field category_id — mantics, Apr 09 '19 at 18:38
Just use the integer autonumber and when you want to display it to the user make it Cat & yourID.ToString("0000") and since it's always CAT, remove that and parse the numeric section back to integer to query the database. — Charles May, Apr 09 '19 at 18:51
You can't show a MessageBox in a Console Application. Which is it WinForms or Console? — Mary, Apr 09 '19 at 19:39
Possible duplicate of [Custom user autonumber](https://stackoverflow.com/questions/35574726/custom-user-autonumber). Gustav's answer properly formats the number to have compatible zero-padding. It is not for VB.Net, but the key elements are in his concise answer. — C Perkins, Apr 10 '19 at 00:21

score 0 · Answer 1 · answered Apr 10 '19 at 01:00

The overall concept is a duplicate of other questions, the key concern being proper formatting of the number with correct zero padding. There are various ways to do this in VB.Net, but the Int32.ToString(String) accepts a format string that can handle zero-padding. It's also unnecessary to calculate the string length, since Substring() has an overload to get all remaining characters to the right.

    If IsDBNull(dr("max_id")) Then
      autonumber = "CAT001"
    Else
        Dim str As String
        str = dr.Item("max_id").ToString()
        autonumber = "CAT" & (Convert.ToInt32(str.Substring(3)) + 1).ToString("000");
    End If

If the first two zeros should be static (i.e. always "CAT00"), then it is critical to still format the changing portion with proper padding. Otherwise the text strings will not sort properly and so the numeric order will be ineffective. In that case, you should use something like

    If IsDBNull(dr("max_id")) Then
      autonumber = "CAT00001" '* NOT CAT001
    Else
        Dim str As String
        str = dr.Item("max_id").ToString()
        autonumber = "CAT00" & (Convert.ToInt32(str.Substring(5)) + 1).ToString("000");
    End If

Of course in either case, be aware that the number of integer digits limits the range of valid numbers. For example, 3 digits ("000") has a maximum of 999.

A better approach is to use pure integer AutoNumbers in the database: 132 -> "CAT132". Then just format the number for display purposes, and when necessary parse user input text to extract the integer portion: "CAT0089" -> 89. This approach highly simplifies the backend code and leaves the AutoNumber functionality to the database itself... no need to have custom code to generate the numbers.

Generating Auto ID in Database

1 Answers1