[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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

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
centem
Asked:
centem
1 Solution
 
Daniel WilsonCommented:
1.  You want an IF NOT EXISTS ....
2.  We need to see your Button_Click event handler code.
0
 
JoeNuvoCommented:
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
 
Vipul Patel.NET ExpertCommented:
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
centemAuthor Commented:
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
 
JoeNuvoCommented:
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
 
JoeNuvoCommented:
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
 
centemAuthor Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now