Solved

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

Posted on 2010-09-13
7
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP.NET Calendar Control 5 52
Stop Git from being my repository 1 48
Better way to filter date  - Query 5 45
Display info from DB to Label in asp.net 7 44
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 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