Avatar of TechIsaac
TechIsaac
Flag for United States of America asked on

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

C#SQL

Avatar of undefined
Last Comment
Ryan Chong

8/22/2022 - Mon
Ryan Chong

"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 ?
Pavel Celba

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?
TechIsaac

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
TechIsaac

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ryan Chong

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