 |
BOOK: ASP.NET Website Programming Problem-Design-Solution  | This is the forum to discuss the Wrox book ASP.NET Website Programming: Problem - Design - Solution, Visual Basic .NET Edition by Marco Bellinaso, Kevin Hoffman; ISBN: 9780764543869 |
|
Welcome to the p2p.wrox.com Forums.
You are currently viewing the BOOK: ASP.NET Website Programming Problem-Design-Solution section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|

September 6th, 2004, 06:44 AM
|
Authorized User
|
|
Join Date: Oct 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
New.aspx works but login.aspx fails Validation
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!
|

September 6th, 2004, 07:17 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
Hey,
Could you post any code? I don't have the book.
Brian
|

September 7th, 2004, 11:05 AM
|
Authorized User
|
|
Join Date: Oct 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

September 9th, 2004, 05:16 AM
|
Authorized User
|
|
Join Date: Oct 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

September 9th, 2004, 07:52 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
Hey,
Try:
if not @UserID is NULL
return @UserID
else
return -1
Brian
|

September 9th, 2004, 08:13 AM
|
Authorized User
|
|
Join Date: Oct 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

September 10th, 2004, 11:48 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
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
|

September 10th, 2004, 10:47 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

September 13th, 2004, 02:29 AM
|
Authorized User
|
|
Join Date: Oct 2003
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
 |