Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Check if email exists

Status
Not open for further replies.
primagic

primagic

IS-IT--Management
Jul 24, 2008
476
GB
I am using the following stored procedure to check if email exists before adding the record.

Code: 
USE [quickbabysit]
GO
/****** Object:  StoredProcedure [dbo].[AddNewBabysitterRegistration]    Script Date: 05/12/2012 13:11:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[AddNewBabysitterRegistration]
(
@BabysitterID int OUTPUT,
@Firstname varchar(150),
@Surname varchar(150),
@EmailAddress nvarchar(max),
@Password nchar(8),
@AddressLine1 varchar(150),
@AddressLine2 varchar(150),
@AddressLine3 varchar(150),
@AddressLine4 varchar(150),
@Town varchar(150),
@Postcode varchar(20),
@DateofBirth datetime,
@Gender varchar(10),
@ContactPhone varchar(20),
@Availability varchar(50),
@Visa varchar(4),
@CRB varchar(4)
)
AS
BEGIN

IF NOT EXISTS(SELECT * 
                  FROM   dbo.Babysitters
                  WHERE  ( @EmailAddress = dbo.Babysitters.EmailAddress
                         )
)  
BEGIN
  INSERT INTO dbo.Babysitters
  (Firstname
  ,Surname
  , EmailAddress
  , Password
  , AddressLine1
  , AddressLine2
  , AddressLine3
  , AddressLine4
  , Town
  , Postcode
  , DateofBirth
  , Gender
  , ContactPhone
  , Availability
  , Visa
  , CRB  )
           
     VALUES 
     (@Firstname,
      @Surname, 
      @EmailAddress, 
      @Password, 
      @AddressLine1, 
      @AddressLine2, 
      @AddressLine3, 
      @AddressLine4, 
      @Town, 
      @Postcode, 
      @DateofBirth, 
      @Gender, 
      @ContactPhone, 
      @Availability, 
      @Visa, 
      @CRB) 

END
END

My codebehind is:

Code: 
Protected Sub btnRegister_Click(sender As Object, e As System.Web.UI.ImageClickEventArgs) Handles btnRegister.Click
        If Me.chkTerms.Checked = True Then
            'Add record here
            Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("quickbabysitConnectionString").ConnectionString
            Dim con As New SqlConnection(ConnString)
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "AddNewBabysitterRegistration"
            cmd.Parameters.Add("@Firstname", SqlDbType.VarChar).Value = txtFirstname.Text.Trim()
            cmd.Parameters.Add("@Surname", SqlDbType.VarChar).Value = txtSurname.Text.Trim()
            cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar).Value = txtEmail.Text.Trim()
            cmd.Parameters.Add("@Password", SqlDbType.NChar).Value = txtPassword.Text.Trim()
            cmd.Parameters.Add("@AddressLine1", SqlDbType.VarChar).Value = txtAddress.Text.Trim()
            cmd.Parameters.Add("@AddressLine2", SqlDbType.VarChar).Value = txtAddress2.Text.Trim()
            cmd.Parameters.Add("@AddressLine3", SqlDbType.VarChar).Value = txtAddress3.Text.Trim()
            cmd.Parameters.Add("@AddressLine4", SqlDbType.VarChar).Value = txtAddress4.Text.Trim()
            cmd.Parameters.Add("@Town", SqlDbType.VarChar).Value = txtCity.Text.Trim()
            cmd.Parameters.Add("@Postcode", SqlDbType.VarChar).Value = txtPostcode.Text.Trim()
            cmd.Parameters.Add("@DateofBirth", SqlDbType.DateTime).Value = txtDateofBirth.Text.Trim()
            cmd.Parameters.Add("@Gender", SqlDbType.VarChar).Value = ddlGender.SelectedItem.Text.Trim()
            cmd.Parameters.Add("@ContactPhone", SqlDbType.VarChar).Value = txtPhone.Text.Trim()
            cmd.Parameters.Add("@Availability", SqlDbType.VarChar).Value = rblAvailability.SelectedValue.Trim()
            cmd.Parameters.Add("@Visa", SqlDbType.VarChar).Value = rblVisa.SelectedValue.Trim()
            cmd.Parameters.Add("@CRB", SqlDbType.VarChar).Value = rblCRB.SelectedValue.Trim()

            Session("UserID") = txtEmail.Text
            Session("UserType") = "Babysitter"

            Dim BabysitterID As New SqlParameter("@BabysitterID", SqlDbType.Int)
            BabysitterID.Direction = ParameterDirection.Output
            cmd.Parameters.Add(BabysitterID)

            cmd.Connection = con


            Try
                con.Open()
                cmd.ExecuteScalar()


            Catch ex As Exception
                Throw ex
            Finally
                con.Close()
                con.Dispose()
            End Try

            Dim MyReg As String = Convert.ToString(BabysitterID.Value)
            Me.txtRegID.Text = MyReg


            GetFile()
            UpdateCVStatus()
            send_emailSitter()
            pnlRegSitter.Visible = False
            pnlSuccess.Visible = True
            FormsAuthentication.RedirectFromLoginPage(txtEmail.Text, False)
            'Response.AddHeader("Refresh", "4;URL=babysitters/home.aspx")
            Response.Redirect("babysitters/home.aspx")


        Else
            Me.lblTerms.Visible = True
        End If
    End Sub

Whenever I register trying to register someone with an email address that already exists, it doesnt add the record but it seems to then redirect them as if they have logged in.

How can I display a message in a label somewhere that says 'Email address already exists'

 
Sort by date Sort by votes

No where in your stored procedure are you setting the @BabySitter_ID value to indicate either success or failure for the insert. You should either set @Babysitter_ID to -1 as a failure flag (email exists) or the ID of the last inserted record (see ScopeIdentity()).


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
 
Upvote 0 Downvote
This was my original SP before I modified it.

What do I need to change to check for emailaddress firs, if exists stop insertion and display message.

Code: 
USE [quickbabysit]
GO
/****** Object:  StoredProcedure [dbo].[AddNewBabysitterRegistrationx]    Script Date: 05/12/2012 18:02:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddNewBabysitterRegistrationx]
(
@BabysitterID int OUTPUT,
@Firstname varchar(150),
@Surname varchar(150),
@EmailAddress nvarchar(max),
@Password nchar(8),
@AddressLine1 varchar(150),
@AddressLine2 varchar(150),
@AddressLine3 varchar(150),
@AddressLine4 varchar(150),
@Town varchar(150),
@Postcode varchar(20),
@DateofBirth datetime,
@Gender varchar(10),
@ContactPhone varchar(20),
@Availability varchar(50),
@Visa varchar(4),
@CRB varchar(4)


)
AS
BEGIN

SET NOCOUNT ON;


INSERT INTO dbo.Babysitters(Firstname, Surname, EmailAddress, Password, AddressLine1, AddressLine2, AddressLine3, AddressLine4, Town, Postcode, DateofBirth, Gender, ContactPhone, Availability, Visa, CRB  )
VALUES (@Firstname, @Surname, @EmailAddress, @Password, @AddressLine1, @AddressLine2, @AddressLine3, @AddressLine4, @Town, @Postcode, @DateofBirth, @Gender, @ContactPhone, @Availability, @Visa, @CRB)

SELECT SCOPE_IDENTITY()
SET @BabysitterID = SCOPE_IDENTITY()



END
 
Upvote 0 Downvote


Not tested, but something like this:

Code: 
SET NOCOUNT ON; 

IF  not exists (select * from dbo.Babysitters where emailAddress = @EmailAddress) 
BEGIN
	INSERT INTO dbo.Babysitters(Firstname, Surname, EmailAddress, Password, AddressLine1, AddressLine2, AddressLine3, AddressLine4, Town, Postcode, DateofBirth, Gender, ContactPhone, Availability, Visa, CRB ) 
	VALUES (@Firstname, @Surname, @EmailAddress, @Password, @AddressLine1, @AddressLine2, @AddressLine3, @AddressLine4, @Town, @Postcode, @DateofBirth, @Gender, @ContactPhone, @Availability, @Visa, @CRB) 

	SET @BabysitterID = SCOPE_IDENTITY()
END
ELSE
BEGIN
	Set @BabysitterID = -1	
END


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
 
Upvote 0 Downvote
Thanks,

I modified my SP to something like this

Code: 
USE [quickbabysit]
GO
/****** Object:  StoredProcedure [dbo].[AddNewBabysitterRegistration]    Script Date: 05/12/2012 23:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddNewBabysitterRegistration]
(
@BabysitterID int OUTPUT,
@Firstname varchar(150),
@Surname varchar(150),
@EmailAddress nvarchar(max),
@Password nchar(8),
@AddressLine1 varchar(150),
@AddressLine2 varchar(150),
@AddressLine3 varchar(150),
@AddressLine4 varchar(150),
@Town varchar(150),
@Postcode varchar(20),
@DateofBirth datetime,
@Gender varchar(10),
@ContactPhone varchar(20),
@Availability varchar(50),
@Visa varchar(4),
@CRB varchar(4)


)
AS
SET NOCOUNT ON;
IF not exists (select * from dbo.Babysitters where emailAddress = @EmailAddress)
BEGIN
INSERT INTO dbo.Babysitters(Firstname, Surname, EmailAddress, Password, AddressLine1, AddressLine2, AddressLine3, AddressLine4, Town, Postcode, DateofBirth, Gender, ContactPhone, Availability, Visa, CRB ) 
VALUES (@Firstname, @Surname, @EmailAddress, @Password, @AddressLine1, @AddressLine2, @AddressLine3, @AddressLine4, @Town, @Postcode, @DateofBirth, @Gender, @ContactPhone, @Availability, @Visa, @CRB) 

SET @BabysitterID = SCOPE_IDENTITY()
END 
ELSE 
BEGIN 
Set @BabysitterID = -1 
END

How do I then get the aspx page to stop inserting the record. It seems to not insert the record but continue logging in?
 
Upvote 0 Downvote

To have the aspx page not insert the record, you'll need to query the database with another stored procedure with just the email address to see if it exists (or not) before you attempt the insert. What you have now will work with a small change in your code:

Code: 
Dim MyReg As Integer = Convert.ToInt32(BabysitterID.Value)

        If MyReg = -1 Then
            'highlight email address background to draw attention
            txtEmail.BackColor = Drawing.Color.Pink
            lblEmailError.Text = "Email address already exists"

        Else
            Me.txtRegID.Text = MyReg
            GetFile()
            UpdateCVStatus()
            send_emailSitter()
            pnlRegSitter.Visible = False
            pnlSuccess.Visible = True
            FormsAuthentication.RedirectFromLoginPage(txtEmail.Text, False)
            'Response.AddHeader("Refresh", "4;URL=babysitters/home.aspx") 
            Response.Redirect("babysitters/home.aspx")

        End If

This way only postbacks that have inserted the record where the email address did not exist in the table will be redirected.


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

AhmedAzizBarbary
  • Locked
  • Question
How to pass list of checkboxes not checked on page model razor page?
Replies
0
Views
472
AhmedAzizBarbary
AhmedAzizBarbary
D KARTHICK
  • Locked
  • Question
Which is faster if-else or Switch case ?
Replies
4
Views
297
jbenson001
jbenson001
proximity
  • Locked
  • Question
Check GridView and play sound if result is zero!
Replies
3
Views
159
jbenson001
jbenson001
ahm1985
  • Locked
  • Question
cannot save changes of courses if remove in edit httppost
Replies
0
Views
92
ahm1985
ahm1985
dvannoy
  • Locked
  • Question
Checking if DataAdapter has records
Replies
1
Views
95
dvannoy
dvannoy

Part and Inventory Search

Sponsor

Back
Top