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?