Solved

How do you return error message if Row exists in SQL Server DB

Posted on 2010-09-13
7
310 Views
Last Modified: 2012-05-10
Greetings,
Below is my SP. I'm calling it through a button click event on a ASP C# form.  How do I return the "Message" portion to an ASP label. Many thanks any that can help.

CREATE PROCEDURE insertName  
@id int,  
@fname varchar(50),  
@lname varchar(50)  
AS  
 
 
IF EXISTS(SELECT fn,ln FROM names WHERE fn=@fname AND ln=@lname)    
BEGIN    
INSERT INTO names(fn,ln) VALUES(@fname,@lname)    
Select 'Data  Inserted.'Message
END  
ELSE  
BEGIN  
  select'Already exists.'Message
END

I'm trying to find a way to edit the text property of a label so that if inserting an already existing data the error is displayed in the label.
0
Comment
Question by:centem
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 33667160
1.  You want an IF NOT EXISTS ....
2.  We need to see your Button_Click event handler code.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33668473
it's correct the you uses "EXISTS"

but you just write it wrong

your current code is

IF (data already exists)
     try to insert it
ELSE
     try to edit it

not make sense, is it?
so you need to fix it by either put NOT keyword as above comment.
or switch the location of edit and insert
0
 
LVL 5

Expert Comment

by:Vipul Patel
ID: 33670871
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:centem
ID: 33674518
OK.  I corrected the order like so

IF EXISTS (SELECT * FROM names WHERE fn='@fname' AND ln='@lname')
SELECT 'Already Exists' Message
ELSE
INSERT INTO names(fn,ln) VALUES(@fname,@lname)
SELECT 'Row Added' Message

However, the 'Already Added' message never appears. Even after purposefully adding duplicates I get the 'Row Added' message. Why?
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33678019
you must remove '
it cause variable to become text instead

IF EXISTS (SELECT * FROM names WHERE fn='@fname' AND ln='@lname')

correct one is

IF EXISTS (SELECT * FROM names WHERE fn=@fname AND ln=@lname)
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 125 total points
ID: 33678026
beside
for command under your "ELSE"
you got more than 1 command
which require to make a block by BEGIN/END


IF EXISTS (SELECT * FROM names WHERE fn=@fname AND ln=@lname)
SELECT 'Already Exists' Message
ELSE
BEGIN
INSERT INTO names(fn,ln) VALUES(@fname,@lname)
SELECT 'Row Added' Message
END
0
 

Author Closing Comment

by:centem
ID: 33797156
I put partially complete because I wanted to know how to return this to my ASP web app. The answer only addressed the sql portion. But, the sql portion was accurate. Thanks.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now