Retrieve data from SQL Server Pivot using Web Service Method

Question

I had an output like this in relation to Columns to Rows in Sql Server with multiple data

EmployeeID    01/01/2018         |  01/02/2018         |  01/03/2018
00001         07:00 AM - 05:00PM |  07:00 AM - 05:00PM |  07:00 AM - 05:00PM
00002         07:00 AM - 05:00PM |  07:00 AM - 05:00PM |  07:00 AM - 05:00PM

The Question is how can i call the field name in Web Service Here is my code

            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                my_cs var = new my_cs ();
                var.DTRDate = rdr["01/01/2018"].ToString();
                var.DTRDate = rdr["01/02/2018"].ToString();
                var.DTRDate = rdr["01/03/2018"].ToString();
                mylist.Add(var);
            }
            con.Close();

It says:

System.IndexOutOfRangeException: "01/01/2018" at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at System.Data.SqlClient.SqlDataReader.get_Item(String name)

need help..

did you debug rdr content? btw you are saving all 3 dates on the same variable overwriten first 2 and getting only the last one — Juan Carlos Oropeza, Sep 12 '18 at 03:01
yes.. it says An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code — Genebert, Sep 12 '18 at 03:04
I mean the content of rdr after `rdr.Read()` and before the `.ToString()` — Juan Carlos Oropeza, Sep 12 '18 at 03:04

score 1 · Answer 1 · answered Sep 12 '18 at 03:06

1

Not sure what you have the error, I assume the fields have a different name you are expecting.

You can debug rdr to check what are the field name or check this question:

Can you get the column names from a SqlDataReader?

But you can always access fields by index

 while (rdr.Read())
        {
            my_cs var = new my_cs ();
            var.DTRDate1 = rdr[0].ToString();
            var.DTRDate2 = rdr[1].ToString();
            var.DTRDate3 = rdr[2].ToString();
            mylist.Add(var);
        }

answered Sep 12 '18 at 03:06

Juan Carlos Oropeza

47,252
12
78
118

i want DTRdate to be dynamic base on the given date of pivot sql server – Genebert Sep 12 '18 at 05:09
then use index instead of field name – Juan Carlos Oropeza Sep 12 '18 at 14:17

Retrieve data from SQL Server Pivot using Web Service Method

1 Answers1