Solved

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

Posted on 2010-09-13
7
311 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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