RSS Forums RSS
Please support our ASP.NET advertiser: Lunarpages ASP Web Hosting
Views: 1498 | Replies: 6 | Thread Tools  Display Modes
Reply
Join Date: Nov 2007
Posts: 28
Reputation: u4umang2001 is an unknown quantity at this point 
Rep Power: 2
Solved Threads: 0
u4umang2001's Avatar
u4umang2001 u4umang2001 is offline Offline
Light Poster

Question exec command in stored procedure depends on condition

  #1  
Feb 6th, 2008
Hi

i want to insert the email into a table,
i pass the parameter to the stored procedure

if this email_id doesnt exists in the table
only then it should be inserted into the table...

how to do this using a stored procedure

please help
its urgent!!!
Thanks & Regards
Umang
AddThis Social Bookmark Button
Reply With Quote  
Join Date: Jan 2008
Location: Largo Florida
Posts: 448
Reputation: serkansendur is on a distinguished road 
Rep Power: 1
Solved Threads: 32
serkansendur's Avatar
serkansendur serkansendur is offline Offline
Posting Pro in Training

Re: exec command in stored procedure depends on condition

  #2  
Feb 6th, 2008
this t-sql below does what you want :

declare @email nvarchar(50)
set @email = 'test'
if not exists (select * from yourtable where email=@email )
insert into yourtable(email) values(@email )
Serkan Şendur
MCAD.NET
Reply With Quote  
Join Date: Nov 2007
Posts: 28
Reputation: u4umang2001 is an unknown quantity at this point 
Rep Power: 2
Solved Threads: 0
u4umang2001's Avatar
u4umang2001 u4umang2001 is offline Offline
Light Poster

Re: exec command in stored procedure depends on condition

  #3  
Feb 6th, 2008
thanks


but i want to display the message also if email exists

then wht can i do for that???
Thanks & Regards
Umang
Reply With Quote  
Join Date: Nov 2007
Posts: 28
Reputation: u4umang2001 is an unknown quantity at this point 
Rep Power: 2
Solved Threads: 0
u4umang2001's Avatar
u4umang2001 u4umang2001 is offline Offline
Light Poster

Re: exec command in stored procedure depends on condition

  #4  
Feb 6th, 2008
how can i show that message at my .aspx page???
Thanks & Regards
Umang
Reply With Quote  
Join Date: Jan 2008
Location: Largo Florida
Posts: 448
Reputation: serkansendur is on a distinguished road 
Rep Power: 1
Solved Threads: 32
serkansendur's Avatar
serkansendur serkansendur is offline Offline
Posting Pro in Training

Re: exec command in stored procedure depends on condition

  #5  
Feb 6th, 2008
execute the following query in ExecuteScalar mode, and display the return value to the user.

declare @email nvarchar(50)
set @email = 'test'
if not exists (select * from yourtable where email=@email )
begin
insert into yourtable(email) values(@email )
select 'inserted successfully'
end
else
begin
select 'the provided email already exists in our records'
end
Serkan Şendur
MCAD.NET
Reply With Quote  
Join Date: Nov 2007
Posts: 28
Reputation: u4umang2001 is an unknown quantity at this point 
Rep Power: 2
Solved Threads: 0
u4umang2001's Avatar
u4umang2001 u4umang2001 is offline Offline
Light Poster

Re: exec command in stored procedure depends on condition

  #6  
Feb 6th, 2008
no
its not working...
Thanks & Regards
Umang
Reply With Quote  
Join Date: Jan 2008
Location: Largo Florida
Posts: 448
Reputation: serkansendur is on a distinguished road 
Rep Power: 1
Solved Threads: 32
serkansendur's Avatar
serkansendur serkansendur is offline Offline
Posting Pro in Training

Re: exec command in stored procedure depends on condition

  #7  
Feb 6th, 2008
ok i will go step by step :
first create your stored procedure in sql server with the following :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE myInsertProcedure
@email nvarchar(50)
AS
BEGIN

SET NOCOUNT ON;

if not exists (select * from yourtable where email=@email )
begin
insert into yourtable(email) values(@email )
select 'inserted successfully'
end
else
begin
select 'the provided email already exists in our records'
end
END
GO

then go to your aspx page and create the markup below :
<asp: TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp: Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
lastly double click your button and type the following :
SqlConnection sqlCon = new SqlConnection("Data Source=ALPC28\\SERKANS2005;Initial Catalog=denemeler;Integrated Security=True;Pooling=False");
        SqlCommand sqlCom = new SqlCommand();
        sqlCom.CommandType = CommandType.StoredProcedure;
        sqlCom.CommandText = "myInsertProcedure";
        sqlCom.Connection = sqlCon;
        SqlParameter sqlPar = new SqlParameter("@email", SqlDbType.VarChar);
        sqlPar.Value = TextBox1.Text;
        sqlCom.Parameters.Add(sqlPar);
        string notification;
        sqlCon.Open();
        notification = sqlCom.ExecuteScalar().ToString();
        sqlCon.Close();
        Response.Write(notification);

i tried this creating a sample application, it works, just modify your connection string and adjust everything according to your webpage
Serkan Şendur
MCAD.NET
Reply With Quote  
Reply

Only community members can participate in forum threads. You must register or log in to contribute.



Other Threads in the ASP.NET Forum
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

Thread Tools Display Modes
Forums | Blogs | Tutorials | Code Snippets | Whitepapers | RSS Feeds | Advertising
All times are GMT -4. The time now is 6:49 pm.
Newsletter Archive - Sitemap - Privacy Statement - Acceptable Use Policy - Contact Us
Forum system based on vBulletin Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC