up vote 0 down vote favorite

I have a problem with C#, I have a Class with a function for SqlDataReader and another for SqlCommand (the first one is just for read values from a DataBase and the second one is for INSERT, UPDATE, DELETE ... in the same DB).

The problem is, for the first part of the code (the login), I have to search the values from an Active Directory (it works), then I must see if the user has username and password in my own DB (it works), and then, if the user is not in the DB then I have to create it and get the ID, if it is already created then I just have to get the ID.

The problem is that I get this message :

InvalidOperationException was unhandled by user code

There already exist an Open DataReader associated with this Command, who as to be closed first.

There is the code :

Class.cs :

    private static string MyConnectionString = "THIS IS MY CONNECTION";
    private SqlConnection MyConnection = new SqlConnection(MyConnectionString);
    public SqlCommand MyCommand = new SqlCommand();
    public SqlDataReader MyReader = null;

    public void DBMyReader(String SqlQuery)
    {
        if (MyConnection.State != ConnectionState.Open)
            MyConnection.Open();

        MyCommand.Connection = MyConnection;
        MyCommand.CommandText = SqlQuery;
        MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public void DBMyUpdate(String SqlQuery)
    {
        if (MyConnection.State != ConnectionState.Open)
            MyConnection.Open();

        var cmdTest = new SqlCommand();

        cmdTest.Connection = MyConnection;
        cmdTest.CommandText = SqlQuery;
        cmdTest.ExecuteNonQuery();
    }

    public void DBMyInsert(String SqlQuery)
    {
        DBMyUpdate(SqlQuery);
    }

** Login.aspx.cs: **

                    MyClass.DBMyReader("SELECT util_codi,util_logi,util_nome FROM Tgep_util WHERE util_logi='"
                            + Session["username"].ToString() + "'");
            MyClass.MyReader.Read();

            if (!MyClass.MyReader.HasRows)
            {
                MyClass.MyReader.Close();
                MyClass.DBMyInsert("INSERT INTO Tgep_util(util_logi,util_nome) "
                            + "VALUES ('" + Session["username"].ToString() + "','" + Session["nome"].ToString() + "')");
            }

            MyClass.DBMyReader("SELECT util_codi,util_logi,util_nome FROM Tgep_util WHERE util_logi='"
            + Session["username"].ToString() + "'");

            MyClass.MyReader.Read();

            Session["user_id"] = MyClass.MyReader["util_codi"].ToString();

            Response.Redirect("FRM_Principal.aspx");

Edit : Update Code (Works for now)

share|improve this question

2 Answers

up vote 3 down vote accepted

The error means exactly what it says.. You have loaded a SQLCommand and started reading rows, and are now trying to do an insert. You need to close out that reader first, or use a new command.

in the DBMyUpdate function you could just create a new command:

public void DBMyUpdate(String SqlQuery)
{
    if (MyConnection.State != ConnectionState.Open)
        MyConnection.Open();
    var cmdUpdate = new SqlCommand();

    cmdUpdate.Connection = MyConnection;
    cmdUpdate.CommandText = SqlQuery;
    cmdUpdate.ExecuteNonQuery(CommandBehavior.CloseConnection);
}

edit: based on comments to this answer, it required using separate connections which seems odd/incorrect.

share|improve this answer
Hi, thanks for your help. The problem is that if I close the Reader i have to start another time de query i don't know if it is very eficient... Is there a solution tu solve the problem only using the classe ? – aliasbody Jun 9 '11 at 15:44
@aliasbody I just added a way to alter your DBMyUpdate function to use a new command that should not conflict with your existing reader. – Fosco Jun 9 '11 at 15:45
Don't worry about efficiency until your code works. You don't want fast broken code. – John Saunders Jun 9 '11 at 15:46
With your code I got the same error... It doesn't make any sense since I use a new SqlCommand... – aliasbody Jun 9 '11 at 15:49
@aliasbody are you sure this was recompiled? just making sure, because that doesn't sound right to get the same error.. – Fosco Jun 9 '11 at 15:51
show 12 more comments
up vote 0 down vote

You can't use the same DataReader for insert/update that you used for insertion. You have to close the DataReader first before associating some other command with the DataReader.

share|improve this answer
But when I tried to close the Reader in the classe (so the action is automatic) then I got an error saying that there is no Connection Open on the Update function :S... – aliasbody Jun 9 '11 at 15:51

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.