Convert NCHAR to string using c# - WebAPI

TechIsaac
TechIsaac used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
"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 ?
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
IT Contracting
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.
Ryan ChongSoftware Team Lead

Commented:
Ok cool, don't you think we assisted you in the process?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial