473,326 Members | 2,133 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.

Checking for entry in database before insert

Hi,

I was wondering which is the best way of checking if a
record exist in the database before inserting.

There are quite a few ways I have seen. Here are some...

1> Insert and hit a primary key violation and then handle
it

2> Select of a dataset/datareader based on the new record
id. If the result has something in it, then don't insert

3> Change the sql statement to count(*) based on the new
record id. If count > 0 then don't insert

So, I was wondering which is the best way(most effecient
way) of doing it. or if there are better way.

Thanks

Joey

Nov 16 '05 #1
6 2708
The best way to handle the situation is optimistic approach, i.e.
" Insert and hit a primary key violation and then handle it"

This is so because :
1. If the record doesnt violate any property then it is inserted without
problems. In passimistic approach you would always check before this step
which is an overhead. Since RDBMS handle concurrency issues optimism is safe
and the best approach.

2. If insertion causes violation you would handle it regardless of the
approach taken. But optimism ignores searching for duplication.

HTH
Ashish

"Joey Lee" <an*******@discussions.microsoft.com> wrote in message
news:24****************************@phx.gbl...
Hi,

I was wondering which is the best way of checking if a
record exist in the database before inserting.

There are quite a few ways I have seen. Here are some...

1> Insert and hit a primary key violation and then handle
it

2> Select of a dataset/datareader based on the new record
id. If the result has something in it, then don't insert

3> Change the sql statement to count(*) based on the new
record id. If count > 0 then don't insert

So, I was wondering which is the best way(most effecient
way) of doing it. or if there are better way.

Thanks

Joey

Nov 16 '05 #2
In the real world I would do say a customer lookup first. The people
doing the data input would shoot me if the data was already there and
they spent all that time putting in the data only to find out someone
already did this.

Regards,
Jeff
I was wondering which is the best way of checking if a
record exist in the database before inserting.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #3
Joey,

In my opinion is the best approac first decide what methode you want to use.

The (very) classic one where you use the datareader, and the system.data
commands with sql string to update insert or to delete.

Or use the dataadapter approach with the dataset/datatable.

In my opinion is it just bad to mix those up.

Just my thought,

Cor
Nov 16 '05 #4
How is it that you know the primary key before you insert the record? Are
you using some part of the business information as the primary key?

If this is the case, then as soon as the user has entered the PK info, you
should look up the data and show it to the user. Let them know that the
record exists and that they can update it if they'd like.

Also: it is a good idea not to use business data as part of the primary key.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"Joey Lee" <an*******@discussions.microsoft.com> wrote in message
news:24****************************@phx.gbl...
Hi,

I was wondering which is the best way of checking if a
record exist in the database before inserting.

There are quite a few ways I have seen. Here are some...

1> Insert and hit a primary key violation and then handle
it

2> Select of a dataset/datareader based on the new record
id. If the result has something in it, then don't insert

3> Change the sql statement to count(*) based on the new
record id. If count > 0 then don't insert

So, I was wondering which is the best way(most effecient
way) of doing it. or if there are better way.

Thanks

Joey

Nov 16 '05 #5
Nick,

I advise you (and the OP) to use a Guid for the primary key.

4 times :-)

Cor
Nov 16 '05 #6
I very much agree, Cor. I prefer Guids when inserting records.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"Cor Ligthert" <no************@planet.nl> wrote in message
news:e0**************@TK2MSFTNGP14.phx.gbl...
Nick,

I advise you (and the OP) to use a Guid for the primary key.

4 times :-)

Cor

Nov 16 '05 #7

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

Similar topics

18
by: Domestos | last post by:
Hi all, How do I check in a mySQL table called 'Users' on column user_name when registering a new user_name to make sure the new 'user_name' does not already exist in that column? TIA
2
by: Aaron Reimann | last post by:
I have a lot of check boxes. This is an update of the check boxes, I want something was checked, then to do an insert (which is currently working), if something is no longer checked...delete the...
5
by: baur79 | last post by:
Hi guys i try to run this code in loop and to pass even the entry is duplicated def email_insert_in_db(email): sql="INSERT INTO emails (email) values ('%s') "%(email)...
2
by: mookid | last post by:
Hello, I am new to PHP so I have done a research on how to check if an entry exists on the table. I came up with the following code: include("dbinfo.inc.php"); $Name=$_POST; $Code=$_POST;...
10
by: Phil Latio | last post by:
I am inserting data into user table which contains 5 fields, sounds simple enough normally but 2 of the fields are designated as UNIQUE. If someone does enter a value which already exists, how do I...
1
by: Edwina Rothschild | last post by:
Hello, I am new to PHP so I have done a research on how to check if an entry exists on the table. I came up with the following code: include("dbinfo.inc.php"); $Name=$_POST; $Code=$_POST;...
3
by: dba | last post by:
with html and php using mysql. How do I catch error's from a database like "duplicate entry" and display something on the entry screen. Also how do you clear the data boxes on a screen. When I...
1
by: geetamadhavi | last post by:
Hi All, I have developed a php applciaiton where a new window is opening on checking the whether valid user orntot how to make that in same window after checking i have die(' not valid user ' ); i...
1
by: Orbie | last post by:
Hi All, I'm new to VB.NET and i'm looking for some help with my Windows Form. I need to check if a Commodity entered into (TextBox1.Text) already exists on my table before i insert it. I'm having...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.