Home / Questions / 335581
Accepted
Franqo Balsamo
Asked: 2020-03-10 05:11:34 +0800 CST 2020-03-10 05:11:34 +0800 CST

Query with DataGridView

  • 772

I have a query regarding items selected by means of a CheckBoxwithin DataGridView. The thing is, I load mi DataGridViewand add CheckBoxes. What happens is that I have a TextBoxthat helps to filter when I press numbers to filter everything goes well but when I delete them CheckBoxthey change position and the CheckBox. Attached a GIFreference:

enter image description here

As you will see, CheckBoxthey change places at the time of filtering and are deselected. Does anyone have an idea how to fix that?

I leave the method with which I filter:

private void filtrarTitulo(string valor)
        {
            SqlConnection conexion = new SqlConnection();
            conexion.ConnectionString = "cadena de conexión";
            conexion.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter("SELECT Titulo FROM V_CuetaWeb WHERE Titulo LIKE ('" + valor + "%') ORDER BY Titulo DESC", conexion);
            da.Fill(ds, "Cuotas");
            conexion.Close();
            dtgTitulo.DataSource = ds;
            dtgTitulo.DataMember = "Cuotas";
        }

Where I use it:

private void txtTitulo_TextChanged(object sender, EventArgs e)
        {
            filtrarTitulo(txtTitulo.Text.ToString().Trim());
        }

How do I assign the CheckBoxto DataGridView:

private void Form1_Load(object sender, EventArgs e)
        {
            llenaTitulo();

            DataGridViewCheckBoxColumn chk = new DataGridViewCheckBoxColumn();
            chk.HeaderText = "Seleccione";
            chk.Name = "check";
            dtgTitulo.Columns.Add(chk);
            dtgTitulo.AllowUserToAddRows = false;
        }

And as I charge the DataGridView:

