Randomly generated ID numbers

Question

hello,


I am trying to create a code for a database table with a unique and randomly generated ID numbers in the format similar to "0001", "0002", "0003", ... to "9999". Do anyone know or have an idea how to go about this? I would be grateful to know please. Thanks.

Answer 1

Hi,

 

Randomly generating ID numbers becomes a bit problematic as time goes on. The reason is you need to check that the random number generated wasn't previously used. So say you have 5000 records the amount of time needed to produce a un-used random ID might be quite long because the random number generator might continue to create a number that has been used, it's random so it might take 10000 attempts to find the next available ID.

 

Hopefully I described that well enough. It's like someone says I'm thinking of a number between 1 and 9999 and you randomly guessing it.

 

What your better doing is generating the numbers in a database table / file and then randomly selecting from the available list of numbers. Then when the number is used remove it from the list. Next time you randomly select from the available list you only select a number that hasn't been used. So after 5000 records your next random number comes from the list of remaining numbers and so you don't need to do the check.

 

Again I hope I described that ok.

Answer 2

Thank you very much. Your description is well understood and the expert analysis has been noted. With this analysis in mind,
I will now opt for the SQL Server identity specification starting from 1 for the identity seeding. But my question now is it possible to reformat the identity seeding to a format similar to "0001", "0002", "0003", ... to "9999". Thanks.

Answer 3

Hi,

Strictly speaking, there is no such thing as a randomly generated number especially if there are parameters - this is because the computer could repeat the same "random" process to generate a number; and as more records are filled then the programme will doing more cheking to see if the number exists, and generate another random number if needed.

Suggestion - instead of randomly generating a number, how about auto increment the number. That way you'll know each number will be different.

Steve

Answer 4

Thanks Steve. I have now gathered from the post by Derek Smyth, and I wanna use the SQL Server auto increment number. But how do I format auto increment number a 4-digit number (similar to "0001", "0002", "0003", ... to "9999") starting from the number 1. Thanks.

Answer 5

Unfortunately I'm not sitting in front of my computer now and I can't remember!!

Hopefully, someone else will be reading this and they can help you

Steve

 

Answer 6

Hi AngelinaPerez,

Where do you need to format the above number? Is it in the server or the client application?

I presume that you need to format the number in the client application. If you display the data in the datagridview, this control supplies the CellFormatting event. You can handle this event to format the cell value. Try it. If you have any further issues, feel free to tell us.

Best regards,
Riquel

Answer 7

Thanks Riquel, I am actually thinking of formatting the number at the database level or possibly via a VB textbox. Is there an easier way to this. Thanks.

Answer 8

Hi AngelinaPerez
You just can create a GUID and  convert it to string . Then just Substring the String  upto 4.


Satya

Answer 9

Hi AngelinaPerez,

You can fetch data from the database and databinding to TextBox control with the required format. It is the better option not to modify the value to get the required format in the database. This should be implemented in the client application to display the data with the specific format. You need to handle Binding.Format event. The Format event is raised when data is pushed from the data source into the control. You can handle the Format event to convert unformatted data from the data source into formatted data for display. When data is pulled from the control into the data source, the Parse event is raised to unformat the displayed value, then the Format event occurs to reformat the data for display. If you have any further issues, feel free to tell us.

Best regards,
Riquel

Answer 10

Thanks Riquel. I am just wondering if you're referring to something in this line....

MyString = TextBox.Text 
if Len(MyString)= 2 then ...

Answer 11

Convert the number to a string and pad it with zeros to fill 4 places, like this:

 

Code Snippet

Dim number As Integer = 5

TextBox1.Text = number.ToString.PadLeft(4, "0"c)

 

 

 

Answer 12

Take a look at the following code example. If you have any further issues, feel free to tell us.

Public Class Form1
    Dim Customer As New DataTable
    Dim CustomerGroups As New DataTable
    Dim bs1 As New BindingSource
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        CustomerGroups.Columns.Add("ID", GetType(Integer))
        CustomerGroups.Columns.Add("GroupDescription", GetType(String))
        Customer.Columns.Add("CustomerGroup", GetType(String))
        Customer.Columns.Add("name", GetType(String))
        For i As Integer = 0 To 2
            Customer.Rows.Add("cg" & i.ToString, "name" & i.ToString)
            CustomerGroups.Rows.Add(i, "GroupDescription" & i.ToString)
        Next
        bs1.DataSource = CustomerGroups
        Dim binding1 As Binding = New Binding("Text", bs1, "ID")
        TextBox1.DataBindings.Add(binding1)
        AddHandler binding1.Format, AddressOf Formatb
        AddHandler binding1.Parse, AddressOf Parseb
        BindingNavigator1.BindingSource = bs1
        bs1.Position = 1
    End Sub
    Private Sub Formatb(ByVal sender As Object, ByVal cevent As ConvertEventArgs)
        If cevent.DesiredType IsNot GetType(String) Then
            Exit Sub
        End If
        Select Case CType(cevent.Value.ToString.Trim, Integer)
            Case 0
                cevent.Value = "one"
            Case 1
                cevent.Value = "two"
            Case 2
                cevent.Value = "three"
        End Select
    End Sub
    Private Sub Parseb(ByVal sender As Object, ByVal cevent As ConvertEventArgs)
        If cevent.DesiredType IsNot GetType(Integer) Then
            Exit Sub
        End If
        If (cevent.Value.trim = "one") Then
            cevent.Value = 0
        ElseIf (cevent.Value.trim = "two") Then
            cevent.Value = 1
        ElseIf (cevent.Value.trim = "three") Then
            cevent.Value = 2
        End If
    End Sub
End Class