How to detect an error from SQL Server in C#

Question

I'm using Silverlight and I'm trying to get the ID for my new user from my database.

This is how I'm doing it:

Random r = new Random();
int id = r.Next(1000,2000) * 10;

query = "SELECT MAX(ParticipantID) FROM Participant";
SqlCommand cmd2 = new SqlCommand(query, SqlCon);

try
{
    SqlCon.Open();
    id = (int)cmd2.ExecuteScalar();
}
catch { logger.Error("Add: sqlConnectionError"); }
finally { SqlCon.Close(); }

My problem is that sometimes I get the the id from the random math and not from my database.

The problem will appear in the SqlCon.Open(); or in id = (int)cmd2.ExecuteScalar(); line?

Is there a way that if it's not getting the id from the database it will try again?

Let's say try 100 time until the id is different then before?

How can I do it? I can not do it recursively.

Is there a way with a loop?

The problem is your whole approach. If you want an ID that is unique in the table/database, you should ask the database engine to provide one for you. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Feb 11, 2014 at 19:00
maybe it will work but i need to fix this code and not to write new one. — Dkova
– Dkova, Commented Feb 11, 2014 at 19:39

Sriram Sakthivel · Accepted Answer · 2014-02-11 19:04:06Z

1

i get the the "id" from the random math and not from my DB

It is because you're generating it randomly using this

int id = r.Next(1000,2000) * 10;

Why to initialize it with Random in first place? initialize it with 0.

int id = 0;
for (int i =0; i<100; i++)
{
     id = ...;//Get value from DB
    if(IsValidId(id))//validate the ID
    {
        break;
    }
}

However it sounds like you should be using Auto-Increment column; that's the better option if you're just looking for uniqueness.

answered Feb 11, 2014 at 19:04

Sriram Sakthivel

73.8k7 gold badges118 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dkova Over a year ago

i know that i generate it with math random, i cannot do it with 0 because i have user 0 i need all the time new users but from 0-infinity with no holes. i cannot change the DB it's not mine. but i think it will work what your are saying. Thx

Collectives™ on Stack Overflow

How to detect an error from SQL Server in C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related