public void llenaTitulo()
        {
            try
            {
                string conn = "cadena de conexión";
                using (SqlConnection conexion = new SqlConnection(conn))
                {
                    conexion.Open();
                    string query = "SELECT Titulo AS Título FROM V_CuetaWeb GROUP BY Titulo ORDER BY Titulo DESC";
                    SqlCommand sqlCommand = new SqlCommand(query, conexion);
                    SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dtgTitulo.DataSource = ds.Tables[0];
                    conexion.Close();
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

problem one: Se cambian de lugar los ChckcBox

problem two: Se deseleccionan los CheckBox

Any suggestion?

UPDATE:

private void filtrarTituloDos(string valor)
        {
            List<DataGridViewRow> filtro = new List<DataGridViewRow>();
            foreach (DataGridViewRow fila in dtgTitulo.Rows)
            {
                //Asumo que el campo a filtrar se llama Titulo
                //Filtramos las filas con campo Titulo igual a valor
                if (fila.Cells["Título"].Value.ToString() == valor)
                {
                    filtro.Add(fila);
                }
            }
            //Borrrando todas la filas actuales del DataGridView
            dtgTitulo.Rows.Clear();

            foreach (DataGridViewRow fila in filtro)
            {
                //Agregamos las filas filtradas al DataGridView
                dtgTitulo.Rows.Add(fila);
            }
        }

private void txtTitulo_TextChanged(object sender, EventArgs e)
        {
            lblCaracterTitulo.Text = Convert.ToString(8 - txtTitulo.Text.Length);
            filtrarTituloDos(txtTitulo.Text.ToString().Trim());
        }

Problem 1: System.ArgumentException: 'No se puede borrar esta lista.' in the line: dtgTitulo.Rows.Clear();at the time of typing in the TextBoxto filter

Problem 2: If I comment out the line from the problem above, it happens that it doesn't filter anything. Suggestions?

c#

3 Answers

  1. Do you think if in the filltitle() method you also put the code of the checkbox columns:

     DataGridViewCheckBoxColumn chk = new DataGridViewCheckBoxColumn();
            chk.HeaderText = "Seleccione";
            chk.Name = "check";
            dtgTitulo.Columns.Add(chk);
            dtgTitulo.AllowUserToAddRows = false;

    I think that would be the solution. slds

    • 1

  2. Here is an example of what I would do without using databases or other objects:

        private void filtrarTitulo(string valor)
    {
        List<DataGridViewRow> filtro = new List<DataGridViewRow>();
        foreach (DataGridViewRow fila in dtgTitulo.Rows)
        {
            //Asumo que el campo a filtrar se llama Titulo
            //Filtramos las filas con campo Titulo igual a valor
            if (fila.Cells["Titulo"].Value.ToString() == valor)
            {
                filtro.Add(fila);
            }
        }
        //Borrrando todas la filas actuales del DataGridView
        dtgTitulo.Rows.Clear();

        foreach (DataGridViewRow fila in filtro)
        {
            //Agregamos las filas filtradas al DataGridView
            dtgTitulo.Rows.Add(fila);
        }
    }
    • 1
  3. Best Answer

    I put a variant that can be used to solve your problem, note that I create a DataGridView, just to show the itemsfiltered ones, I assign it the same location and size as the dtgTitulo, which implies that it will be shown superimposed on the DataGridView dtgTitulo, but you can give it another location, even you could display it in another form as a popup. 

    //Declarando un DataGridView, para mostrar los items filtrados
private DataGridView dataFiltro;

private void Form1_Load(object sender, EventArgs e)
{
            llenaTitulo();

            DataGridViewCheckBoxColumn chk = new DataGridViewCheckBoxColumn();
            chk.HeaderText = "Seleccione";
            chk.Name = "check";
            dtgTitulo.Columns.Add(chk);
            dtgTitulo.AllowUserToAddRows = false;
            //Crear el DataGridView que mostrará el filtro.
            dataFiltro = new DataGridView();
            dataFiltro.Location = dtgTitulo.Location;
            dataFiltro.Size = dtgTitulo.Size;
            dataFiltro.Visible = false;
            this.Controls.Add(dataFiltro);
}

    The procedure filtrarTitulo.

          private void filtrarTitulo(string valor)
        {
                        List<DataGridViewRow> filtro = new List<DataGridViewRow>();
        foreach (DataGridViewRow fila in dtgTitulo.Rows)
        {
            //Asumo que el campo a filtrar se llama Titulo
            //Filtramos las filas con campo Titulo igual a valor
            if (fila.Cells["Titulo"].Value!=null && fila.Cells["Titulo"].Value.ToString() == valor)
            {
                filtro.Add(fila);
            }
        }
        //Borrando todas la columnas actuales del DataGridView dataFiltro
        dataFiltro.Columns.Clear();
        foreach (DataGridViewColumn columna in dtgTitulo.Columns)
        {
            //Agregamos columnas clonadas al DataGridView dataFiltro
            if (columna is DataGridViewCheckBoxColumn)
            {
                int index = dataFiltro.Columns.Add(new DataGridViewCheckBoxColumn());
                dataFiltro.Columns[index].Name = columna.Name;
                dataFiltro.Columns[index].HeaderText = columna.HeaderText;
            }
            else
                dataFiltro.Columns.Add(columna.Name, columna.HeaderText);
        }


        foreach (DataGridViewRow fila in filtro)
        {
            //Agregamos las filas filtradas al DataGridView dataFiltro
           int rowindex = dataFiltro.Rows.Add();
           int columindex = 0;
           foreach (DataGridViewCell celda in fila.Cells)
           {
               if (celda.Value != null)
               {
                   dataFiltro[columindex, rowindex].Value = celda.Value;
               }
               columindex++;
           }
        }
        //Colocamos la propiedad visible a true
        dataFiltro.Visible = true;
        //Lo traemos al frente, se mostrará sobre el DataGridView dtgTitulo
         dataFiltro.BringToFront();
        }

    In your original code you make the call to the procedure filtrarTituloin the event TextChangedof the TextBox, that is not convenient, because you would be executing the call every time the user presses a key, I recommend adding a button that the user can press when he finishes writing the text and want to apply the filter.

       private void buttonAplicarFiltro_Click(object sender, EventArgs e)
    {
      if(txtTitulo.Text!="")
      {
         lblCaracterTitulo.Text = Convert.ToString(8 - txtTitulo.Text.Length);
   filtrarTitulo(txtTitulo.Text.ToString().Trim());
      }
    }

    Also in your procedure llenaTitulo, you must do the following, in addition to everything you have already done.

    public void llenaTitulo()
        {
          //Aquí deja todo el código que tienes escrito en este procedimiento.

         //Aquí agrega lo siguiente.
          //Traer al frente el DataGridView dtgTitulo
          dtgTitulo.BringToFront();
          //Ocultar el DataGridView dataFiltro
          dataFiltro.Visible = false;
        }
    • 0