Increment Alphanumeric In Vb.net Form Textbox

I am trying to generate an auto increment alphabumeric ID in a textbox on form load, and with the below code, and I can insert the first set of data to ID 'ABC1' to an empty table,

Solution 1:

With this code you will get a formatted string that could be correctly retrieved from your database using the MAX function

Dim curValue asInteger
Dim resultas String
using con as SqlConnection =new SqlConnection("server=localhost;initial catalog=TEMPDB;Trusted_Connection=True;")
    con.Open()
    Dim cmd  =new SqlCommand("Select MAX(ID) FROM TEST", con)
    result= cmd.ExecuteScalar().ToString()
    if string.IsNullOrEmpty(result) Thenresult= "ABC000"
    End If

    result= result.Substring(3)
    Int32.TryParse(result, curValue)
    curValue = curValue  +1result= "ABC" + curValue.ToString("D3")

EndUsing

This code will store in the ID column strings formatted as 'ABC001', 'ABC002' and so on. The inclusion of zeros before the number stored is required by the MAX function if you try to use it on string values otherwise a string ABC2 will be higher than ABC19 because the comparison of the 4th character. Of course when you query a datatable to search for a single result like above is simpler to use ExecuteScalar than using a datareader.

Solution 2:

Try this

PublicFunction IncrementString(ByVal Sender AsString) AsStringDim Index AsIntegerFor Item AsInteger = Sender.Length - 1To0Step -1SelectCase Sender.Substring(Item, 1)
            Case"000"To"999"CaseElse
                Index = Item
                ExitForEndSelectNextIf Index = Sender.Length - 1ThenReturn Sender & "1"'  Optionally throw an exception ?ElseDim x AsInteger = Index + 1Dim value AsInteger = Integer.Parse(Sender.Substring(x)) + 1Return Sender.Substring(0, x) & value.ToString()
    EndIfEndFunction

Then call it as shown:

Dim comm AsNew SqlCommand
 comm.CommandText = "SELECT MAX(UserID) FROM SQLTable"