We help IT Professionals succeed at work.

Convert NCHAR to string using c# - WebAPI

TechIsaac
TechIsaac asked
on
95 Views
Last Modified: 2019-02-10
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

CERTIFIED EXPERT

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 ?
CERTIFIED EXPERT

Commented:
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:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions