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
6  2732 
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*******@disc ussions.microso ft.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
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!
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
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*******@disc ussions.microso ft.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
Nick,
I advise you (and the OP) to use a Guid for the primary key.
4 times :-)
Cor
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******** ******@TK2MSFTN GP14.phx.gbl... Nick,
I advise you (and the OP) to use a Guid for the primary key.
4 times :-)
Cor This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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
|
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 checkbox
join/link that is in the database.
So, the insert/checked is working, but the "unchecking" is not working.
I don't know how to compare an array of what was not checked.
Here is my code:
if (is_array($_POST)) {
|
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)
db=_mysql.connect(host = "localhost", user = db_user, passwd =
db_pass, db = db_name)
|
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;
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
|
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 capture this specific
error?
Would it make more sense to actually run a SELECT query first and if that
returned a result, then I use that for error checking and don't run insert
until select returns nothing?
Cheers
| | |
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;
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
|
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 use 'go back on
page' the data remains.
Thanks
|
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 even tried with echo also how to solve this the message should come under in the same window only see my code it is
program
----
<?php
// Connects to your Database
mysql_connect('localhost', 'root', 'epara') or die(mysql_error());
|
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 issues checking if the number of rows returned from my Select is equal 0? Also i'm wondering should i be checking for an exception and open/closing my connections each time i make a call to my DB or is the way i have it coded below OK??
Any input...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
