Click here to Skip to main content
15,860,859 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Actually I am developing program in c# windows application and I have to make a software where every customer have new unique id with unique departmentid.
here I am giving you the table design-
id cust_name age deprtmntname deptid
1----cust1-----25-----test1----------1
2----cust2-----20-----test2----------1
3 cust3------35----test1----------2
==========================================

the problem is whenever more than 10 users start putting entry then sometime unique id of departmentid is giving dublicate values. plz suggest me some option so that every user can get unique id along with unique departmentid. Might be there is some option from server then I am using SQL server 2008 r2.

thank you

What I have tried:

squery = "SELECT isnull(max(slno),0)+1 FROM OPDPatients with (holdlock) where slyear=@year";
cmd1 = new SqlCommand(squery, con);
cmd1.Parameters.AddWithValue("@year", LoginForm.dtyear.Year);
da = new SqlDataAdapter(cmd1);
dt = new DataTable();
da.Fill(dt);
Posted
Updated 3-Jan-17 1:54am
Comments
Philippe Mori 5-Jan-17 9:28am    
You should learn minimal database development first... If you don't even understand database ID generation, then you should not write code at this point.

People that try to write code without learning basic stuff like you do are creating bugged software because they have no idea of what they should do.

The problem is that you're getting the last used ID from the database and then trying to use it without it actually existing in the database.

As you're found, in a multi-user environment, you cannot do that as more than one machine can execute this query at the same time and get the same ID.

Your only solution is to have the database assign the value to the record for you. You MUST use an auto numbered field. You don't ask the database for the value. You just save a new record with at least the minimum amount of information to keep the data valid, WITHOUT SETTING THE ID VALUE, and the database engine will assign one for you. You then get the id value back from the database, it's IDENTITY value, and you've got the ID value. You'll be running TWO queries, an INSERT and a SELECT, in the same query.

You can find an example here[^]. For more, see Google[^].
 
Share this answer
 
Comments
jitendraintarvo 4-Jan-17 1:49am    
thanks for giving me best idea but these example gives only one id in return but I want two Id in return first for id column and second for deptid column. plz help me with that.
Dave Kreskowiak 4-Jan-17 11:05am    
You do NOT get back two ID's from a single query like this. You insert into one table at a time, get the ID for that record back, then put that ID into the dependent record and INSERT that next.
Don't do it like that.
Make the ID field an IDENTITY column in the DB, and let SQL handle making the values unique.
SQL AUTO INCREMENT a Field[^]
 
Share this answer
 
Apart from Solutions 1 and 2, if you have specific format for the id, you may want to explore Ask Your Database for that Unique ID[^].
BTW, I notice you put the "deprtmntname" in the user table, why? Aren't you duplicating the "deprtmntname" data every time you insert a new user. Rightfully, "deprtmntname" and "deptid" should be in one table say "dept" by itself. You should remove the "deprtmntname" from but leave the "deptid" in the user table, the "deptid" of the user table will link to the "deptid" of the dept table.
Learn more about 1NF, 2NF, 3NF and BCNF in Database Normalization[^]

+++++[round 2]+++++

Quote:
let me explain you again I have a table named customerdetails here I have to provide a unique id to every individual customer and one more unique id I have to provide to customer on basis of departmentname. Every user registered with registration no and also with departmentid like I have more than 20 department I am giving you the example like dept1, dept2, dept3, dept4.

To implement your requirement in relational database design, you have two entities here, the user and the dept, each should have one one table of its own like this:
user table:                                          dept table:
userid | deptid | username | userotherdetails        deptid | deptname |deptotherdea
           V                                            |
           |____________________________________________|

When a user registers, he is given a unique userid and his deptid value should be derived from a valid value from the dept table. To find the deptname of a user, you will link the deptid from the user table back to the dept table. In this way, the dept table acts like a lookup table.
Both the userid and deptid are primary keys for the respective tables.
If you do not understand my explanation, then clearly you have got to read up on Introduction to database design[^].
One thing to note is that database design is unlike excel spreadsheet. They are design principles to follows so as to minimize duplication, redundant data, data integrity, and to facilitate the manipulation of data subsequently.
 
Share this answer
 
v5
Comments
jitendraintarvo 4-Jan-17 1:52am    
thanks for giving me reply but without departmntname how will i get unique id for every deprtment. there are more than 20 diff-diff department. on basis of departmentname deptid should be generated. plz help with that.
thank
Peter Leow 4-Jan-17 2:12am    
"on basis of departmentname deptid should be generated", what basis? Any example?
jitendraintarvo 5-Jan-17 3:18am    
let me explain you again I have a table named customerdetails here I have to provide a unique id to every individual customer and one more unique id I have to provide to customer on basis of departmentname. Every user registered with registration no and also with departmentid like I have more than 20 department I am giving you the example like dept1, dept2, dept3, dept4.
Peter Leow 5-Jan-17 3:48am    
replied in my solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900