Generating unique random number as identification in database

Question

I have been making ID in my program but to have one it need to generate random numbers but if it already exist in my database then it has to perform another random numbers to prevent duplication . My problem is how do i generate another random numbers again?

here's my code:

    private void button1_Click(object sender, EventArgs e)
    {
       random rand = new random();
        aidentification.Text = rand.Next(1, 5).ToString();   
        string exist = string.Empty;
        exist =  "Select * from fruit_stock " +
                  "where identification=@id";



        SqlConnection conn = new SqlConnection("server=WIN10;database=fruit_storage;user=fruit_admin;password=admin;");

       SqlCommand cmd = new SqlCommand()
    {
        Connection = conn,
        CommandType = CommandType.Text,
        CommandText = exist

    };
cmd.Parameters.AddWithValue("@id", aidentification.Text);
        try
        {
         conn.Open();
         SqlDataReader reader;
         reader = cmd.ExecuteReader();
            if (reader.HasRows){
               // PUT THE CODE HERE TO PERFORM ANOTHER RANDOM NUMBERS
            }
         conn.Close();
        }
      catch (Exception ex) {
          MessageBox.Show(ex.Message);
      }

    }      
}

}

Why not make the SQL portion a function that determines if ID has rows, and returns has or has not, then you can just { pick number } while (has rows) — BugFinder, Dec 01 '17 at 07:24
better way you mark the `identification` column as primary key and then do some code changes in your code for whenever you try to insert record with same id then this will throw the exception — Jaydip Jadhav, Dec 01 '17 at 07:25
Why not declare an `ID int IDENTITY ` column on your table - that way it will autogenerate the ID for you and you need no guessing. This approach of guessing a free, new ID has so many problems that I wont even start. [msdn, IDENTITY](https://msdn.microsoft.com/en-us/library/ms186775(v=sql.110).aspx) — Patrick Artner, Dec 01 '17 at 07:26
Possible duplicate of [How to request a random row in SQL?](https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) — Patrick Artner, Dec 01 '17 at 07:44

score 1 · Answer 1 · answered Dec 01 '17 at 07:32

1

This answer is about not having your problem - not solving it in code (fix by better design)

CREATE TABLE fruitInStock
(
 id int IDENTITY(1,1),
 fruitname varchar (20), -- this should be a FK into a fruits table 
 amountOf int NOT NULL
  -- more fields
);    

INSERT fruitInStock
   (fruitname , amountOf )
VALUES
   ('Apple', 20),
   ('Pear', 10),
   ('Kiwi', 2000)    
;

SELECT * from fruitInStock

gives

id  fruitname   amountOf
1   Apple   20
2   Pear    10
3   Kiwi    2000

The ID is autogenerated on insert and wont ever duplicate - the database takes care of its own integrity and your code does not have to. Simply insert without ID.

answered Dec 01 '17 at 07:32

Patrick Artner

50,409
9
43
69

I have done that but the task given to me is use a random class in ID – Ndrangheta Dec 01 '17 at 07:38
@Ndrangheta If you need to get one random row from your table which is filled with data, use this SO answers suggestion: https://stackoverflow.com/a/19419/7505395 – Patrick Artner Dec 01 '17 at 07:42
@MethodMan 'INTO' can be omitted - and instead of trying to get one over and over one could simply get a random one with one try: https://stackoverflow.com/a/19419/7505395 – Patrick Artner Dec 01 '17 at 07:43
1

@PatrickArtner how does this link help.. Random Row and Random Number are not the same – MethodMan Dec 01 '17 at 07:44
the random class must be on the c# code and not in the sql query – Ndrangheta Dec 01 '17 at 07:48
@MethodMan He is computing a random number, is setting it to some Textbox-Input element, then he is checking if this number exists in his table, then he wants to get another random number and so forth. He is drawing random results from his table - the linked answer will allow him to do this: drawing one (or more if he changes the TOP clause) random rows from his data. – Patrick Artner Dec 01 '17 at 07:50
@Ndrangheta: Please revisit your questions and put **all sideconditions** into it. I am under the impression this is _again_ one of those "homework with unreasonable side condition" tasks ... – Patrick Artner Dec 01 '17 at 07:51

score 1 · Answer 2 · answered Dec 01 '17 at 07:43

1

You Try This

DECLARE @ID INT ,@New_ID INT
IF EXISTS(Select 1 from fruit_stock where identification=@id)
     SET @New_ID=MAX(identification)+1 FROM fruit_stock -- It Will give you Random Number

OR

DECLARE @ID INT ,@New_ID INT
IF EXISTS(Select 1 from fruit_stock where identification=@id)
     SET @New_ID=ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))-- It Will give you Random Number

answered Dec 01 '17 at 07:43

Alfaiz Ahmed

1,698
1
11
17

the first will give the next id for that table by calculating the max and adding , the second one a huge one based on a guid's int value. The second one is at least a bit rand-ish – Patrick Artner Dec 01 '17 at 07:57

score 0 · Answer 3 · answered Dec 01 '17 at 10:28

0

If you need to generate the identifier on the client side (can be necessary in distributed systems, when the access to the central database is not always reliably available), you could just use a Guid instead of an int as your primary identifier. Generate it locally with Guid.NewGuid(), and with Microsoft databases you can define the type of the column in the table as Guid.

answered Dec 01 '17 at 10:28

Bernhard Hiller

2,163
2
18
33

i like guid but its generating really really long output. I only want to generate 5 or 8 digits – Ndrangheta Dec 01 '17 at 14:23

Generating unique random number as identification in database

3 Answers3