Save values when DataGridView Row is Checked (Clicked) after Filter using C# and VB.Net in Windows Forms

Last Reply 9 days ago By pandeyism

Posted 10 days ago

Hi,

I am working in c# windows application populate record from sql server to data grid view, with dynamic checkbox facility in each row. i want to select selected rows for some purpose via checkbox of that particular row. till now i successfully achieve my target. but i'm facing a minor issue regarding saving a checked status, Example i want to check only those records whose Name = Max i have a textbox in that textbox i call text chnage event with like Query

Code for Filter by name:

try
{
    SqlCommand cmd = null;
    SqlConnection con = null; Ranks rank = new Ranks();
    con = new SqlConnection(cs.DBcon);
    con.Open();
    cmd = con.CreateCommand();
    cmd.CommandText = "Select * from Records where Name like @Name order by Pno";
    cmd.Parameters.AddWithValue("@Name", "%" + FilterByNameTextbox.Text.Trim() + "%");
    SqlDataAdapter adapter1 = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter1.Fill(dt);

    dataGridView1.DataSource = dt;
    Make_fields_Colorful();
}
catch (Exception exception)
{
    MessageBox.Show(exception.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}

if i write Max in filter by name textbox it would return 3 records with name starts with max using like query as i mention code above. so i only check 2 records out of 3 using dynamic checkbox, till now my code runs perfectly. Now i want to check records which name starts from Ali, now when i write ali in my filter by name textbox it will return rows where name like ali , but problem comes here it will remove my previous checked records, so how i would able to save checked records for both max and ali's rows:

Code for adding dynamic checkboxes in each row

DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
checkBoxColumn.Name = "checkBoxColumn";
checkBoxColumn.DataPropertyName = "Report";
checkBoxColumn.HeaderText = "Report";
dataGridView1.Columns.Insert(10, checkBoxColumn);
dataGridView1.RowTemplate.Height = 100;
dataGridView1.Columns[10].Width = 50;

Please suggest me with your code , i would like to appreciate your quick response.

Thanks in advance

Posted 9 days ago

Hi tanzeelars,

As per your requirement you have to take one textbox and put all checked value in textbox make it visible false on form_load and get values on button click.

Please refer below sample.

Namespaces

C#

using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

private const string ConnectionString = @"Data Source=.\SQL2005;Initial Catalog=Northwind;uid=sa;pwd=pass@123";
public Form1()
{
    InitializeComponent();
    string query = "SELECT CustomerId, ContactName, Country FROM Customers";
    DataTable dt = GetData(query, ConnectionString);
    dataGridView1.DataSource = dt;

    dataGridView1.ReadOnly = true;

    //Add a CheckBox Column to the DataGridView at the first position.
    DataGridViewCheckBoxColumn checkBoxColumn = new DataGridViewCheckBoxColumn();
    checkBoxColumn.HeaderText = "";
    checkBoxColumn.Width = 30;
    checkBoxColumn.Name = "checkBoxColumn";
    dataGridView1.Columns.Insert(0, checkBoxColumn);

    //Assign Click event to the DataGridView Cell.
    dataGridView1.CellContentClick += new DataGridViewCellEventHandler(DataGridView_CellClick);
    textBox1.Visible = false;
}

private DataTable GetData(string query, string ConnectionString)
{
    using (SqlConnection con = new SqlConnection(ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    return dt;

                }
            }
        }
    }
}

private void DataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
    if (e.RowIndex >= 0 && e.ColumnIndex == 0)
    {
        DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
        row.Cells["checkBoxColumn"].Value = !Convert.ToBoolean(row.Cells["checkBoxColumn"].EditedFormattedValue);
        if (Convert.ToBoolean(row.Cells["checkBoxColumn"].Value))
        {
            textBox1.Text += row.Cells[2].Value.ToString() + ",";
        }
    }
}

private void txtName_TextChanged(object sender, EventArgs e)
{
    string query = "SELECT CustomerID, ContactName, Country FROM Customers";
    query += " WHERE ContactName LIKE '%' + @ContactName + '%'";
    query += " OR @ContactName = ''";
    string constr = @"Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123";
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@ContactName", txtName.Text.Trim());
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }
    }
}

private void Save(object sender, EventArgs e)
{
    string constr = @"Data Source=.\SQL2005;Initial Catalog=Test;User ID=sa;Password=pass@123";
    string value = textBox1.Text;
    foreach (var item in value.Split(','))
    {
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO TestDemo VALUES(@Name)", con))
            {
                if (!string.IsNullOrEmpty(item))
                {
                    cmd.Parameters.AddWithValue("@Name", item);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
    string query = "SELECT Name FROM TestDemo";
    DataTable dt = GetData(query, constr);
    dataGridView2.DataSource = dt;
}

VB.Net

Private Const ConnectionString As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;uid=sa;pwd=pass@123"
Public Sub New()
    InitializeComponent()
    Dim query As String = "SELECT CustomerId, ContactName, Country FROM Customers"
    Dim dt As DataTable = GetData(query, ConnectionString)
    dataGridView1.DataSource = dt
    dataGridView1.[ReadOnly] = True
    Dim checkBoxColumn As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn()
    checkBoxColumn.HeaderText = ""
    checkBoxColumn.Width = 30
    checkBoxColumn.Name = "checkBoxColumn"
    dataGridView1.Columns.Insert(0, checkBoxColumn)
    dataGridView1.CellContentClick += New DataGridViewCellEventHandler(AddressOf DataGridView_CellClick)
    textBox1.Visible = False
End Sub

Private Function GetData(ByVal query As String, ByVal ConnectionString As String) As DataTable
    Using con As SqlConnection = New SqlConnection(ConnectionString)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.CommandType = CommandType.Text
            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Private Sub DataGridView_CellClick(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)
    If e.RowIndex >= 0 AndAlso e.ColumnIndex = 0 Then
        Dim row As DataGridViewRow = dataGridView1.Rows(e.RowIndex)
        row.Cells("checkBoxColumn").Value = Not Convert.ToBoolean(row.Cells("checkBoxColumn").EditedFormattedValue)

        If Convert.ToBoolean(row.Cells("checkBoxColumn").Value) Then
            textBox1.Text += row.Cells(2).Value.ToString() & ","
        End If
    End If
End Sub

Private Sub txtName_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim query As String = "SELECT CustomerID, ContactName, Country FROM Customers"
    query += " WHERE ContactName LIKE '%' + @ContactName + '%'"
    query += " OR @ContactName = ''"
    Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;User ID=sa;Password=pass@123"
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@ContactName", txtName.Text.Trim())

            Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                sda.Fill(dt)
                dataGridView1.DataSource = dt
            End Using
        End Using
    End Using
End Sub

Private Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = "Data Source=.\SQL2005;Initial Catalog=Test;User ID=sa;Password=pass@123"
    Dim value As String = textBox1.Text

    For Each item In value.Split(","c)

        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand("INSERT INTO TestDemo VALUES(@Name)", con)
                If Not String.IsNullOrEmpty(item) Then
                    cmd.Parameters.AddWithValue("@Name", item)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End If
            End Using
        End Using
    Next

    Dim query As String = "SELECT Name FROM TestDemo"
    Dim dt As DataTable = GetData(query, constr)
    dataGridView2.DataSource = dt
End Sub

Screenshot