By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

check the code urgent

P: 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
Share this Question
Share on Google+
5 Replies


kenobewan
Expert 2.5K+
P: 4,871
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
Expert 100+
P: 692
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
Expert 2.5K+
P: 4,871
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
Expert 100+
P: 692
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
Expert 2.5K+
P: 4,871
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.