-
Oct 4th, 2013, 01:02 AM
#1
Thread Starter
Lively Member
How to make sure I insert unique values in a column when they are not zeroes?
I am using vb6 with sql 2000. I have a column 'r_num'. I have to make sure that only distinct values are allowed in the column except for zeroes.
There will be 3 kinds of insertions.
- User entering zeroes,
- User entering numbers (which will be distinct for sure) and
- Me generating using maximum value and inserting it.
But there will be multiple users at the same time. Therefore there is chance that duplicates will be coming while generating the values and inserting it. How can I avoid that situation? Thank you
-
Oct 4th, 2013, 06:29 AM
#2
Re: How to make sure I insert unique values in a column when they are not zeroes?
Several ideas come to mind...
Create another table that has just one column - an identity column - and have that be used to generate the unique-non-zero values you need. Then use that value on INSERT into your actual table.
Another idea - although I dislike triggers - is to maybe use a trigger...
Personally - I do something like this all over our financial applications. But I have the benefit of using stored procedures - so I can package all this logic into the CREATE SPROC.
Code:
RedoB:
Set @DataValue=(select DataValue from Control_T where FiscalYr=@FiscalYr and Item='LAST BATCH')
Set @NewDataValue=Right('000000'+Cast(Cast(@DataValue as int)+1 as VarChar(6)),6)
Update Control_T
Set DataValue=@NewDataValue,Tdate=@Tdate
Where FiscalYr=@FiscalYr and Item='LAST BATCH' and DataValue=@DataValue
If @@RowCount<>1 Goto RedoB
Set @BatchEntry=Cast(@NewDataValue as int)
Putting that DataValue=@DataValue part on the UPDATE WHERE clause allows for concurrency - so this is multi-user safe (and has about 15 years of user interaction to prove it!).
-
Oct 4th, 2013, 07:04 AM
#3
Re: How to make sure I insert unique values in a column when they are not zeroes?
I'm not 100% clear on the requirement. Are you asking how you can generate unique numbers in a multiuser set up? If so then you should be fine as long as you make the generation part if the insert statement so they're executed as a single atom. E.g.
Code:
Insert Into BlahTable (ID, Description)
Select Max(ID) + 1, 'Some Descriptive Text'
From BlahTable
(It was actually Szlamany who suggested this to to me years ago and I've lived by it ever since ) Because it's a single statement it get's exected as a single unit so no other inserts can intervleave. If you can't phrase it as a single statement for some reason you can wrap it in a transaction to achieve the same result.
Or are you asking how you ensure users don't enter the same number in there more than once. If that's the case you'd be best implementing a Constraint on the table. Google will give you plenty of info on how to do that.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Oct 5th, 2013, 07:33 AM
#4
Thread Starter
Lively Member
Re: How to make sure I insert unique values in a column when they are not zeroes?
szlamany, Will that identity column allow me to let users put their own number in that column? Because they will. But it will be unique. FunkyDexter,I cannot add constraint because there will be zeroes (only zeroes will repeat) in many rows. Otherwise it will should be unique numbers. That is like these : (0,0,1,3,5,0,8,10...)
So thEse 3 conditions happening will be : taking maximum when user don't know the number, user putting unique numbers, and putting zeroes when user does not want a value. My concern is when taking maximum, multiple users may be saving the same number. That should not happen. THANK YOU
-
Oct 5th, 2013, 08:09 AM
#5
Re: How to make sure I insert unique values in a column when they are not zeroes?
Are you using STORED PROCEDURES for any of this?
But - regardless - you can INSERT you OWN VALUES into an IDENTITY column by "turning" the identity "aspect" off for the connection requiring it.
If the column in that "control" table is marked as a primary key - then by default it will not allow duplicates - so that would take care of that requirement.
But really - a stored procedure would give you much, much more control over all this.
-
Oct 7th, 2013, 12:27 AM
#6
Thread Starter
Lively Member
Re: How to make sure I insert unique values in a column when they are not zeroes?
There is a problem with 'when to turn off the identity aspect' thing. I do not know if the user in going to put their own value or accepting the maximum value which I generated for them. Or there would be zeroes.
I am not using stored procedure currently. I am using recordset.update method. There will be 20+ parameters to pass to stored procedure.
-
Oct 7th, 2013, 04:45 AM
#7
Re: How to make sure I insert unique values in a column when they are not zeroes?
20 parameters is not a lot for a sproc - I have sprocs with that all the time.
Using a SPROC gives you ultimate-server-side control over this - where you can write IF/blocks and such to test for conditions and deal with the varying INSERT scenarios you are going to be throwing at it.
In a SPROC this would be written in 2 minutes...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|