Creating a random ID that doesn't repeat on SQL Data

Question

I have a registration form but the auto generated id is repeated that results to error on the database table. How can I make it unique every time the regForm loads?

I don't know if this is the right code.

Edit: I want the random number to be numbers only because if I use GUID it gives me 36 char

I want the ID to be random because it is the primary key on my sql data

private void regform_Load(object sender, EventArgs e)
{
    txBNo.Text = "09XXXXXXXXX";

    connect.Open();

    SqlCommand cmd = new SqlCommand("SELECT userid FROM tblLogin", connect);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    System.Data.SqlClient.SqlDataReader dr = null;
    dr = cmd.ExecuteReader();

    Random rnd = new Random();
    int rndNum = rnd.Next(0, 10);
    txBID.Text = "EA-2017-" + rndNum.ToString();

    if (dr.Read())
    {
        if (txBID.Text == dr["userid"].ToString())
        {
            Random rnd1 = new Random();
            int rndNum1 = rnd1.Next(0, 10);
            txBID.Text = "EA-2017-" + rndNum1.ToString();
        }
    }
}

Any type of response is greatly appreciated. Thank you in advance.

Generate GUID. It remains Unique. Guid g; g = Guid.NewGuid(); string strRandomNumber = g.ToString(); — SH7, Jul 30 '17 at 09:37
Can you talk us through why you need it to be random? That might help us direct you to the best solution. — mjwills, Jul 30 '17 at 09:38
@user3501749 sorry but guid creates 36 characters. I want it to be 3-4 digit numbers only — vicserna1997, Jul 30 '17 at 09:45
@mjwills It needs to be random because it is a primary key thank you — vicserna1997, Jul 30 '17 at 09:46
@vicserna1997 Why does it need to be 4 digits long? What happens if you have 10,000 entries? Have you considered using an IDENTITY column? — mjwills, Jul 30 '17 at 09:50
Why are you re-inventing the wheel? Use `IDENTITY` column and let SQL server deal with it for you. Limiting yourself to 4 chars is a bad idea. — webnoob, Jul 30 '17 at 09:53
@webnoob thanks for your suggestion. But the userid must have the `’EA-’ + datetime + //generatedID;` using an identity column only use int with the auto increment right? — vicserna1997, Jul 30 '17 at 09:57
I would suggest you use the `IDENTITY` column then have another column for your unique code. Something like `user_code`. This would allow you to generate your user code (to identify the user) but when you're interacting with the DB, let the `IDENTITY` column take care of the primary key so you don't have to mess with it. — webnoob, Jul 30 '17 at 10:01

score 3 · Accepted Answer · answered Jul 30 '17 at 10:04

Whilst this doesn't answer exactly what you want, I don't believe what you want is a good idea or possible. Having a truly unique number with 4 digits isn't possible and using DateTime variations will lead to problems when more than one user is doing something.

I would suggest you use the IDENTITY column then have another column for your unique code. Something like user_code. This would allow you to generate your user code (to identify the user) but when you're interacting with the DB, let the IDENTITY column take care of the primary key so you don't have to mess with it.

You can use the IDENTITY column value for updates to the table date but if you want to show the user_code to the user then you have the ability to do so. It also allows you to change this user code going forward if your requirements change (for instance, what if you needed to change the prefix from EA to EYUP).

score 2 · Answer 2 · answered Jul 30 '17 at 10:03

OP, with only four digits of variation in the identifier, it is not possible to choose a random number with a reasonably low risk of colliding with some other user performing the same activity.

Instead, the typical way this situation is handled is to generate the identifier not when the form is loaded but when the record is saved, using an identity column. You would then display the ID on a confirmation page that is displayed to tell the user the process of entering the record and saving it is complete.

Balagurunathan Marimuthu · Answer 3 · 2017-07-30T09:49:23.413

0

You can try to insert datetime with seconds to make it identity as below:

txBID.Text = "EA-" + DateTime.Now.ToString("yyyy-hhmmss");

Or, you can use to generate new GUID as below: Refer here

txBID.Text = "EA-2017-" + Guid.NewGuid().ToString();

edited Jul 30 '17 at 09:49

answered Jul 30 '17 at 09:37

Balagurunathan Marimuthu

2,927
4
31
44

Thanks for the datetime suggestion **Balagurunathan Marimuthu** but I can’t use guid because it generates 36 characters. The generated id must have 3-4 digit numbers only. Thank you again – vicserna1997 Jul 30 '17 at 09:48
And what if 2 people enter data at the exact same moment? Using DateTime can **never** be guaranteed to be unique unless you can guarantee the system only has one user. – webnoob Jul 30 '17 at 09:52
@webnoob OP doesn't have had mentioned in his question. He just want to be unique. – Balagurunathan Marimuthu Jul 30 '17 at 09:55
GUID will *in almost all cases* be unique, you can't say the same for DateTime so I would say it's bad to advise that route. – webnoob Jul 30 '17 at 09:56
@webnoob Yes! I agree. But, OP wants only 3-4 chars long needs to be allowed. – Balagurunathan Marimuthu Jul 30 '17 at 09:58
Then he cannot have what he wants. Sometimes, providing what the OP wants isn't in the best interests of the OP. – webnoob Jul 30 '17 at 10:00

Creating a random ID that doesn't repeat on SQL Data

3 Answers3