C# Invalid Operation Exception

I do not know how to close the Data Reader....
because I need to run 2 different queries, the program stopped at the second query....
anyone can help?
error.jpg
Billy MaVice PresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arabia_vnCommented:
Then simply close the first one and open the second one, like this
SqlDataReader myReader;
myReader = myCommand.ExecuteReader();
myReader.Close();

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Billy MaVice PresidentAuthor Commented:
do you mean I should close the connection?

I am connecting my Oracle database using Olb.
Todd GerbertIT ConsultantCommented:
You can either use two SqlCommands, or make sure you close the first SqlDataReader before executing the second one; since your queries are sequential (i.e. one runs after the other, not both at the same time) just closing the DataReader makes the most sense.  Putting objects inside a "using" block is a good way to make sure that it's disposed of when you're done using it, even if an exception occurs. http://msdn.microsoft.com/en-us/library/yh598w02%28VS.80%29.aspx

using (SomeObject obj = new SomeObject())
{
  // The "obj" variable is only valid inside this block
  // that way you can't use it after it's been Disposed
  // The project wouldn't compile if you attempted to use
  // obj after this block, that way Visual Studio will keep
  // you from doing something that'll cause a problem
  obj.SomeMethod();
} // The "obj" variable will always be Disposed here

Open in new window

using (SqlConnection connection = new SqlConnection("Your Connection String"))
{
  connection.Open();
  using (SqlCommand command = connection.CreateCommand())
  {
    command.CommandText = "SELECT * FROM SomeTable";
    using (SqlDataReader reader = command.ExecuteReader())
    {
      while (reader.Read())
        Debug.WriteLine(reader["theColumn"].ToString();
    }  // reader will be disposed/closed here

    command.CommandText = "SELECT * FROM SomeOtherTable";
    using (SqlDataReader reader = command.ExecuteREader())
    {
      while (reader.Read())
        Debug.WriteLine((int)reader["someOtherolumn"]);
    }
  }
  connection.Close();
}

Open in new window

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Todd GerbertIT ConsultantCommented:
Close the reader, you can leave the connection open until both readers are done.
Billy MaVice PresidentAuthor Commented:
so difficult...I am new to C#....
I have got no experience and no knowledge...I just did it by trial and error
Billy MaVice PresidentAuthor Commented:
sigh...i create one more command object......
seem stupid...but easier.
OleDbConnection connection = new OleDbConnection(connectionString);

                connection.Open();

                string query = "UPDATE WORK_RATING R " + 
                               "SET R.YEAR = '" + newYear.Text + "', " + 
                               "R.PLANNED_RATING = '" + newPlannedRating.Text + "', " +
                               "R.ACTUAL_RATING = '" + newActualRating.Text + "' " + 
                               "WHERE R.WORK_ID = '" + selectedWorkID + "' " +
                               "AND R.YEAR = '" + oldYear + "'";

                //Response.Write(query);
                OleDbCommand command = new OleDbCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = query;
                command.Connection = connection;

                GridView2.DataSource = command.ExecuteReader();

                query = "UPDATE WORK_OPTION O " +
                        "SET O.YEAR = '" + newYear.Text + "', " +
                        "O.PLANNED_OPTION = '" + newPlannedOption.Text + "', " +
                        "O.ACTUAL_OPTION = '" + newActualOption.Text + "' " +
                        "WHERE O.WORK_ID = '" + selectedWorkID + "' " +
                        "AND O.YEAR = '" + oldYear + "'";

                //Response.Write(query);
                command = new OleDbCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = query;
                command.Connection = connection;

                GridView2.DataSource = command.ExecuteReader();

                connection.Close();

                GridView2.EditIndex = -1;
                LoadData2();
                message2.Visible = false;

Open in new window

Todd GerbertIT ConsultantCommented:
not sure what you're trying to get at there - why are you setting the data source of the same GridView twice?
Billy MaVice PresidentAuthor Commented:
I just wanna run the update statement
Todd GerbertIT ConsultantCommented:
Well, if the update doesn't return any records there's not much use assigning it to the grid view. The command objects have an ExecuteNonQuery() method you can use for AWKWARD statements that don't return any records, like UPDATE.

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
  connection.Open();
  using (OleDbCommand command = connection.CreateCommand())
  {
    command.CommandText = "update table1...etc";
    command.ExecuteNonQuery();
  }
  using (OleDbCommand command = connection.CreateCommand())
  {
    command.CommandText = "update table2...etc";
    command.ExecuteNonQuery();
  }
  connection.Close();
}
Billy MaVice PresidentAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.