Convert NCHAR to string using c# - WebAPI

I have a sql parameter which is NCHAR

I want to convert the variable to string as you will see in the code block.

It throwing a error when I reference the variable.

"Message": "An error has occurred.",
    "ExceptionMessage": "Pic_Date",
    "ExceptionType": "System.IndexOutOfRangeException",
    "StackTrace": "   at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)\r\n   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)\r\n   at System.Data.SqlClient.SqlDataReader.get_Item(String name)\r\n   at lob_totals.Models.LobTotals.pic_of_day(List`1 PicDateCaption, String Date_In) in c:\\!web_insight\\API\\projects\\lob-totals-sol\\lob-totals\\Models\\LobTotals.cs:line 318\r\n   at lob_totals.Controllers.LobTotalsController.pic_of_day(String id) in c:\\!web_insight\\API\\projects\\lob-totals-sol\\lob-totals\\Controllers\\LobTotalsController.cs:line 205"
}

What am I doing incorrectly.
This task is a snippet from my WebAPI

Isaac
string Pic_Date { get; set; } 
 string Pic_Caption { get; set; }

 public List<LobTotals> pic_of_day (List<LobTotals> PicDateCaption, string Date_In)
          {

              using (var con = lob_totals.Models.Database.GetConnection())
              {
                  // Find Pic Of Day
                  using (SqlCommand command = new SqlCommand("insight_sel_pic_day", con))
                  {
                      command.CommandType = System.Data.CommandType.StoredProcedure;
                       
                      con.Open();
                       
                      command.Parameters.Add(new SqlParameter("@INpic_date", System.Data.SqlDbType.NChar)).Value = Date_In;
                      using (SqlDataReader rdr = command.ExecuteReader())
                      // Locate Picture
                          if (rdr.HasRows)
                              while (rdr.Read())
                      {
                          LobTotals picData = new LobTotals();
                          picData.Pic_Date = rdr["Pic_Date"].ToString();
                          picData.Pic_Caption = rdr["Caption"].ToString();
                           



                          PicDateCaption.Add(picData);
                          con.Close();

                      }

                      return PicDateCaption;
                  }

Open in new window

TechIsaacIT Contracting Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
"ExceptionType": "System.IndexOutOfRangeException"

I would probably think not the NChar issue.

try look at LobTotalsController.cs:line 205 instead.

also make sure the sp: insight_sel_pic_day returned fields such as Pic_Date and Caption ?
pcelbaCommented:
What SQL engine are we talking about here?
What is the Stored procedure parameter data type?
What is the type of the Date_In variable?
Are you sure the SP call returned correct results?
TechIsaacIT Contracting Author Commented:
sql 2016,  the parameter type is NCHAR,  I found the problem the SP does not return the parameter.  It only returns
the caption in the stored procedure.

Thanks for the hints.

Isaac
TechIsaacIT Contracting Author Commented:
The stored procedure which I was referred to does not return the parameter.  I made adjusts to include the parameter in
my returned list.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongSoftware Team LeadCommented:
Ok cool, don't you think we assisted you in the process?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.