Restrict Insert if record exsists

Can someone point me in the right direction on this? There are 3
fields in the DB: model, rma, bagqty. If the values on a certain row
are I530, 1108, 50 and a user goes to input the same data I dont want
the insert to be allowed and force a new insert.

I dont need someone to do this for nor do I want that, just need a push
in the right direction.

Thanks,

Danny

Re: Restrict Insert if record exsists

On Jan 4, 6:33 am, "Mangler" wrote:
> Can someone point me in the right direction on this? There are 3
> fields in the DB: model, rma, bagqty. If the values on a certain row
> are I530, 1108, 50 and a user goes to input the same data I dont want
> the insert to be allowed and force a new insert.

Is there anything wrong with using a unique key in the database and
then catching the resulting exception in your code?

Cheers
Chad

Re: Restrict Insert if record exsists

Chad.Burggraf@gmail.com wrote:

> Is there anything wrong with using a unique key in the database and
> then catching the resulting exception in your code?
>
> Cheers
> Chad


It wonr work in this situation. Say for example,there is RMA 1108,
model LG225, part A, bagqty 50. If the user needs to insert a line
with all of the same info but the bagqty is 45 the insert will be
allowed because the billing for those bags will be different. The only
time it will be restricted is if the user tries to insert the exact
same information twice. I dont see how a unique key can help this
one...

Re: Restrict Insert if record exsists

"Mangler" wrote in message
news:1167917609.111056.6890@51g2000cwl.googlegroups.com...
> Can someone point me in the right direction on this? There are 3
> fields in the DB: model, rma, bagqty. If the values on a certain row
> are I530, 1108, 50 and a user goes to input the same data I dont want
> the insert to be allowed and force a new insert.
>
> I dont need someone to do this for nor do I want that, just need a push
> in the right direction.
>

Could you clarify what you want? What does "I dont want the insert to be
allowed and force a new insert." actually mean? It seems to me to be
contradictory.

--
Mike Brind

Re: Restrict Insert if record exsists

Mangler wrote:
> Chad.Burggraf@gmail.com wrote:
>
>> Is there anything wrong with using a unique key in the database and
>> then catching the resulting exception in your code?
>>
>> Cheers
>> Chad
>
>
> It wonr work in this situation. Say for example,there is RMA 1108,
> model LG225, part A, bagqty 50. If the user needs to insert a line
> with all of the same info but the bagqty is 45 the insert will be
> allowed because the billing for those bags will be different. The
> only time it will be restricted is if the user tries to insert the
> exact same information twice. I dont see how a unique key can help
> this one...

Why not? Include all the fields that make a record unique in the unique
key ... even if it means including all the fields. "unique key" does not
have to mean "unique single-field key". If you need specifics as to how
to do this, let us know what database type and version you are using.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Restrict Insert if record exsists

Bob Barrows [MVP] wrote:

> Why not? Include all the fields that make a record unique in the unique
> key ... even if it means including all the fields. "unique key" does not
> have to mean "unique single-field key". If you need specifics as to how
> to do this, let us know what database type and version you are using.


SQL 2000 - 8.0.760

I appreciate your help and patience on this.

Re: Restrict Insert if record exsists

Mangler wrote:
> Bob Barrows [MVP] wrote:
>
>> Why not? Include all the fields that make a record unique in the
>> unique key ... even if it means including all the fields. "unique
>> key" does not have to mean "unique single-field key". If you need
>> specifics as to how to do this, let us know what database type and
>> version you are using.
>
>
> SQL 2000 - 8.0.760
>
> I appreciate your help and patience on this.
You have two choices: a key (constraint) or a unique index. Look up both
in BOL to see the differences.

You add a constraint to a table by using an ALTER TABLE statement. Here
is the syntax, form BOL:
ALTER TABLE [database.[owner.]]table_name
[WITH {CHECK | NOCHECK}]
{{CHECK | NOCHECK} CONSTRAINT {constraint_name | ALL}
|
[ADD
{col_name column_properties [column_constraints]
| [[, ] table_constraint]}
[, {next_col_name | next_table_constraint}]...]
|
[DROP CONSTRAINT]
constraint_name [, constraint_name2]...]}

Here is an example of a statement to create a unique constraint:
ALTER TABLE dbo.ProductionConstants ADD UNIQUE NONCLUSTERED
(CompanyNumber,DivisionNumber,FiscalYear,FiscalMonth)
ON [PRIMARY]


Here is the basic syntax for the CREATE INDEX statement, from BOL:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [[database.]owner.]table_name (column_name [, column_name]...)
[WITH
[PAD_INDEX, ]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] SORTED_DATA | SORTED_DATA_REORG]
[[,] IGNORE_DUP_ROW | ALLOW_DUP_ROW]]
[ON segment_name]

Here is an example:

CREATE UNIQUE CLUSTERED INDEX IX_ProductionConstants_CompDivFisc
ON dbo.ProductionConstants(DivisionNumber,FiscalYear,FiscalMont h)
WITH FILLFACTOR = 90
ON [PRIMARY]

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Restrict Insert if record exsists

Thanks for the help. Time to research!!!

Re: Restrict Insert if record exsists

Thanks for the help. I will look into those things...