Selecting last record in SQL Server database not always working

Question

There is claims table which when a user creates a claim, it then gets inserted into this table, each claim is supposed to have its own unique NaturalOrderID allocated to it, the NaturalOrderID is generated by the system, here is the first part of the code, this method gets the maximum number which is the last number to be used later to come with a new unique NaturalOrderID

 private int GetLastNaturalClaimNo(string finYear)
 {
        int maxNo = 0;
        string query = string.Empty;

        query = @"SELECT MAX(CAST(SubString(NaturalOrderId, 4,50) AS int)) FROM Claim
WHERE NaturalOrderId LIKE '" + finYear + "/%' AND (CAST(SubString(NaturalOrderId, 4,50) AS int) BETWEEN 10000 AND 500000) ";

        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RAC_DARAConnectionString"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand(query, conn);// ***OLD Query**
            conn.Open();

            object result = cmd.ExecuteScalar();

            // If no claim of this sort is found, then we are starting a new financial year.
            maxNo = string.IsNullOrEmpty(result.ToString()) ? 10000 : Convert.ToInt32(result);
        }

        return maxNo;
    }

Then this return maxim will be used to allocated the new NaturalOrderId to the new claim that will be created, here is the code that generates a new OrderId based on the maximum number from the previous method

public string GenerateNaturalClaimNumber(int claimId, DateTime date, string userName)
{
        // Get the financial period
        string finYear = GetFinancialYear(date);

        // Get the maximum claim inserted
        int maxClaimNo = GetLastNaturalClaimNo(finYear);

        int newClaimNo = maxClaimNo + 1;

        string naturalNo = finYear + "/" + newClaimNo.ToString();

        // Update the claim with the new number ASAP (before someone else grabs the number and creates a duplicate)
        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RAC_DARAConnectionString"].ConnectionString))
        {
            string sql = @"UPDATE Claim SET 
                    NaturalOrderId = @naturalNo, 
                    UserName = @userName
                    WHERE ClaimId = @claimId";
            //"UPDATE Claim SET NaturalOrderId = '" + naturalNo + "' WHERE ClaimId = " + claimId.ToString()
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@naturalNo", naturalNo);
                cmd.Parameters.AddWithValue("@userName", string.IsNullOrEmpty(userName) ? null : userName);
                cmd.Parameters.AddWithValue("@ClaimId", claimId.ToString()); // it was claimNo
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

        return naturalNo;
    }

My problem is, this doesn't always seem to work as it sometimes allocate same naturalOrderId to two or more different claims (screen shot of few examples attached)

NaturalOrderID duplicates

Answer 1

You are computing the naturalOrderID at your appside, you 100% should be using the identity column that SQL Server provides. What is probably happening is you have multiple orders going in at the same time, so when you pull that max ID someone else is inserting while you are calculating. Thus your natural order is out of sync.

Try and use the SQL Server Identity property