| Author |
Topic |
|
iamsmahd
Yak Posting Veteran
55 Posts |
 Posted - 2005-10-10 : 09:26:34
|
| hi there....I have a form which is connected to a table in sql server 2000 engine.the table (t1) has a column (col1) and is NOT as an identity column.When a user tries to insert a new record, the form (form1) shows the last value of col1 (it calculates the maximum of col1 )+1 as its value of field (field1)users say that every time they wanna to save their changes, two users see the equal value of field1 and so when 2 users simoltanously insert their records one of them is lost.Is it good way, changing the property of col1 as an identity column or not? If no, what is your solutions?Thank you very muchsonia |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-10 : 09:29:22
|
| If it is incremental column then use Idnetity columnOtherwise you need to query it again to get the maximum value + 1 just before the Insert statementMadhivananFailing to plan is Planning to fail |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-10-10 : 09:34:04
|
| Hi,what we do in our application is that we maitain the different table for the generating the autogenerated number .. so when any user comes in addnew mode we make the new get the new id to that user and when another user comes in the same mode the new id is agin assign to it .. so when the user insert the record it always the unquie id and till now we havent face and any concurrency problem though Max 1000 user connected @ time to our application.. if you planning to use sumthing like this then.. i will post the Table scripts.. and explanation too.Complicated things can be done by simple thinking |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-10 : 18:49:36
|
| As Madhivanan pointed out, if you want your key to be an incrementing integer, then there is no need to maintain a separate table of keys. Let SQL Server do it for you with the IDENTITY feature. Why bother writing your own routine to do this? Only justification I can think of is that you are not using a pure integer, but rather some combination number and code.---------------------------EmeraldCityDomains.com |
|
|
|
iamsmahd
Yak Posting Veteran
55 Posts |
Posted - 2005-10-11 : 01:58:16
|
| friends,Do you mean that when one user (u1) opens form1 for inserting a new record, and the col1 is as identity, sql server gives a value of col1 to u1 (v1) and when the other user either opens form1 to insert a new record, this time sql server give him a value greater than v1 even if u1 has not inserted his new record?Is this correct?sonia |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-10-11 : 02:35:00
|
| The identity should be integer field so when for the first record it will give 1 or the value which u have specified and then other user or the same user when insert the record it will increament the value by the seed you have specified while creating the identity column.Complicated things can be done by simple thinking |
|
|
|
HCLollo
Starting Member
49 Posts |
Posted - 2005-10-11 : 04:58:55
|
| Sonia,what SQL Server do is basically increment the value of the identitycolumn by the 'seed' value you assigned (usually 1), as chiragkhabariapointed out, when the data is inserted into the table. If you needto assign a number based on the order the user accessed the form, thiscould be a little tricker, involving keeping track of the actualtime of view, or something like that...HCL"If it works fine, then it doesn't have enough features" |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-10-11 : 13:06:57
|
| And if you're concerned about finding out what the number was that SQL Server assigned, you can SELECT SCOPE_IDENTITY() using the same connection to retrieve the last assigned number.---------------------------EmeraldCityDomains.com |
|
|
|
|
resolving concurrency problem on "INSERT"