December 10, 2016 at 5:49 pm
I wrongly posted this in SQL Server 2005 so re-posting here as I couldn't move it. I'm running SQL Server 2014. I'm having trouble getting the right result from my stored procedure, I'm not going to post the code because I've messed around with it too much and I can't remember what I started with. What I'm trying to do is this:
If submitted username and password match a record in db
if EmailConfirmed = true
user = 1 (activated)
else
user = -2 (not activated)
else
user = -1 (user invalid)
end
The table is called AspNetUsers, fields are as follows:
Id
UserName
PasswordHash
EmailAddress
EmailConfirmed
No matter how I amend the code it gives unexpected results. Please could you help with the code?
December 10, 2016 at 6:55 pm
This was removed by the editor as SPAM
December 10, 2016 at 10:11 pm
Can you post the full code for the stored procedure please?
😎
December 11, 2016 at 8:00 am
I'm with Wryan138 here: CASE statement (my favorite TSQL 4-letter word!!) #FTW
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 11, 2016 at 6:20 pm
Hi, thanks for the replies.
This code returns the user id but what I want it to return is either 1, -1 or -2. My vb code then uses a CASE statement to do whatever based on the returned number. I thought using "SELECT 1" etc. would return just the number when the SP is run.
ALTER PROCEDURE [dbo].[Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(80)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @userid INT
SELECT @userid = Id
FROM AspNetUsers WHERE UserName = @Username AND [Password] = @Password
IF @userid IS NOT NULL
BEGIN
IF (SELECT EmailConfirmed FROM AspNetUsers WHERE Id = @userid) = 'True'
BEGIN
UPDATE AspNetUsers
SET AccessFailedCount = 1
WHERE Id = @userid
SELECT 1 -- User Activated
END
ELSE
BEGIN
SELECT -2 -- User Not Activated.
END
END
ELSE
BEGIN
SELECT -1 -- User Invalid.
END
END
December 11, 2016 at 8:05 pm
It would REALLY help if you would give us a create table statement with sample data INSERT statements and sproc calls and their actual output and what you think the output SHOULD be (and why). Then we can actually help you without any further confusion or uncertainty. We could also fix your code properly.
At a minimum you have an extra SELECT statement that seems to be completely unnecessary. If you SELECT EmailConfirmed and UserID in the first statement you won't need a second hit to get the EmailConfirmed value.
What is the actual output? Seems like you should get a 1 or -2 or -1 out in an unnamed column (which I would name, BTW). That column value would be an integer IIRC. You would perform an UPDATE under certain circumstances too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 12, 2016 at 12:19 am
ma701ss (12/11/2016)
Hi, thanks for the replies.This code returns the user id but what I want it to return is either 1, -1 or -2. My vb code then uses a CASE statement to do whatever based on the returned number. I thought using "SELECT 1" etc. would return just the number when the SP is run.
ALTER PROCEDURE [dbo].[Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(80)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @userid INT
SELECT @userid = Id
FROM AspNetUsers WHERE UserName = @Username AND [Password] = @Password
IF @userid IS NOT NULL
BEGIN
IF (SELECT EmailConfirmed FROM AspNetUsers WHERE Id = @userid) = 'True'
BEGIN
UPDATE AspNetUsers
SET AccessFailedCount = 1
WHERE Id = @userid
SELECT 1 -- User Activated
END
ELSE
BEGIN
SELECT -2 -- User Not Activated.
END
END
ELSE
BEGIN
SELECT -1 -- User Invalid.
END
END
If I am not mistaken, EmailConfirmed is defined as BIT, not VARCHAR, so
this
IF (SELECT EmailConfirmed FROM AspNetUsers WHERE Id = @userid) = 'True'
needs to be replaced with this
IF (SELECT EmailConfirmed FROM AspNetUsers WHERE Id = @userid) = 1
December 12, 2016 at 5:05 am
Or using CASE:
ALTER PROCEDURE [dbo].[Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(80)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @userid INT, @EmailConfirmed BIT; -- is it a BIT or a string type?
SELECT
@userid = Id,
@EmailConfirmed = EmailConfirmed
FROM dbo.AspNetUsers
WHERE UserName = @Username
AND [Password] = @Password;
IF @userid IS NOT NULL AND @EmailConfirmed = 1
UPDATE dbo.AspNetUsers SET AccessFailedCount = 1 WHERE Id = @userid; -- surely not a failure? AccessSucceededCount = 1
-- return a single row with a single column called ReturnValue, or whatever
-- You could also use an OUTPUT parameter for this because it's a scalar value
SELECT ReturnValue = CASE
WHEN @userid IS NOT NULL AND @EmailConfirmed = 1 THEN 1 -- User Activated
WHEN @userid IS NOT NULL THEN -2 -- User Not Activated.
ELSE -1 END; -- User Invalid.
END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 12, 2016 at 5:38 pm
This was removed by the editor as SPAM
December 14, 2016 at 4:17 am
My favourite! 🙂
Declare @SomeField varchar(100)
Declare @SomeVariable varchar(100)
If @SomeField = 'ConditionA'
Select 'It was A'
Else
Begin
Select 'Use the Begin-End for Compound Statements'
Select 'You can also use a Case statement.'
Set @SomeVariable = Case
When @SomeField = 'ConditionB' Then 'It Was B'
When @SomeField = 'ConditionC' Then 'It Was C'
Else 'It was something else'
End
Select 'More compound statements'
End
Select @SomeField
Select @SomeVariable
December 15, 2016 at 4:08 am
Hi,
There are two ways to get results from sqlclient:
1. The return value of a procedure
2. The resultset of a procedure
Try to create the a sp like as below and compare the result of you vb app:
Create procedure dbo.test
As
Select test=1
Return 2
Att.
Nilson Nakano
December 15, 2016 at 6:28 pm
Thanks again, pleased to see so many willing to help. Maybe cos it's Christmas 🙂
I will reply to the comments soon, just to let you know that I haven't abandoned the thread!
December 16, 2016 at 3:36 am
Assuming you have an index on NormalizedUserName...
I'd use something like this:
DECLARE @UserName VARCHAR(1000);
DECLARE @PasswordHash VARCHAR(1000);
SELECT TOP 1 * FROM
(
SELECT U.UserName, CASE WHEN U.EmailConfirmed = 1 THEN 1 ELSE -2 END AS UserState
FROM AspNetUsers U
WHERE U.NormalizedUserName = @UserName
AND U.PasswordHash = @PasswordHash
UNION ALL
(SELECT @UserName, -1)
) AS MatchedUsers
December 16, 2016 at 6:24 pm
I tried the code from ChrisM@Work on page 1 of this thread and it compiled OK. I'm getting the same error though. Not sure now if it's my vb code that's the problem. IIS gives the following error:
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value '20c0e9d4-b54a-4126-8d90-f21da0d35766' to data type int.
Source Error:
Line 17: cmd.Connection = con
Line 18: con.Open()
Line 19: userId = Convert.ToInt32(cmd.ExecuteScalar())
Line 20: con.Close()
Line 21: End Using
20c0e9d4-b54a-4126-8d90-f21da0d35766 is the passwordhash. I don't understand why the SQL is returning this rather than 1, 2 or -1. Seems to be a problem with userId = Convert.ToInt32(cmd.ExecuteScalar())
The vb code is as follows:
Imports System.Web.Security
Public Class Login
Inherits System.Web.UI.Page
Protected Sub ValidateUser(sender As Object, e As EventArgs)
Dim userId As Integer = 0
Dim constr As String = ConfigurationManager.ConnectionStrings("testConnectionString").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("Validate_User")
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Username", Login1.UserName)
cmd.Parameters.AddWithValue("@Password", Login1.Password)
cmd.Connection = con
con.Open()
userId = Convert.ToInt32(cmd.ExecuteScalar())
con.Close()
End Using
Select Case userId
Case -1
Login1.FailureText = "Username and/or password is incorrect."
Exit Select
Case -2
Login1.FailureText = "Account has not been activated."
Exit Select
Case Else
FormsAuthentication.RedirectFromLoginPage(Login1.UserName, Login1.RememberMeSet)
Exit Select
End Select
End Using
End Sub
End Class
December 17, 2016 at 4:28 pm
Hi Rob, I don't have a field call NormalizedUserName in the table.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply