December 17, 2016 at 4:59 pm
This was removed by the editor as SPAM
December 17, 2016 at 5:48 pm
Hi Wryan, no I've not tested before but just ran it in SSMS and got the following:
Msg 245, Level 16, State 1, Procedure Validate_User, Line 18
Conversion failed when converting the nvarchar value '20c0e9d4-b54a-4126-8d90-f21da0d35766' to data type int.
It's trying to convert the user id to an integer but I don't see where even though it gives the line number.
December 17, 2016 at 9:21 pm
This was removed by the editor as SPAM
December 18, 2016 at 3:49 am
ma701ss (12/17/2016)
Hi Wryan, no I've not tested before but just ran it in SSMS and got the following:Msg 245, Level 16, State 1, Procedure Validate_User, Line 18
Conversion failed when converting the nvarchar value '20c0e9d4-b54a-4126-8d90-f21da0d35766' to data type int.
It's trying to convert the user id to an integer but I don't see where even though it gives the line number.
The user id is a guid and cannot be converted to an integer, please amend the logic of your code.
😎
January 1, 2017 at 2:57 pm
Hi, happy new year, been away and just coming back to this now.
What I'm not understanding is why the return value is not either -1, -2 or 1. Why is it returning a value from the db?
January 3, 2017 at 5:20 am
ma701ss (1/1/2017)
Hi, happy new year, been away and just coming back to this now.What I'm not understanding is why the return value is not either -1, -2 or 1. Why is it returning a value from the db?
How many columns does your stored procedure return?
What are their datatypes?
What does this statement do? How many rows/columns does it process?
userId = Convert.ToInt32(cmd.ExecuteScalar())
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
January 3, 2017 at 6:51 am
I think the UserID variable in your code will be receiving the RETURN value from executing the stored procedure. This is not the same as the results of the SELECT statements; you would have to look at the resultset to retrieve that information.
January 3, 2017 at 8:05 am
It would appear that the [Id] field in AspNetUsers contains GUIDS.
However, your proc declares @userid as INT, and then tries to assign a GUID to an INT variable.
Let's start by trying to get the data type issue resolved.
Try this mod to your proc
ALTER PROCEDURE [dbo].[Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(80)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @userid UNIQUEIDENTIFIER = NULL;
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 that does not work, please run the following script to get the column information that will help us to assist you.
DECLARE @TableName sysname = 'AspNetUsers';
SELECT
'CREATE TABLE [' + s.name + '].[' + t.name + '] (' + CHAR(10)
+ STUFF(
( SELECT ' , ' + c.name + ' '
+ dt.name
+ CASE WHEN dt.name IN ('datetime2', 'datetimeoffset')
THEN '(' + CONVERT(VARCHAR, c.scale) + ')'
WHEN dt.name IN ('char', 'varchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, c.max_length) END + ')'
WHEN dt.name IN ('nchar', 'nvarchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, c.max_length/2) END + ')'
WHEN dt.name IN ('decimal', 'numeric')
THEN '(' + CONVERT(VARCHAR, c.[precision]) + ', ' + CONVERT(VARCHAR, c.scale) + ')'
ELSE ''
END
+ CASE c.is_nullable WHEN 1 THEN ' NULL' ELSE ' NOT NULL' END + CHAR(10)
FROM sys.columns c
INNER JOIN sys.types dt ON dt.user_type_id = c.user_type_id
WHERE c.object_id = t.object_id
ORDER BY c.column_id
FOR XML PATH('')
), 3, 1, ' ')
+ ');' + CHAR(10)
FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
WHERE t.name = @TableName;
January 3, 2017 at 6:54 pm
Forget returning the user id or data from any other columns, is it possible to amend the code to just return one value, i.e. either -2, -1 or 1? I just want to check the username and password are correct, and then if emailconfirmed is true/false and this will decide (by using CASE) if -2, -1 or 1 is returned. I don't need/want to return any values from the db.
January 3, 2017 at 9:16 pm
ma701ss (1/3/2017)
Forget returning the user id or data from any other columns, is it possible to amend the code to just return one value, i.e. either -2, -1 or 1? I just want to check the username and password are correct, and then if emailconfirmed is true/false and this will decide (by using CASE) if -2, -1 or 1 is returned. I don't need/want to return any values from the db.
That is exactly what your proc is trying to do. However, you have a data type mismatch, which needs to be rectified in order to evaluate whether to return -2, -1, or 1.
January 4, 2017 at 1:27 am
ma701ss (1/3/2017)
Forget returning the user id or data from any other columns, is it possible to amend the code to just return one value, i.e. either -2, -1 or 1? I just want to check the username and password are correct, and then if emailconfirmed is true/false and this will decide (by using CASE) if -2, -1 or 1 is returned. I don't need/want to return any values from the db.
Of course. Can you post up your stored procedure?
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
January 4, 2017 at 4:31 pm
DesNorton (1/3/2017)
It would appear that the [Id] field in AspNetUsers contains GUIDS.However, your proc declares @userid as INT, and then tries to assign a GUID to an INT variable.
Let's start by trying to get the data type issue resolved.
Try this mod to your proc
ALTER PROCEDURE [dbo].[Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(80)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @userid UNIQUEIDENTIFIER = NULL;
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 that does not work, please run the following script to get the column information that will help us to assist you.
DECLARE @TableName sysname = 'AspNetUsers';
SELECT
'CREATE TABLE [' + s.name + '].[' + t.name + '] (' + CHAR(10)
+ STUFF(
( SELECT ' , ' + c.name + ' '
+ dt.name
+ CASE WHEN dt.name IN ('datetime2', 'datetimeoffset')
THEN '(' + CONVERT(VARCHAR, c.scale) + ')'
WHEN dt.name IN ('char', 'varchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, c.max_length) END + ')'
WHEN dt.name IN ('nchar', 'nvarchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, c.max_length/2) END + ')'
WHEN dt.name IN ('decimal', 'numeric')
THEN '(' + CONVERT(VARCHAR, c.[precision]) + ', ' + CONVERT(VARCHAR, c.scale) + ')'
ELSE ''
END
+ CASE c.is_nullable WHEN 1 THEN ' NULL' ELSE ' NOT NULL' END + CHAR(10)
FROM sys.columns c
INNER JOIN sys.types dt ON dt.user_type_id = c.user_type_id
WHERE c.object_id = t.object_id
ORDER BY c.column_id
FOR XML PATH('')
), 3, 1, ' ')
+ ');' + CHAR(10)
FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
WHERE t.name = @TableName;
This line made it work as it should, thanks:
DECLARE @userid UNIQUEIDENTIFIER = NULL;
Thanks sincerely to everybody who posted replies, I appreciate your help. I will be back.......
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply