Hi,

i would like to check the email which is already exist in my db.

i don't want to allow the user to register with same email multiple.

this is my asp code and SP

SP :1

 SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_l4i_users

@useremail as varchar(50)

AS

BEGIN
declare @cnt aS INT

SET @CNT=(select count(*)[cnt] from l4i_users where user_email=@useremail)

IF( @CNT=0)

PRINT('VALID EMAIL ID')

ELSE

PRINT('INVALID EMAIL ID')

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SP 2:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CReate Proc sp_l4i_users_insert
@userName varchar(50),
@useremail varchar(50),
@userpassword varchar(50),
@useraddress varchar(400),
@usercity int,
@userstate int,
@userphone varchar(20),
@userid varchar(50)output
as
begin

    if exists(Select 1 from [l4i_users] where [user_email]=@useremail)
        begin
            return 0 --Email Already exists
        end
    else
        begin
        insert into l4i_users (user_name,user_email,user_password,user_address,user_city,user_state,user_phone)values(@username,@useremail,@userpassword,@useraddress,@usercity,@userstate,@userphone)--Do insert Here

                      return 1
        end
end




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 ASPX.CS:

protected void Button1_Click1(object sender, EventArgs e)
    {

        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Learn4ICETConnectionString"].ConnectionString;

        con.Open();
       
        SqlCommand cmd = new SqlCommand();
        SqlCommand cmd1 = new SqlCommand();
   
        //checking e-mail address
        cmd = new SqlCommand("sp_l4i_users", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@useremail", SqlDbType.VarChar, 200);
        cmd.Parameters["@useremail"].Value = TextBox2.Text;        
                     
        //insert
        cmd1 = new SqlCommand("sp_l4i_users_insert", con);
        cmd1.CommandType = CommandType.StoredProcedure;

        cmd1.Parameters.Add("@username", SqlDbType.VarChar, 50);
        cmd1.Parameters["@username"].Value = TextBox1.Text;
        cmd1.Parameters.Add("@useremail", SqlDbType.VarChar, 50);
        cmd1.Parameters["@useremail"].Value = TextBox2.Text;
        cmd1.Parameters.Add("@userpassword", SqlDbType.VarChar, 50);
        cmd1.Parameters["@userpassword"].Value = TextBox4.Text;
        cmd1.Parameters.Add("@useraddress", SqlDbType.VarChar, 400);
        cmd1.Parameters["@useraddress"].Value = TextBox5.Text;
        cmd1.Parameters.Add("@usercity", SqlDbType.Int);
        cmd1.Parameters["@usercity"].Value = DropDownList1.SelectedValue;
        cmd1.Parameters.Add("@userstate", SqlDbType.Int);
        cmd1.Parameters["@userstate"].Value = DropDownList2.SelectedValue;
        cmd1.Parameters.Add("@userphone", SqlDbType.VarChar, 20);
        cmd1.Parameters["@userphone"].Value = TextBox8.Text;
        cmd1.Parameters.Add("@Userid", SqlDbType.VarChar,50);
        cmd1.Parameters["@Userid"].Direction = ParameterDirection.Output;
      
        try
        {
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                lb_message.Visible = true;
                lb_message.Text = "Email which you had entered is already exist in our database";

            }

            else
            {
            try
            {
                //Regestering the user and sending email to registered user
                dr.Close();
                cmd1.ExecuteScalar();
                SmtpClient smtp = new SmtpClient("mail.learn4icet.com");
                smtp.UseDefaultCredentials = false;
                smtp.Credentials = new System.Net.NetworkCredential("contactus@learn4icet.com", "learn4icet");
                string vmsgbody = "Dear " + TextBox1.Text + ", <br><br> Welcome to Learn4icet!<br><br> We are very pleased to have you as our customer. <br>You are registered at Learn4icet as"
                + "<br><br>Username: " + TextBox2.Text + "<br>" + "Password: " + TextBox4.Text + "<br><br>Happy learning.<br> <br>Thank you, <br>Learn4icet team";
                MailAddress from = new MailAddress("contactus@learn4icet.com");
                MailAddress To = new MailAddress(TextBox2.Text);
                MailMessage msg = new MailMessage(from, To);
                msg.Subject = "Welcome to Learn4icet";
                msg.IsBodyHtml = true;
                msg.Body = vmsgbody;
                smtp.Send(msg);
                lb_message.Text = "Registered Successfully";
                Panel1.Visible = false;
            }


            catch (Exception smtpexp)
            {
                lb_message.Text = "Error sending email:" + smtpexp.ToString();
            }

            finally
            {
                dr.Close();
            }
        }

        }

        catch (Exception myexp)
        {
            lb_message.Text = "Unexpected error occured, please exit.<br><br>" + myexp.ToString();
        }
        finally
        {
            con.Close();
        }


    }     

we can do by single stored procedure but

i want 2 procedure which i declated above in programme.

after executing above code programe is running successfully

But it is allowing user to insert , i mean allowing user to register with same email multiple times.

i want to restrict that.what's the wrong in SP1

Kindly speciy the procedure

Your first procedure does nothing useful, since it doesn't return anything. The second procedure seems to be OK and I believe it should only insert new users. So, for now I would drop the whole check first logic from your page and try direct insert.

 hi noam thanks for the reply.

I would like to have two procedures.

proc 1: for checking if email is exist

and 

proc 2:

write insert statement

see my aspx.cs code i had specified 2 procedures in code.like that i want 2 proc...

I think that you are going the long way around the bend with this one. You can instead place a simple unique constraint on the email column. Then it would just be a matter of handling the error that is returned to the application from the db if you try to insert a duplicate email.

Hope this helps,
AJ

Hello,

I dont think you need 2 SPs for this purpose. 1st SP is just used for testing in sql itself. In 2nd SP, you have created @userid as output parameter but you are not using it in c# code.

Make changes to the SP as below.

CReate Proc sp_l4i_users_insert
@userName varchar(50),
@useremail varchar(50),
@userpassword varchar(50),
@useraddress varchar(400),
@usercity int,
@userstate int,
@userphone varchar(20),
@userid varchar(50)output
as
begin

    if exists(Select 1 from [l4i_users] where [user_email]=@useremail)
        begin
            set @userid=0 --Email Already exists
        end
    else
        begin
        insert into l4i_users (user_name,user_email,user_password,user_address,user_city,user_state,user_phone)values(@username,@useremail,@userpassword,@useraddress,@usercity,@userstate,@userphone)--Do insert Here

                      set @userid = SCOPE_IDENTITY()
        end
end

And take the @userid output parameter in your code. if its 0 then user already exists otherwise it will hold the value of newly created user.

in sp1 u didnt return any value to check in the front end

just u have used print alone,so datareader condition  always fails & try to execute else part 

in else part too u have made same check so it wont write to the database but u didnt handle the return values 0 or 1 in front end

pls specify the procedure

I want to check the email exist or not?