I have a problem which looks very similar to one posted by reidcor and doonbug in the past. I couldn't see if and how they resolved it.
I have created my own version of thephile. The code is virtually identical. I have created the database from scratch by copying the tables design (although I have made changes such as replace States with Counties etc.), and I cut and paste to create the stored procedures.
I can register a new user successfully, but the validation of the email name and password in login.aspx always fails. If I change the connection string to point to ThePhile database, I can validate the admin@thephile.com user successfully. Similarly, if I change the connection string used by thephile to point to my database, it fails to validate a user in my table. Therefore, I think the code is OK, but the problem is in my database itself. I have looked at the properties of each table item and they look identical. I created a similar stored procedure to look for the email address only (i.e. doesn't use password) and it also returns -1. If I use the query editor to select rows with that password, it successfully shows the correct row. Please can someone help before it drives me mad!

Hey,

Could you post any code? I don't have the book.

Brian

The code is really too long to post. You can download it from the Wrox website. I am using the C# version.

I suspect that my problem has to do with the way I created the tables and stored procedures. I did this using Visual Studio.Net, with MSDE as the SQL server. It looks to me that the original database was created via a version of the the SQL script that also comes with the download, and this includes other directives such as SET QUOTED_IDENTIFER ON etc. With MSDE I can't see a way of duplicating these directives. I think I will buy SQL Server developer's edition, and start again.

I have made some progress on this. The problem seems to be with the SQL at the end of the sp_Validatelogin stored procedure:
IF @UserID != NULL
    RETURN @UserID
    ELSE
    RETURN -1
If I just get rid of the IF statement, and return @UserID, I get the correct ID returned. With the IF statement, I always get -1 returned. For some reason, it always thinks @UserID = NULL (even though I know it isn't NULL as it returns the correct value without the IF). Any ideas? Is it to do with ANSI_NULLS?

Hey,

Try:

if not @UserID is NULL
  return @UserID
else
  return -1

Brian

That worked - thank-you very much. I am not too hot on SQL etc. Do you know why this would work, and the original wouldn't, bearing in mind that the original code is as supplied with the book, and works i.e. if I point to the books database ThePhile, and therefore call their stored procedure (with the != rather than not IS), it works. Is it something to do with the way I created the table (via Visual Studio and MSDE)? UserID is an Identity field.
Anyway, thanks again for your help.
Regards,
Nigel

Hey,

I didn't think you could use != together (thought only separately). But maybe I'm wrong. I thought you always had to use the "is" for checking null. But at any rate, I have no idea why != doesn't work for you.

Out of curiosity, if UserID isn't null (identity), does @UserID represent UserID, and if so, why check for null, since a value is always present?

Brian

Brian

We're checking to make sure the UserID was found. We take the EMailAddress and EncryptedPassword as inputs. If the proper UserID isn't found, we return -1 instead of NULL. If the UserID is found, then we return it as an integer.

Like you, I also thought "is" has to be used to test for NULL, but I think this is working because ANSI_NULLS are turned off for this procedure. I strongly agree that "if not @UserID is NULL" is a more standard approach.

Here's the whole original SP:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE sp_Accounts_ValidateLogin
@EmailAddress varchar(255),
@EncryptedPassword binary(20)
AS
   DECLARE @UserID int

   SELECT @UserID = UserID FROM Accounts_Users WHERE EmailAddress = @EmailAddress
    AND Password = @EncryptedPassword

   IF @UserID != NULL
    RETURN @UserID
   ELSE
    RETURN -1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks for your help. I was using the stored procedure from the restored database, and didn't see any mention of ANSI_NULLS. In trying to fix this, I have come accross the possibility of using ANSI_NULLS (and QUOTED_IDENTIFIER), but I didn't know how to apply it when creating tables/procedures via MSDE. I know ThePhile came with a sql script file for creating the tables etc. (which an earlier post said was out of date and not to be used) which contained references to ANSI_NULLS etc. so I did think my problem was down to how I created my database/tables with MSDE.
Thanks again for your help.