invalid operation exception

Question

krishnisilva 0 Junior Poster

14 Years Ago

hi there,

Question 1:

i have a question in invalid operation exception. it says that " there is already an open datareader associate with this command which must be closed first. "
the code is shown below

public void AddToAction()
        {
            
            String query = @"Select * from TopicAction";
            SqlCommand command = new SqlCommand(query,DB.getConnection());

            db.openConnection();
            SqlDataReader reader = command.ExecuteReader();

            SqlDataAdapter da = new SqlDataAdapter(command);
            DataTable dt = new DataTable();

           [B] da.Fill(dt);
            [/B]


            while (reader.Read())
            {
                for (int r = 0; r < dt.Columns.Count; r++)
                {
                    for (int c = 0; c < dt.Columns.Count; c++ )
                        dgvDAction.Rows[r].Cells[c].Value = dt.Rows[r]["TActionID"].ToString();
                    
                }
            }
            reader.Close();
            db.closeConnection();

        }

the bolded text in the code is highlighted and the aboveexception occurs. how can i solve this.

Question 2:

when getting data from the MS SQL MDF file what are the connections that i should close??

please reply
thanxxxxxxxxxxx

4 Contributors
16 Replies
146 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by sergb

Ionelul 7 Junior Poster in Training

14 Years Ago

If you comment this line

SqlDataReader reader = command.ExecuteReader();

you will still get the error?

Question 2. Ideally, you should have one connection to the database. So any other connection you create and use in a particular method should be closed and disposed.

Ionut

Edited 14 Years Ago by Ionelul because: n/a

farooqaaa 48 Enthusiast

14 Years Ago

You've opened the connection after making the command. Try opening it before the command:

public void AddToAction()
        {
            [B]db.openConnection();[/B]

            String query = @"Select * from TopicAction";
            SqlCommand command = new SqlCommand(query,DB.getConnection());

            
            SqlDataReader reader = command.ExecuteReader();

            SqlDataAdapter da = new SqlDataAdapter(command);
            DataTable dt = new DataTable();

            da.Fill(dt);
            


            while (reader.Read())
            {
                for (int r = 0; r < dt.Columns.Count; r++)
                {
                    for (int c = 0; c < dt.Columns.Count; c++ )
                        dgvDAction.Rows[r].Cells[c].Value = dt.Rows[r]["TActionID"].ToString();
                    
                }
            }
            reader.Close();
            db.closeConnection();

        }

farooqaaa 48 Enthusiast

14 Years Ago

Sorry, I was mistaken.

As Ionelul said, you should close any other connection that is open.

Try commenting the db.OpenConnection() or SqlDataReader reader = command.ExecuteReader(); line and see if you still get the error. I mean:

// db.OpenConnection();
// SqlDataReader reader = command.ExecuteReader();

Thanks

sergb 7 Junior Poster

14 Years Ago

sergb:
hey when i do this it gives an Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

y is this??????????

The "Index out of range" error maybe be thrown when you try to access the Rows or Cells collections of the dgvDAction. Is this a DataGridView? Did you add Rows and Cells to the grid before trying to assign values to them?

In general, I am not quiet sure why you need the reader and the while(reader.Read()) loop. The reader is not used anywhere in the body of the loop. You can just iterate over the DataTable. With this while loop you are re-assigning the value of each cell as many times as there are records in the reader.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

krishnisilva 0 Junior Poster · Answer 1 · 2010-05-13T19:36:51+00:00

i added the db.openconnection() befor the executing command as shown below,

db.openConnection();
SqlDataReader reader = command.ExecuteReader();

the same error comes y is this please how can i solve this??

thanxxxxxxx

krishnisilva 0 Junior Poster · Answer 2 · 2010-05-13T23:07:25+00:00

hey can i send u the project??

is it still not working

sergb 7 Junior Poster · Answer 3 · 2010-05-14T02:05:29+00:00

SqlDataAdapter is using a SqlDataReader behind the scenes to populate your DataTable.
But SqlConnection object does not support more then one open SqlDataReader at a time (unless MARS is enabled on the connection).
When you call "command.ExecuteReader()", it opens a DataReader on your connection.
When you call "da.Fill(dt)", it tries to open another DataReader on the same connection.
Hence, you should either call "reader.Close()" before "da.Fill(dt)", or create your reader after the "da.Fill(dt)" call.

Try moving the line
SqlDataReader reader = command.ExecuteReader();
below
da.Fill(dt);

Hope it helps.

Ionelul 7 Junior Poster in Training · Answer 4 · 2010-05-14T02:37:08+00:00

hey can i send u the project??
is it still not working

if you cannot get it working, it would be helpful to attach it.

krishnisilva 0 Junior Poster · Answer 5 · 2010-05-14T02:45:18+00:00

SqlDataAdapter is using a SqlDataReader behind the scenes to populate your DataTable.
But SqlConnection object does not support more then one open SqlDataReader at a time (unless MARS is enabled on the connection).
When you call "command.ExecuteReader()", it opens a DataReader on your connection.
When you call "da.Fill(dt)", it tries to open another DataReader on the same connection.
Hence, you should either call "reader.Close()" before "da.Fill(dt)", or create your reader after the "da.Fill(dt)" call.
Try moving the line
SqlDataReader reader = command.ExecuteReader();
below
da.Fill(dt);
Hope it helps.

sergb:
hey when i do this it gives an Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

y is this??????????

krishnisilva 0 Junior Poster · Answer 6 · 2010-05-14T02:46:08+00:00

