I have a simple table with user details with following columns:
This is the SP that I have:
ALTER PROCEDURE [dbo].[Login]
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
The ADO .NET code that I wrote is as follows:
SqlDataReader reader = sqlCommand.ExecuteReader();
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:
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?