Hello,

I have created a MS SQL database and have a table user with the following
column : username,password,email.I have set the username as the primary key
on the table.

Below is my ASP code :

'Check if username doesn't already exist
 do while not rsAuthors.EOF
  if rsAuthors("user_login")=Username then
   set rsAuthors=nothing
   set adoConn=nothing
   Response.Redirect("signup.asp?login=1")
  end if
  rsAuthors.MoveNext
 loop

 'Add a record
 rsAuthors.AddNew
 'Put username and password in record
 rsAuthors("user_login")=Username
 rsAuthors("user_pass")=Password
 rsAuthors("user_name")=Fullname
 rsAuthors("user_email")=Email
 'Save record
 rsAuthors.Update

 set rsAuthors=nothing
 set adoConn=nothing

When I try to create a new user with the same username which already exist
in the table column username, I get the below message :


Microsoft OLE DB Provider for SQL Server error '80040e2f'

Violation of PRIMARY KEY constraint 'PK_User'. Cannot insert duplicate key
in object 'User'.

How can I avoid this error message?I do redirect the user to another page if
the username exist in the database but this function does not happen.



Thanks





Andy

I would use a sproc to handle this, rather than the expensive way you are
doing it at the moment (returning records back to ADO, and the trying to
work out if the user exists - your method will start to fall over as the
number of users increases).

Run the following in Query Analyser:

CREATE PROC usp_UserCreate

    @UserLogin             varChar(100),
    @UserPassword      varChar(100),
    @UserName            varChar(100),
    @UserEmail            varChar(100)

AS

    IF EXISTS
    (
        SELECT
            NULL
        FROM
            Users
        WHERE
            UserLogin = @UserLogin
    )
    BEGIN
        RETURN 1
    END
    ELSE
    BEGIN

        INSERT INTO (Users)
        (
            User_login,
            User_pass,
            User_name,
            User_email
        )
        VALUES
        (
            @UserLogin,
            @UserPass,
            @UserName,
            @UserEmail
        )

    END

GO

GRANT EXEC ON usp_UserCreate TO PUBLIC

GO


Now, in your ASP code, you do this:

<%
Set objCommand = Server.CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConn
With objCommand
    .CommandText = "usp_UserCreate"
    .CommandType = adCmdStoredProc
    .Parameters.Append
        .CreateParameter("@ReturnValue", adInteger, adParamReturnValue, 4)
    .Parameters.Append _
        .CreateParameter("@UserLogin", adVarChar, adParamInput, 100,
UserName)
    .Parameters.Append _
        .CreateParameter("@UserPassword", adVarChar, adParamInput, 100,
Password)
    .Parameters.Append _
        .CreateParameter("@UserName", adVarChar, adParamInput, 100,
FullName)
    .Parameters.Append _
        .CreateParameter("@UserEmail", adVarChar, adParamInput, 100, Email)
    .Execute
End With

intReturnValue = objCommand.Parameters("@ReturnValue")

Set objCommand = Nothing
objConn.Close
Set objConn = Nothing

If intReturnValue  = 1 then
    ' User already exists
    Response.Redirect("signup.asp?login=1")
Elseif intReturnValue = 0 then
    ' User inserted into database
End If
%>

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Andy" <andy@t...>
To: "ASP Databases" <asp_databases@p...>
Sent: Friday, February 07, 2003 10:25 AM
Subject: [asp_databases] Insert Duplicate Key


: Hello,
:
: I have created a MS SQL database and have a table user with the following
: column : username,password,email.I have set the username as the primary
key
: on the table.
:
: Below is my ASP code :
:
: 'Check if username doesn't already exist
:  do while not rsAuthors.EOF
:   if rsAuthors("user_login")=Username then
:    set rsAuthors=nothing
:    set adoConn=nothing
:    Response.Redirect("signup.asp?login=1")
:   end if
:   rsAuthors.MoveNext
:  loop
:
:  'Add a record
:  rsAuthors.AddNew
:  'Put username and password in record
:  rsAuthors("user_login")=Username
:  rsAuthors("user_pass")=Password
:  rsAuthors("user_name")=Fullname
:  rsAuthors("user_email")=Email
:  'Save record
:  rsAuthors.Update
:
:  set rsAuthors=nothing
:  set adoConn=nothing
:
: When I try to create a new user with the same username which already exist
: in the table column username, I get the below message :
:
:
: Microsoft OLE DB Provider for SQL Server error '80040e2f'
:
: Violation of PRIMARY KEY constraint 'PK_User'. Cannot insert duplicate key
: in object 'User'.
:
: How can I avoid this error message?I do redirect the user to another page
if
: the username exist in the database but this function does not happen.

what's the SQL populating rsAuthors?

strSQL = "SELECT username, password, email " _
	& "FROM tblAuthors " _
	& "WHERE username = '" & UserName & "'"

If so you just need to check for EOF and BOF being true and you'll know no
records have been returned, so the username doesn't exist already.  Then you
can proceed to adding the record.

I'd consider adding an autonumber field to the table and using that as your
Keyfield though.  You can still do exactly what you're doing above - you
won't even have to change the code.

HTH,
Col

-----Original Message-----
From: Andy [mailto:andy@t...]
Sent: 06 February 2003 23:25
To: ASP Databases
Subject: [asp_databases] Insert Duplicate Key


Hello,

I have created a MS SQL database and have a table user with the following
column : username,password,email.I have set the username as the primary key
on the table.

Below is my ASP code :

'Check if username doesn't already exist
 do while not rsAuthors.EOF
  if rsAuthors("user_login")=Username then
   set rsAuthors=nothing
   set adoConn=nothing
   Response.Redirect("signup.asp?login=1")
  end if
  rsAuthors.MoveNext
 loop

 'Add a record
 rsAuthors.AddNew
 'Put username and password in record
 rsAuthors("user_login")=Username
 rsAuthors("user_pass")=Password
 rsAuthors("user_name")=Fullname
 rsAuthors("user_email")=Email
 'Save record
 rsAuthors.Update

 set rsAuthors=nothing
 set adoConn=nothing

When I try to create a new user with the same username which already exist
in the table column username, I get the below message :


Microsoft OLE DB Provider for SQL Server error '80040e2f'

Violation of PRIMARY KEY constraint 'PK_User'. Cannot insert duplicate key
in object 'User'.

How can I avoid this error message?I do redirect the user to another page if
the username exist in the database but this function does not happen.



Thanks





Andy





*******

This message and any attachment are confidential and may be privileged or otherwise protected from disclosure.  If you are not the
intended recipient, please telephone or email the sender and delete this message and any attachment from your system.  If you are
not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.

For further information about Clifford Chance please see our website at http://www.cliffordchance.com or refer to any Clifford
Chance office.