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

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 6 Years Ago by Ionelul: n/a

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

        }

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

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

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 can i send u the project??

is it still not working

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

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??????????

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

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

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

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

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

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

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)
            {

            }

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

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

Edited 6 Years Ago by krishnisilva: n/a

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.

This article has been dead for over six months. Start a new discussion instead.