check the code urgent

[gopim]

strSql = "SELECT ISNULL(max(substring(User_ID,2,len(User_ID))) + 1,'100') FROM Users";
sqlCmd.CommandText = strSql;
sqlDr = sqlCmd.ExecuteReader();
sqlDr.Read();
UserID = "U" + sqlDr.GetValue(0);

after this i write insert query.

in that insert query my user_Id value will be like this U100,U101... upto U1000. upto this we don't have any problems. after U1000 no one can't insert any record i got error

Violation of PRIMARY KEY constraint 'PK__Users__656C112C'. Cannot insert duplicate key in object 'Users'. The statement has been terminated.

this problem is due to above select query generating User_ID. it can't generate U1001 ... values
could any one please give me any suggessions very urgent

[kenobewan]

My friend, the whole point of making id a primary field is to ensure that they are protected. Depending on which db you are using there are methods for auto incrementing the id so that this situation never arises. Redesign your db or prepare for alot of pain in the future.

[shweta123]

Hi,

Are you using any loop for getting the UserID and inserting UserID value into the table? Because of which you are getting the error for insertion. Can you post more details about your code?

strSql = "SELECT ISNULL(max(substring(User_ID,2,len(User_ID))) + 1,'100') FROM Users";
sqlCmd.CommandText = strSql;
sqlDr = sqlCmd.ExecuteReader();
sqlDr.Read();
UserID = "U" + sqlDr.GetValue(0);

after this i write insert query.

in that insert query my user_Id value will be like this U100,U101... upto U1000. upto this we don't have any problems. after U1000 no one can't insert any record i got error

Violation of PRIMARY KEY constraint 'PK__Users__656C112C'. Cannot insert duplicate key in object 'Users'. The statement has been terminated.

this problem is due to above select query generating User_ID. it can't generate U1001 ... values
could any one please give me any suggessions very urgent

[kenobewan]

Hi,

Are you using any loop for getting the UserID and inserting UserID value into the table? Because of which you are getting the error for insertion. Can you post more details about your code?

You actually support this practice? Isn't there a danger of ending up with non unique id numbers? I experienced this pain once when I joined a company. What is the design of the table?

[shweta123]

Hi,

You should generate the UserIds from the code only if :

1) You do not want only numeric values in the ID. i.e.
You want to take character(e.g. "U") in the beginning of the userid.
2) You want to start generate IDs from perticulat digit.(e.g. 100)
3) You do not want the ID values which are sequential.
4) You can get more no of unique ID combinations if you make ID using letters and numbers.

[kenobewan]

Hi,

You should generate the UserIds from the code only if :

1) You do not want only numeric values in the ID. i.e.
You want to take character(e.g. "U") in the beginning of the userid.
2) You want to start generate IDs from perticulat digit.(e.g. 100)
3) You do not want the ID values which are sequential.
4) You can get more no of unique ID combinations if you make ID using letters and numbers.

At the risk of appearing argumentative:
1) Not sure when this would be desirable, why would you want this?
2) I created a default of 3000 once, in sql it allowed me to set the initial number.
3) An auto increment could also be set >1.
4) True and a case can be made for using secure id's that are alphanumerical, but again doing this in the application is risky.

Guess my point was generate the id in the database, even if it is by a stored procedure, rather than the application.