AVOID DUPLICATION IN DATABASE and PROGRAM!
-
hai, i m now working in ASP.NET with C# web application(.net 2003, 1.1 framework) and sql server 2000. i m having a sql table with two fields Roll_number and name. the roll number is auto generated by MY PROGRAM. when user fill the name and click OK button, the record is saved in database. this application is accessed by 1000's of users. Here the Roll_number is getting Replica. How to avoid this. i want to LOCK the roll_number which is generated by MY PROGRAM. if other users try to attempt, it should give the next number. The main aim of my process is to AVOID DUPLICATION in my database (since roll_number is primary key and i cant generate auto number in my database). Help me! - KARAN
-
hai, i m now working in ASP.NET with C# web application(.net 2003, 1.1 framework) and sql server 2000. i m having a sql table with two fields Roll_number and name. the roll number is auto generated by MY PROGRAM. when user fill the name and click OK button, the record is saved in database. this application is accessed by 1000's of users. Here the Roll_number is getting Replica. How to avoid this. i want to LOCK the roll_number which is generated by MY PROGRAM. if other users try to attempt, it should give the next number. The main aim of my process is to AVOID DUPLICATION in my database (since roll_number is primary key and i cant generate auto number in my database). Help me! - KARAN
Use a stored proc to get the highest roll number then add 1 SELECT max(roll_number) FROM table
only two letters away from being an asset
-
Use a stored proc to get the highest roll number then add 1 SELECT max(roll_number) FROM table
only two letters away from being an asset
Thanks for your reply Mark Nischalke, the same i performed.Even though when more than 1000 users accessing, the MAX value is getting repeated. (while 2 user attempt same time, the MAX value is supplied to them and when they updating REPLICA occuring)..... and also i like to handle this with my coding itself. Kindly help me.............
modified on Friday, March 14, 2008 9:35 AM
-
Thanks for your reply Mark Nischalke, the same i performed.Even though when more than 1000 users accessing, the MAX value is getting repeated. (while 2 user attempt same time, the MAX value is supplied to them and when they updating REPLICA occuring)..... and also i like to handle this with my coding itself. Kindly help me.............
modified on Friday, March 14, 2008 9:35 AM
Just curious, why can't you use autonumber? Assign the number when inserting the record using the same technique
only two letters away from being an asset
-
Just curious, why can't you use autonumber? Assign the number when inserting the record using the same technique
only two letters away from being an asset
Thanks for your reply. I cant generate AUTO ID since, i just want to show the ID in text box and this auto number is mixed with some characters (eg.. "ROLL100", "ROLL101", "ROLL102" etc...) so i programmatically generate the number starting from 100(and i store this 100 in separate database table(say.."A") and find the MAX and generate the next ROll number) and add the "ROLL" as prefix and display in the user text box. After user finish all other process he save this(so, in a separate database table(say.."B") the id=ROLL100,name=KARAN etc etc will be saved).So the "A" table is only used to generate the ID and the "B" table consist of all informations. This is my full process. And in both the "A" and "B" table, i m getting replication.how to proceed this and avoid replication.? Help me plz.... - KARAN
-
Thanks for your reply. I cant generate AUTO ID since, i just want to show the ID in text box and this auto number is mixed with some characters (eg.. "ROLL100", "ROLL101", "ROLL102" etc...) so i programmatically generate the number starting from 100(and i store this 100 in separate database table(say.."A") and find the MAX and generate the next ROll number) and add the "ROLL" as prefix and display in the user text box. After user finish all other process he save this(so, in a separate database table(say.."B") the id=ROLL100,name=KARAN etc etc will be saved).So the "A" table is only used to generate the ID and the "B" table consist of all informations. This is my full process. And in both the "A" and "B" table, i m getting replication.how to proceed this and avoid replication.? Help me plz.... - KARAN
I believe you are making it more difficult than it really is. Just because you want to append text to your ID doesn't preclude you from using an autonumber column. INSERT INTO A RETURN scope_identity() Or just eliminate table A, you can append whatever text to the ID after it is retrieved.
only two letters away from being an asset
-
I believe you are making it more difficult than it really is. Just because you want to append text to your ID doesn't preclude you from using an autonumber column. INSERT INTO A RETURN scope_identity() Or just eliminate table A, you can append whatever text to the ID after it is retrieved.
only two letters away from being an asset
Thanks my friend. whats the major problem is here they save the information year wise. (i.e, year 2007 consist of 100 records(from ROLL100 to ROLL200 AND the year 2008 consist of the same 100 records from ROLL100 to ROLL200). so i had my table as..... <u><b>number year</b></u> 100 2007 101 2007 100 2008 101 2008 and i will populate the year wise storing the exact storing mechanism of Roll number in master table is ("ROLL100/2007").Is there any process in which i want to LOCK this value "ROLL100" or locking the "100" numeric, so that no other should get this value and after the completion of this process, then only the next user should proceed. OR, values should placed in QUEUE status and checked... <i>some ideas like this... i like to do it programmatically plz.. </i>HELp me.. thanks in advance..... KARAN
modified on Friday, March 14, 2008 10:29 AM
-
Thanks my friend. whats the major problem is here they save the information year wise. (i.e, year 2007 consist of 100 records(from ROLL100 to ROLL200 AND the year 2008 consist of the same 100 records from ROLL100 to ROLL200). so i had my table as..... <u><b>number year</b></u> 100 2007 101 2007 100 2008 101 2008 and i will populate the year wise storing the exact storing mechanism of Roll number in master table is ("ROLL100/2007").Is there any process in which i want to LOCK this value "ROLL100" or locking the "100" numeric, so that no other should get this value and after the completion of this process, then only the next user should proceed. OR, values should placed in QUEUE status and checked... <i>some ideas like this... i like to do it programmatically plz.. </i>HELp me.. thanks in advance..... KARAN
modified on Friday, March 14, 2008 10:29 AM
That is the problem with some questions here, not giving all the information at first. First it was an autonumber problem, then adding text, now we have years and a set range. Use a singleton object to access the database and assign numbers. Unless there are still more requirements that you have yet devulge.
only two letters away from being an asset