Commandbehavior.sequentialaccess exception. Attempt to read from column ordinal '0' is not valid.

Question

0.00/5 (No votes)

See more:

, +

I am using

C#

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:

C#

<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 8-Mar-18 1:24am

TempoClick

Updated 8-Mar-18 1:38am

Add a Solution

2 solutions

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.

Posted 8-Mar-18 1:37am

#realJSOP

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

F-ES Sitecore · Accepted Answer · 2018-03-08T01:38:00

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
    });
}

Posted 8-Mar-18 1:38am

F-ES Sitecore

Updated 8-Mar-18 1:44am

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.