Click here to Skip to main content
14,098,130 members
Rate this:
 
Please Sign up or sign in to vote.
See more: , +
I am using
EntityDataReader
to fill a
List<SelectListItem> products
from MS SQL table that has only one column of type "string": [prod_name_pharma_form]
I am getting this exception:
Attempt to read from column ordinal '0' is not valid.  With CommandBehavior.SequentialAccess, you may only read from column ordinal '1' or greater.'


What I have tried:

I am using this code that works perfectly with tables of more than one column:

<pre>public JsonResult AjaxProducts()
        {
            List<SelectListItem> products = new List<SelectListItem>();
            string query = "SELECT p.prod_name_pharma_form FROM Entities.FDF_FAMILY_PROD_NAME as p";
            using (EntityConnection con = new EntityConnection("name=Entities"))
            {
                using (EntityCommand cmd = new EntityCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (EntityDataReader sdr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
                    {
                        while (sdr.Read())
                        {
                            products.Add(new SelectListItem
                            {
                                Value = sdr["prod_name_pharma_form"].ToString(),
                                Text = sdr["prod_name_pharma_form"].ToString()
                            });
                        }
                    }
                }
            }

            return Json(products);
        }

I understand that with "SequentialAccess" I have to read (select) columns in the correct order to avoid this kind of exceptions, but What do I do in case having a table of one column?
Posted
Updated 8-Mar-18 1:38am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

It could be because you're reading it twice. Try this
while (sdr.Read())
{
    string name = sdr["prod_name_pharma_form"].ToString();

    products.Add(new SelectListItem
    {
        Value = name,
        Text = name
    });
}
   
v2
Comments
Maciej Los 8-Mar-18 7:53am
   
Short And To The Point!
TempoClick 8-Mar-18 7:56am
   
Works perfectly! It was reading the column twice. Thanks.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Put a try/catch block around your code so you can properly debug it.

BTW, you don't need the alias "p" if you're not doing a join.

I would also add a check to make sure sdr.HasRows is true before trying to access the sdr object, as well as check to make sure the desired column(s) exist within the column schema before trying to retrieve the value of the desired column(s). In short, it is NEVER safe to assume that you're dataset will contain what you're expecting.
   
Comments
F-ES Sitecore 8-Mar-18 7:40am
   
No need for a row check, if there are no rows in sdr then the code will never go into the

while (sdr.Read())

block as sdr.Read() will return false.
#realJSOP 8-Mar-18 8:03am
   
I always check.
TempoClick 8-Mar-18 7:53am
   
@John Simmons, thanks for the advice.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190518.1 | Last Updated 8 Mar 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100