locked
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.


    Sunday, August 24, 2008 9:24 AM

Answers

  • 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.

    Sunday, August 24, 2008 5:25 PM
  • 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)

     

     

     

    Friday, August 29, 2008 12:49 AM
  • 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
    Friday, August 29, 2008 5:24 AM
    Moderator

All replies

  • 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.

    Sunday, August 24, 2008 5:25 PM
  • 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.

    Monday, August 25, 2008 12:19 AM
  • 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

    Monday, August 25, 2008 12:35 AM
  • 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.
    Monday, August 25, 2008 12:43 AM
  • 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

     

    Monday, August 25, 2008 12:51 AM
  • 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
    Tuesday, August 26, 2008 9:53 AM
    Moderator
  • 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.
    Tuesday, August 26, 2008 5:03 PM
  • Hi AngelinaPerez
    You just can create a GUID and  convert it to string . Then just Substring the String  upto 4.


    Satya
    Wednesday, August 27, 2008 5:37 AM
  • 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

    Thursday, August 28, 2008 2:03 AM
    Moderator
  • Thanks Riquel. I am just wondering if you're referring to something in this line....
    MyString = TextBox.Text 
    if Len(MyString)= 2 then ...
    Friday, August 29, 2008 12:19 AM
  • 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)

     

     

     

    Friday, August 29, 2008 12:49 AM
  • 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
    Friday, August 29, 2008 5:24 AM
    Moderator