how to make auto generated alphanumeric pk?

[bettybarnes]

pls help me with auto generated alphanumeric pk

say

i want my customer id to be like these
CUST00001
CUST00002
CUST00003
CUST00004

any help is a great help.!

Blessings!

[darkelflemurian]

what you can do is to use the Settings feature of visual studio, and each time you create a new pk, save it and the next time load it and add one number at the end only. just remember that probably you will need to split the pk if you are going to use letters and numbers on it in order to add the new number. hope this helps

pls help me with auto generated alphanumeric pk

say

i want my customer id to be like these
CUST00001
CUST00002
CUST00003
CUST00004

any help is a great help.!

Blessings!

[bettybarnes]

thanks i already used this one.. what i'm asking is i want to have it auto generated..
wherein you'll use identity, sequence etc.

[codeorder]

For alphanumeric ID generator, see if this helps.

Dim myChars As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890" '// Characters to use for ID.
        Dim myID As String = Nothing '// used to add random char. until the preset ID length.
        Dim idLength As Integer = 10 '// allow a 10 char. Alphanumeric ID.
        Dim rnd As New Random '// for randomizing char.s.
        For i As Integer = 1 To idLength '// each loop adds 1 random char. 
            myID &= myChars(rnd.Next(0, myChars.Length)) '// add random char. to String. ex: myChars(2) ='s "C".
        Next
        MsgBox(myID) '// display result.

For a alphanumeric ID that only generates a numeric increase in ID numbers, see if this helps.

Private myID As String = "CUST00025" '// ID loaded from/saved to...
    Private iTemp As Integer = 0
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        iTemp = CInt(myID.Substring(4, myID.Length - 4)) '// get only the #'s from String and Convert them to Integer.
        iTemp += 1 '// increase the ID # + 1.
        myID = myID.Substring(0, 4) & iTemp.ToString("00000") '// set the ID back with String and #'s formatted to 5 digit #.
        MsgBox(myID) '// display result.
    End Sub

[bettybarnes]

hi again..im having a problem..my code did not autogenerate :(

[CodeWord]

Betty,

If you are using SQL server, just set your identy column to autogenerate a numeric key, then whenever you want to display it in your application, just concatinate "CUST", and a left padded ID

Like this:

lable1.text = "CUST" & row.Item("CustID").ToString.PadLeft(5, "0"))

the whole CUST, and leading zero would be for display. Under the hood, you'd simply have an integer as your ID and be levereging SQL's identity functionality.

[bettybarnes]

is it not possible if i only just use varchar for my customer_id? so it will accept alphanumeric autogenerated key?

[CodeWord]

I'm not aware of any way to auto generate a alpha-numeric key with a specific pattern (i.e Guids are alpha-numeric, but don't match your pattern) with built in SQL functionality.

You could create a SQL function to autogenerate the key for you on insert that would be called during your insert stored procedure or via a trigger. However, I've noticed that NONE of the code you have supplied with all of your other posts even use stored procedures, so I doubt creating a SQL Function will be an option.

Additional thoughts:
Writing your own function in VB.NET to run on the client would be a problem because you likely have multiple users with a single back-end database. Therefore, a key generated on one machine might be the same as one generated on another machine.

Another idea:
This isn't as efficient, but fits your requirements.

Create TWO ID fields. One called ID which is an auto-generated identity integer. The second field, called Cust_ID (or whatever), is a calculated field that contatinates "CUST" and a padded integer based on the first identity field.

SQL takes care of generating the numbers, and you just format it the way you want in your calculated field.
Sorry for rambling.