Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a simple table with user details with following columns:

USERID  INT
FIRST_NAME  VARCHAR(50)
LAST_NAME VARCHAR(50)
EMAIL  VARCHAR(100)
COUNTRY  VARCHAR(50)
USERNAME  VARCHAR(50)
PASSWORD  VARCHAR(50)


This is the SP that I have:

ALTER PROCEDURE [dbo].[Login]
@userName VARCHAR(50)
,@password VARCHAR(50)
AS
BEGIN
	SELECT USERID AS Id, (FIRST_NAME+' '+LAST_NAME) AS NAME, EMAIL AS email FROM [dbo].[USER] WHERE USERNAME = @userName AND PASSWORD = @password
	FOR JSON AUTO
END


The ADO .NET code that I wrote is as follows:


SqlDataReader reader = sqlCommand.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine(reader["Id"].ToString() + ' ' + reader["email"].ToString());
            }


When I am trying to debug the code or just run it, it throws the following exception in the line with 'Console.WriteLine' statement:

System.IndexOutOfRangeException
HResult=0x80131508
Message=Id
Source=Microsoft.Data.SqlClient

What I have tried:

When I modify my SP to not have 'FOR JSON AUTO' part, the code runs fine and I get the expected result.
But as soon as I add 'For JSON AUTO', I start getting the exception.

I searched some sites for the resolution, but I did not find any. Most of them suggested to check the column name if it is right, so I copied and pasted the column names directly from the SP. But to no avail.

Anyone having any idea on how to resolve this?
Posted
Updated 26-Mar-23 16:28pm
v2
Comments
Mary_Bhatta 26-Mar-23 22:27pm    
HiThanks for your suggestion about password encryption, I will try to implement it.
As for the original question, I tried with the returned column name as well:

Console.WriteLine(reader["Id"].ToString() + ' ' + reader["email"].ToString());

But I am still getting the same exception message.

1 solution

Match your column names to those actually returned.

Your SELECT returns columns called "Id", "NAME", and "email", and your C# code is expecting "USERID" and "EMAIL".

But don't do it like that. Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]

And remember: if this is web based and you have any European Union users then GDPR applies and that means you need to handle passwords as sensitive data and store them in a safe and secure manner. Text is neither of those and the fines can be .... um ... outstanding. In December 2018 a German company received a relatively low fine of €20,000 for just that.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900