Alphanumeric Serial Number for products

Question

Hi,

I am developing a program in which I am assigning specific codes to products of different types that are being stored in the database. The code is in the Alpha numeric format. Now the problem is I don't know how to update the value of this code. Please help...

For ex- If a product is a medicine then the code generated for it should be MED12345

where MED are first three letters of the product and 12345 is any desired number and when I enter the next product in medicine it should automatically generate the code MED12346.

Please help...

Answer 1

Where's your code?

Maybe this?

Dim Temp As String = ""

Dim i As Integer = 1

Temp = "MED" & i.ToString

i += 1

Example

Public Class Form1

    Dim Temp As String = ""

    Dim i As Integer = 1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Text = "Testing"
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Temp = "MED" & i.ToString
        Label1.Text = Temp
        i += 1
    End Sub

End Class

---

You've taught me everything I know but not everything you know.

Answer 2

This could all be done at the database

Public Class Form1
    Public BuilderAccdb As New OleDb.OleDbConnectionStringBuilder With
    {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(
                Application.StartupPath,
                "Products1.accdb"
        )
    }
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Using cn As New OleDb.OleDbConnection With
            {
                .ConnectionString = BuilderAccdb.ConnectionString
            }
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT 
                            Products.Identifier,
                            Prefixes.PrefixText + Products.ItemValue As Product
                        FROM Prefixes 
                        INNER JOIN Products ON Prefixes.Prefix = Products.Prefix
                    </SQL>.Value
                Dim dt As New DataTable
                cn.Open()
                dt.Load(cmd.ExecuteReader)
                dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                DataGridView1.DataSource = dt
            End Using
        End Using
    End Sub
End Class

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

Answer 3

You might use something like this. Two tables Type and Product, with following fields:

Type table:
   Id - auto number? PK
   PrefixText - chars, unique (e.g. MED)
   LastUsed - int, the last number used

Product
   ID - autonumber?, PK
   TypeID - Link to Type table (or could use PrefiText)
   ProductCode - chars, got from prefix table (or just number)

When a record is inserted in product, the Lastused number is looked up in Type for the prefix, it is incremented and stored back to Type. In Product you use the incremented value. You can either store the whole of the Type or just the number since you already have the type.

There's a possible problem if the db is mult user (possible race condition) but that's well known and easily overcome.

Most of this can be done on the db by a stored procedure (or query in Access).

---

Regards David R
---------------------------------------------------------------
The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones.
Object-oriented programming offers a sustainable way to write spaghetti code. - Paul Graham.
Every program eventually becomes rococo, and then rubble. - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

Answer 4

Try like this 

Create Procedure Pro_name 
(
  @product nvarchar(50)
)
AS 
Begin 
Declare @ProductID Nvarchar(15) 
Set @ProductID = 'MED'
 If Not Exists(Select 1  from product)  --to check first record 
 Begin    
 Insert into Product (ProductCode,ProductName)values (@ProductID+'100',@product)
End 
Else 
Begin 
Set @ProductID = (Select top 1 SUBSTRING(ProductCode,4,LEN(ProductCode)-3)+1 from product Order by 1 desc )
 Insert into Product (ProductCode,ProductName)values ('MED'+@ProductID,@product)

Select *from product
End 
End 

---

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 5

Put this code in a form with a Button and a TextBox

Option Strict On
'Note that this assumes that the Alpha part preceeds the numeric part and both are contiguous
'MED12345, SURG33535 are ok but MED12345A or SU5RG33033 are not

Public Class Form1
    Private Function IncString(StringIN As String, Amt As Integer) As String
        Dim Alpha As String = String.Empty
        Dim Numer As String = String.Empty
        Dim Value As Integer = 0
        For Each ch As Char In StringIN
            If IsNumeric(ch) Then
                Numer &= ch
            Else
                Alpha &= ch
            End If
        Next
        If Numer.Length = 0 Then
            Return Alpha & "NO_NUMBER"
        End If
        Return Alpha & (CInt(Numer) + Amt).ToString
    End Function

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        TextBox1.Text = IncString(TextBox1.Text, 1)
    End Sub
End Class

Answer 6

Hi Devon_Nullman,

I have tried your code and it works fine too. But I have to store the generated serial number in a sql database and then when I try to enter another data in the database it should return an updated serial number for the current product from the database which is 1 more than that of the serial number of the previous product.

Please help me about this.

Answer 7

You should not do this from your code.  The process should be built in to the database.  You could use a trigger, but the solution proposed here is probably the best:
http://stackoverflow.com/questions/6444355/mysql-auto-incrementing-alpha-numeric-primary-key

Answer 8

In Dynamics NAV and Navision, there is a "Master Table" with fields like "Last Sales Order Number", "Last Invoice Number", etc - those numbers are updated as each user generates the applicable document. The "IncString" function is built in to the DB engine. For pure MS-SQL, look at what Acamar posted.