Wrox网站高级编程里面的一个问题--创建用户问题

Question

用户验证是通过 Email 和 密码
创建新用户时，先判断Email是否存在于数据库中。如果有则不添加，否则添加。实际上处理时判断Email是否存在总是失效，造成有相同的Email存在， 大家一起看一看，究竟要怎么改？
数据库的内容：CREATE PROCEDURE sp_Accounts_CreateUser
@EmailAddress varchar(255),
@Password binary(20),
@FirstName varchar(30),
@LastName varchar(30),
@Address1 varchar(80),
@Address2 varchar(80),
@City varchar(40),
@State varchar(2),
@ZipCode varchar(10),
@HomePhone varchar(14),
@Country varchar(50),
@UserID int output
AS
INSERT INTO Accounts_Users(EmailAddress, Password, FirstName, LastName, Address1, Address2,
City, State, ZipCode, HomePhone, Country)
VALUES(@EmailAddress, @Password, @FirstName, @LastName, @Address1, @Address2,
@City, @State, @ZipCode, @HomePhone, @Country)
SET @UserID = @@IDENTITY
RETURN 1
GO数据层中的User方法:
 Public Function Create( _
        ByVal emailAddress As String, _
        ByVal password As Byte(), _
        ByVal firstName As String, _
        ByVal lastName As String, _
        ByVal address1 As String, _
        ByVal address2 As String, _
        ByVal city As String, _
        ByVal state As String, _
        ByVal zipCode As String, _
        ByVal homePhone As String, _
        ByVal country As String) As Integer
      Dim rowsAffected As Integer
            Dim parameters As SqlParameter() = { _
                New SqlParameter("@EmailAddress", SqlDbType.VarChar, 255), _
                New SqlParameter("@Password", SqlDbType.Binary, 20), _
                New SqlParameter("@FirstName", SqlDbType.VarChar, 30), _
                New SqlParameter("@LastName", SqlDbType.VarChar, 50), _
                New SqlParameter("@Address1", SqlDbType.VarChar, 80), _
                New SqlParameter("@Address2", SqlDbType.VarChar, 80), _
                New SqlParameter("@City", SqlDbType.VarChar, 40), _
                New SqlParameter("@State", SqlDbType.VarChar, 40), _
                New SqlParameter("@ZipCode", SqlDbType.VarChar, 12), _
                New SqlParameter("@HomePhone", SqlDbType.VarChar, 30), _
                New SqlParameter("@Country", SqlDbType.VarChar, 50), _
                New SqlParameter("@UserID", SqlDbType.Int, 4)}      parameters(0).Value = emailAddress
      parameters(1).Value = password
      parameters(2).Value = firstName
      parameters(3).Value = lastName
      parameters(4).Value = address1
      parameters(5).Value = address2
      parameters(6).Value = city
      parameters(7).Value = state
      parameters(8).Value = zipCode
      parameters(9).Value = homePhone
      parameters(10).Value = country
      parameters(11).Direction = ParameterDirection.Output            Try
                RunProcedure("sp_Accounts_CreateUser", parameters, rowsAffected)
            Catch exc As SqlException                If exc.Number = 2601 Then                    Return CInt(ProcResultCodes.AccountAlreadyOnFile)                Else
                    Throw New AppException("An error occurred while executing" & _
                      "the Accounts_CreateUser stored procedure", exc)
                End If            End Try            Return CInt(parameters(11).Value)
          
    End Function
商务层的User方法：
 Public Function Create() As Integer      Dim dataUser As New Data.User(myModuleSettings.ConnectionString)      myUserId = dataUser.Create( _
          myEmailAddress, _
          myPassword, _
          myFirstName, _
          myLastName, _
          myAddress1, _
          myAddress2, _
          myCity, _
          myState, _
          myZipCode, _
          myHomePhone, _
          "Spain")   ' Country... why is this hard-coded?      Return myUserId    End Function
表示层创建新用户按钮事件
    Private Sub Register_Click( _
          ByVal sender As Object, _
          ByVal e As EventArgs) _
          Handles Register.Click            ' Create a new user based on the information in the form.
            Dim newUser As New Wrox.WebModules.Accounts.Business.User            newUser.Password = SitePrincipal.EncryptPassword(Password.Text)
            newUser.EmailAddress = EmailAddress.Text
            newUser.Address1 = Address1.Text
            newUser.Address2 = Address2.Text
            newUser.City = City.Text            newUser.State = state.Text            newUser.ZipCode = ZipCode.Text
            newUser.HomePhone = HomePhone.Text
            newUser.FirstName = FirstName.Text
            newUser.LastName = LastName.Text            Dim creationResult As Integer
            creationResult = newUser.Create()            Label1.Text = "createResult=" + creationResult.ToString
            Label2.Text = ";AccountOnFile=" + CInt(Wrox.WebModules.Accounts.ProcResultCodes.AccountAlreadyOnFile).ToString()            If creationResult = CInt(Wrox.webModules.Accounts.ProcResultCodes.AccountAlreadyOnFile) Then
                CreateError.Text = _
                  "<br/>创建新用户失败,该E-Mail帐户在系统中已经存在.<br/>"
                CreateError.Visible = True            Else                FormsAuthentication.SetAuthCookie(EmailAddress.Text, True)
            End If   End Sub另外还有个枚举的文件：  Public Enum ProcResultCodes      '检查是否存在该帐户，返回值阻止同一Email被多次注册。
        AccountAlreadyOnFile = -100
    End Enum
谢谢

Answer 1

CREATE PROCEDURE sp_Accounts_CreateUser
@EmailAddress varchar(255),
@Password binary(20),
@FirstName varchar(30),
@LastName varchar(30),
@Address1 varchar(80),
@Address2 varchar(80),
@City varchar(40),
@State varchar(2),
@ZipCode varchar(10),
@HomePhone varchar(14),
@Country varchar(50),
@UserID int output
AS
IF NOT Exists(Select 1 from Accounts_Users where EmailAddress=@EmailAddress)
Begin
INSERT INTO Accounts_Users(EmailAddress, Password, FirstName, LastName, Address1, Address2,
City, State, ZipCode, HomePhone, Country)
VALUES(@EmailAddress, @Password, @FirstName, @LastName, @Address1, @Address2,
@City, @State, @ZipCode, @HomePhone, @Country)
SET @UserID = @@IDENTITY
RETURN 1
End
else
Begin
SET @UserID=0
End
GO

Answer 2

SET @UserID=-100 这样设置有没有问题？这样返回的值才和枚举中的对应。