Auto-generated Reference number repeats itself more than twice

Question

Hello,

I have been trying to create an automated Reference number that can be used to identify each user but in some aspect, there is a repetition in the number.

From my code, the Reference number is supposed to be unique but there are multiple of the same Reference number. How can I create a unique Reference number that cannot be repeated?

For example, if a Reference number like 539873321 exists in the database table, it should generate a new one. But whenever I go to the page a new Reference number is created and in some cases it repeats the same number and I will have like 3 of the same Reference number 539873321

Please I want to create an auto-generated Reference number that cannot be repeated, How can I do that please?

*Here is my code to generate number automatically without repeating the same number

protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                AutoGenerateNumber();
            }
        }

This code generates 9-digit automatic reference number

private string GetAutoNumber()
        {
            string numbers = "1234567890";
            string characters = numbers;
            int length = 9;
            string id = string.Empty;
            for (int i = 0; i < length; i++)
            {
                string character = string.Empty;
                do
                {
                    int index = new Random().Next(0, characters.Length);
                    character = characters.ToCharArray()[index].ToString();
                } while (id.IndexOf(character) != -1);
                id += character;
            }

            return id;
        }

This code is where is search the database table to see if the reference number exists, then it should generate another reference number

private void AutoGenerateNumber()
        {
            try
            {
                using (SqlConnection con = new SqlConnection())
                {
                    con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    SqlDataReader dr;
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Parameters.Clear();
                        cmd.CommandText = "SELECT reference_no FROM IDTable WHERE 
reference_no=@reference_no"; // Here I tried to check the table to see if the Reference number numbers exist. And if it exists then it should automatically create a new one                         cmd.Parameters.AddWithValue("@Reference_no", ReferenceLabel.Text);
                        cmd.Connection = con;
                        con.Open();
                        dr = cmd.ExecuteReader();
                    }
                    if (dr.HasRows)
                    {
                       
                    }
                    else
                    {
                        List<string> result = new List<string>();
                        if (Session["Numbers"] != null)
                        {
                            result = (List<string>)Session["Numbers"];
                        }

                        string number = GetAutoNumber();
                        if (!result.Contains(number))
                        {
                            result.Add(number);
                            Session["Numbers"] = result;
                            ReferenceLabel.Text = number;                         }
                    }
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

Answer 1

Hi @Donald Symmons,

You can't use Session to achieve this because the Session will be cleared when the application stops.

You can determine if the generated auto-generated reference number already exists in the database. If so, you can re-run the GetAutoNumber() method.

 private void AutoGenerateNumber()
 {
     try
     {
         string number = GetAutoNumber();
         using (SqlConnection con = new SqlConnection())
         {
             con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
             SqlDataReader dr;
             using (SqlCommand cmd = new SqlCommand())
             {
                 cmd.Parameters.Clear();
                 cmd.CommandText = "SELECT reference_no FROM IDTable WHERE reference_no = @reference_no"; // Here I tried to check the table to see if the Reference number numbers exist. And if it exists then it should automatically create a new one
                 cmd.Parameters.AddWithValue("@Reference_no", number);
                 cmd.Connection = con;
                 con.Open();
                 dr = cmd.ExecuteReader();
             }
             if (dr.HasRows)
             {
                 string newnumber = GetAutoNumber();
                 //Insert a new number
             }
             else
             {
             }
         }
     }
     catch (SqlException ex)
     {
         string msg = "Error:";
         msg += ex.Message;
         throw new Exception(msg);
     }
 }

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 2

How about using GUID?

Answer 3

your code only works if you website has only one user. two concurrent users will get the same number. you need to change your code so that the new number is saved in the database in the same transaction that creates it. this generally will cause gaps, as a user may be assigned a number, but need complete the transactions.

if gaps are an issue, then the unique number should not be assigned until the first transaction save.

note: there should also be a unique index, so duplicates can not be created.