Having an issue with "DataReader already open"

Question

I've searched on SO and didn't find any answer to the problem I'm having.

I made a linq to get all data from two tables and one ajax call to get new data and fill my dropdownlist as the other dropdownlist changes.

Here's what I have so far:

Controller

public ActionResult Index(int? page)
{
    vEntities db = new vEntities();

    var estados = (from e in db.estado
                   select e);

    var localidades = (from l in db.localidade
                       select l);

    ViewData["estados"] = new SelectList(estados, "cod", "descricao");
    ViewData["localidades"] = new SelectList(localidades, "id", "descricao");

    return View();
}

public ActionResult GetLocalidades(string codEstado)
{
    vEntities db = new vEntities();

    var info = (from l in db.localidade
                where l.cod_estado == codEstado
                select l);

    return Json(info);
}

View

<%= Html.DropDownList("estados", ViewData["estados"] as SelectList, String.Empty)%>
<%= Html.DropDownList("localidade", ViewData["localidades"] as SelectList, String.Empty)%>

<script type="text/javascript">
    $(document).ready(function () {
        $("#estados").change(function () {
            var ddlLocalidade = $("#localidade")[0];
            ddlLocalidade.length = 0;
            var cod = $(this).val();

            $.post(
                "/Home/GetLocalidades",
                { codEstado: cod },
                function (data) {
                    var option;
                    $.each(data, function () {
                        option = new Option(this.descricao, this.id);
                        ddlLocalidade.options.add(option);
                    });
                }
                );

        });
    });
</script>

And when I change the first dropdown, which activates the JS function, I'm getting this error right after the method "Getocalidades" returns the Json:

There is already an open DataReader associated with this Connection which must be closed first

I guess I'm having some trouble formatting the code :) sorry for that.

I can't see any problem with the connection, can anyone tell me what's wrong, please ?

Thanks in advance

I have to admit, I can't see anything glaringly wrong. – Alastair Pitts Jan 21 '11 at 03:24 — Alastair Pitts, Jan 21 '11 at 03:24

score 2 · Accepted Answer · answered Jan 21 '11 at 03:27

2

This is just a guess. But what happens if you change these lines

var estados = (from e in db.estado select e);

var localidades = (from l in db.localidade select l);

var info = (from l in db.localidade where l.cod_estado == codEstado select l);

to

var estados = (from e in db.estado select e).ToList();

var localidades = (from l in db.localidade select l).ToList();

var info = (from l in db.localidade where l.cod_estado == codEstado select l).ToList();

answered Jan 21 '11 at 03:27

Divi

7,621
13
47
63

I did, but now I'm having another problem: "A circular reference was detected while serializing an object of type 'Site.Models.estabelecimento'" again, right after returning Json. By the way, the type "estabelecimento" is not on the return, why is that happening ? THanks for the reply ! – Daniel Jan 21 '11 at 03:44
1

Have a look at this link. http://stackoverflow.com/questions/1153385/a-circular-reference-was-detected-while-serializing-an-object-of-type-subsonic-s It might help you – Divi Jan 21 '11 at 03:49
Strange or not, it did work ! What happened ? Just needed to modify to return a new Json .. Thanks ! – Daniel Jan 21 '11 at 04:12

score 0 · Answer 2 · edited May 23 '17 at 11:44

You can force evaluation of your entities by ToList()'ing them. But you can also set MultipleActiveResultSets=true; in your connection string.

The reason that ToList() works is that it forces the command to execute immediately. MultipleActiveResultSets will allow multiple commands to exist on one database connection even if they all haven't finished executing yet. What I did was force the property into my connection string with the following code. For example,

            case DatabaseType.ORACLE:
                _factory = OracleClientFactory.Instance;
                break;
            case DatabaseType.MSSQL:
                _factory = System.Data.SqlClient.SqlClientFactory.Instance;
                if(!_connectionString.Contains("MultipleActiveResultSets")) //helper contains method for string
                    _connectionString = _connectionString.TrimEnd(';') + ";MultipleActiveResultSets=true;";
                break;

See here: Entity Framework: There is already an open DataReader associated with this Command

Having an issue with "DataReader already open"

Controller

View

2 Answers2