[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2010-09-13
7
Medium Priority
?
320 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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 375 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

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…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

590 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