473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

Jan 4 '07 #1
8 1556
On Jan 4, 6:33 am, "Mangler" <dwald...@directwireless.comwrote:
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

Jan 4 '07 #2

Ch***********@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...

Jan 5 '07 #3

"Mangler" <dw******@directwireless.comwrote in message
news:11********************@51g2000cwl.googlegroup s.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
Jan 5 '07 #4
Mangler wrote:
Ch***********@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.
Jan 5 '07 #5

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.

Jan 5 '07 #6
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,FiscalMon th)
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, FiscalMonth)
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.
Jan 5 '07 #7
Thanks for the help. Time to research!!!

Jan 5 '07 #8
Thanks for the help. I will look into those things...

Jan 5 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: hristov.milen | last post by:
Hi, Is there a way to restrict selecting from a table.. I have problem with one check, I have select and after this insert based on the select. If it happen in one time .. I can insert...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
1
by: Harry | last post by:
Hi, just getting started on ASP.NET and C# and MS-SQL. Ive managed to get all the connections working and database intergrated nicely for content and stuff. I now want to include a user...
3
by: Shapper | last post by:
Hello, I have created 3 functions to insert, update and delete an Access database record. The Insert and the Delete code are working fine. The update is not. I checked and my database has all...
8
by: shorti | last post by:
Here is an example of what I want to do (syntax might not be entirely correct as this is just an example): CREATE TABLE ParentA ( name CHAR (6) NOT NULL; address CHAR(64); ) IN CUSTOMER_TS...
10
by: sara | last post by:
Hi - I am developing a simple app, and just found a problem that I can't fix after 4 hours of trying. I display a list of customers, and the user chooses one and displays "orders". The...
2
by: Vinda | last post by:
I have recently upgraded to Access 2002 and when I run the macro -> run command -> spelling on a form it checks all records rather than just the current record. When you have over 30,000 records...
6
by: Frank Swarbrick | last post by:
I feel like an idiot for asking this, but neither I nor my DBA can figure this out. How do you create a database object that does not implicitly allow access by any defined user? For instance,...
1
by: chanshaw | last post by:
Hey I'm trying to see if a file exsists but I'm using windows, I know in Linux you can use -e but that doesn't seem to work for me in windows. if (-e $filename) { print "File Doesn't...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.