When you register users on ThePhile web app it seems its more than ok to have duplicate entries. I can for example enter the same user details over and over again and the app will keep on accepting it and put it into the database. So its more than alright to have many users with the same Email address, First name, last name, etc, etc.

There is a piece of code in the new.aspx.vb file which goes as follows:

If creationResult = CInt( _
        Wrox.WebModules.Accounts.ProcResultCodes.AccountAl readyOnFile) Then

        CreateError.Text = _
          "<br/>An account with that e-mail address is already on file.<br/>"
        CreateError.Visible = True

'---- etc etc

However this is never triggered. The primary key for the Users table is an integer which is an identity. If this were violeted (A situation which i think would be impossible considering that the database puts this value in itself and not the registering user) Then you would get an error for duplicate records which is handled in ThePhile code.

I can overcome this problem myself by simply adding in another function which would check to see if the regstering users email address already exists in the database and if so then supply the error that the email address is already in use on this site.

So i was just wondering whats the go? Is this a mistake? a bug? a forgotten method that was never written?

You can prevent multiple registration. When adding a new user to the user table, your code is basically calling a stored procedure to insert into the user table.

Modify that that sp so that it first checks if the email is already in the database. If yes return something which you can use to show the message.
In my case it won't happen since I am using string userid for user registration rather than email and that userid is Primary key of the table. Duplicate userid will voilate the Primary key constraint.

Thanks
Musa

What the authors intended to do, which they did not implement in the database design, was for SQL server to throw an error when you tried to insert a new user into the database where they had the same email as someone else already there. SQL Server would throw a 2601 or 2627 error, which can be detected. In order for your application to detect it, modify Data.Users.cs:

Next, you need to modify the Accounts_Users table to make the email column an index. To do this, right-click on Accounts_Users, and choose Design Table. Then highlight the EmailAddress column, right-click, and choose Indexes/Keys... Click the New button, make sure the Column is set to EmailAddress, Check 'Create UNIQUE' and then select 'Constraint'.

I made those changes and it works now, as originally intended by the authors.

References:
http://doc.ddart.net/mssql/sql2000/h...err_1_2fu9.htm


Hope this helps,

Doug

I already saw this code in Data.cs:

catch ( SqlException e )
{
    // 2601 is the number returned when the primary key
    // is violated. We know if we're violating the primary
    // key that the e-mail address is already on file.
    // Its cheaper than actually searching for that address before
    // inserting.
    if (e.Number == 2601)
    {
        return (int)Wrox.WebModules.Accounts.ProcResultCodes.Acco untAlreadyOnFile;
    }
...

The only change needed is to add a unique index on email address. But, this was already done in the updated database backup file included with the code download.

If anyone has not created their DB by restoring the DB backup, they will be missing a lot of additional SPs and other fixes. The DB Creation script in the code download has NOT been updated.

Eric

which file should i use from the code download to get the latest database files.

i have downloaded this filr=e
http://media.wiley.com/product_ancil...l-server-7.zip

But when i try to restore the db, i get an error:
"The backup set holds a backup of a dataabse other than the existing 'thephile'.."

Any idea what I need for the latest DB file

You are trying to restore the SQL Server 7 backup file. You probably want to use the SQL Server 2000 backup file that comes with the code download.

If you try that one and it fails with a similar error, you may have to delete the existing database of that name before doing the restore.

Eric

this file 'thephile-database-backup' comes with the code. I already have used this file to setup the DB. But still a lot of stored procedures are missing, like: sp_Accounts_GetPermissionsInCategory

Also, the Users table still has UserID as the primary key.

Am i missing something??

But in order to make it work properly, we still need to modify "if (e.Number == 2601)" to "if ( (e.Number == 2601) || (e.Number == 2627) )" as Drohm suggested above, otherwise I still got ugly error message.

2601 "Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'."

2627 "Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'."

however, since I am just a beginner, I have no idea about what the difference is between those two error codes. Why my SQL Server 2000 throw a "2627" not a "2601" when I was trying to regist a duplicated user?

Gary