if you cannot get it working, it would be helpful to attach it.

Ionelul: do u know about datagrid view?????????

Ionelul 7 Junior Poster in Training · Answer 7 · 2010-05-14T02:59:28+00:00

Ionelul: do u know about datagrid view?????????

I worked with datagridview, but what is you want to know about it?

krishnisilva 0 Junior Poster · Answer 8 · 2010-05-14T03:10:44+00:00

SqlDataAdapter is using a SqlDataReader behind the scenes to populate your DataTable.
But SqlConnection object does not support more then one open SqlDataReader at a time (unless MARS is enabled on the connection).
When you call "command.ExecuteReader()", it opens a DataReader on your connection.
When you call "da.Fill(dt)", it tries to open another DataReader on the same connection.
Hence, you should either call "reader.Close()" before "da.Fill(dt)", or create your reader after the "da.Fill(dt)" call.
Try moving the line
SqlDataReader reader = command.ExecuteReader();
below
da.Fill(dt);
Hope it helps.

hey

when i call either call "reader.Close()" before "da.Fill(dt)" : when i try to close the reader it gives an error because it is not definied.

create your reader after the "da.Fill(dt)" call. : when i do this it gives an error " Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index "

what should i do
please reply
thanxxxx
appriciate

krishnisilva 0 Junior Poster · Answer 9 · 2010-05-14T03:11:48+00:00

I worked with datagridview, but what is you want to know about it?

how can i add data from database to a unbound datagrid view

Ionelul 7 Junior Poster in Training · Answer 10 · 2010-05-14T03:31:01+00:00

this worked for me:

SqlConnection db = new SqlConnection(consts.ConnectionString); ;
            try
            {
                SqlCommand Filter = new SqlCommand("uspFilterOrders", db);
                Filter.CommandType = CommandType.StoredProcedure; //in this case is a stored procedure, but change this with your query
                Filter.Parameters.Add(new SqlParameter("@FirstName", consts.PrepareSqlValue(FirstName.ToUpper().Trim())));
                Filter.Parameters.Add(new SqlParameter("@LastName", consts.PrepareSqlValue(LastName.ToUpper().Trim())));
                Filter.Parameters.Add(new SqlParameter("@OrderNumber", OrderNumber));
                Filter.Parameters.Add(new SqlParameter("@OrderDate", OrderDate));
                db.Open();
                SqlDataAdapter Adapter = new SqlDataAdapter(Filter);
                DataTable dt = new DataTable();
                Adapter.Fill(dt);
                BindingSource bs = new BindingSource();
                bs.DataSource = dt;
                gridOrders.DataSource = bs;

            }
            catch (Exception e)
            {

            }

krishnisilva 0 Junior Poster · Answer 11 · 2010-05-14T03:41:09+00:00

this worked for me:

SqlConnection db = new SqlConnection(consts.ConnectionString); ;
            try
            {
                SqlCommand Filter = new SqlCommand("uspFilterOrders", db);
                Filter.CommandType = CommandType.StoredProcedure; //in this case is a stored procedure, but change this with your query
                Filter.Parameters.Add(new SqlParameter("@FirstName", consts.PrepareSqlValue(FirstName.ToUpper().Trim())));
                Filter.Parameters.Add(new SqlParameter("@LastName", consts.PrepareSqlValue(LastName.ToUpper().Trim())));
                Filter.Parameters.Add(new SqlParameter("@OrderNumber", OrderNumber));
                Filter.Parameters.Add(new SqlParameter("@OrderDate", OrderDate));
                db.Open();
                SqlDataAdapter Adapter = new SqlDataAdapter(Filter);
                DataTable dt = new DataTable();
                Adapter.Fill(dt);
                BindingSource bs = new BindingSource();
                bs.DataSource = dt;
                gridOrders.DataSource = bs;

            }
            catch (Exception e)
            {

            }

what is this code u have postes

i have attached the project that i am doing when u click login and click proposals in progress and then select on solar in the datagrid view then the error comes
please

how can i solve this

appriciate a lot thanxxxxxxxxx

krishnisilva 0 Junior Poster · Answer 12 · 2010-05-14T03:46:54+00:00

this worked for me:

SqlConnection db = new SqlConnection(consts.ConnectionString); ;
            try
            {
                SqlCommand Filter = new SqlCommand("uspFilterOrders", db);
                Filter.CommandType = CommandType.StoredProcedure; //in this case is a stored procedure, but change this with your query
                Filter.Parameters.Add(new SqlParameter("@FirstName", consts.PrepareSqlValue(FirstName.ToUpper().Trim())));
                Filter.Parameters.Add(new SqlParameter("@LastName", consts.PrepareSqlValue(LastName.ToUpper().Trim())));
                Filter.Parameters.Add(new SqlParameter("@OrderNumber", OrderNumber));
                Filter.Parameters.Add(new SqlParameter("@OrderDate", OrderDate));
                db.Open();
                SqlDataAdapter Adapter = new SqlDataAdapter(Filter);
                DataTable dt = new DataTable();
                Adapter.Fill(dt);
                BindingSource bs = new BindingSource();
                bs.DataSource = dt;
                gridOrders.DataSource = bs;

            }
            catch (Exception e)
            {

            }

what is this code u have postes

i have attached the project that i am doing when u click login and click proposals in progress and then select on solar in the datagrid view then the error comes
please

how can i solve this

appriciate a lot thanxxxxxxxxx

hey i have made it a txt extention delete the txt extension
and use it
k