471,120 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,120 software developers and data experts.

check the code urgent

30
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
Mar 6 '08 #1
5 1580
kenobewan
4,871 Expert 4TB
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.
Mar 6 '08 #2
shweta123
692 Expert 512MB
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
Mar 6 '08 #3
kenobewan
4,871 Expert 4TB
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?
Mar 7 '08 #4
shweta123
692 Expert 512MB
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.
Mar 7 '08 #5
kenobewan
4,871 Expert 4TB
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.
Mar 8 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by N. Spiker | last post: by
8 posts views Thread by Allan Ebdrup | last post: by
11 posts views Thread by eSolTec, Inc. 501(c)(3) | last post: by
12 posts views Thread by Ryan | last post: by
15 posts views Thread by Brian Simmons | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.