locked
How to insert checked checkbox column value to database RRS feed

  • Question

  • hi

           i have created a checkboxcolumn in datagridview  and also having subject column

    what i want now is i only want to insert the checked checkboxcolumn  subject to the database.

    if anyone have any idea about this helpme

    


    Wednesday, April 4, 2012 10:24 AM

Answers

  • Here is another example, this time in C Sharp. I did not append a DataColumn to the DataTable this time yet you can always change it to do that. Note in Button1 click event I show getting CheckBox values from the DataGridView first then from the DataSource of the DataGridView which is a BindingSource component who's DataSource is the DataTable.

    What you cannot do from querying the DataGridView directly is obtain a value for a hidden column as is the Identifier column.

    public class frmMainForm
    {
    	WithEvents bsData As new BindingSource();
    	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles base.Load();
    		bsData.DataSource = GetMockedData();
    		DataGridView1.DataSource = bsData;
    	}
    	private void Button1_Click(object sender, System.EventArgs e)
    	{
    		var CheckedRows = (
    				from R in DataGridView1.Rows.Cast<DataGridViewRow>()
    				where ! R.IsNewRow && Convert.ToBoolean(R.Cells("Process").Value)).ToList
    			select R;
    		Console.WriteLine("Rows taken from DataGridView");
    		foreach (var row in CheckedRows)
    		{
    			Console.WriteLine(row.Cells("FullName").Value);
    		}
    		Console.WriteLine();
    		Console.WriteLine("Rows taken from DataSource");
    		var Items = (
    				from T in ((DataTable)bsData.DataSource).AsEnumerable
    				where Convert.ToBoolean(T["Process"])).ToList
    			select T;
    		if (Items.Count > 0)
    		{
    			foreach (var item in Items)
    			{
    				//
    				// Use a SQL INSERT statement to place data into your database.
    				//
    				Console.WriteLine("[{0}] [{1}]", item("Identifier"), item("FullName"));
    			}
    		}
    		else
    		{
    			MessageBox.Show("Nothing checked");
    		}
    	}
    	public frmMainForm()
    	{
    		//INSTANT C# NOTE: Converted event handler wireups:
    		Button1.Click += new System.EventHandler(Button1_Click);
    	}
    }


    KSG

    • Marked as answer by Bob Wu-MT Monday, April 9, 2012 8:37 AM
    Wednesday, April 4, 2012 1:43 PM

All replies

  • Hello,

    The following example requires a DataGridView and a Button. We start off by adding mocked data into a DataTable followed by adding a Boolean column to the DataTable. The Boolean column will be represented as a CheckBox column when displayed in the DataGridView. Check each row you want to process then press the Button which uses a LINQ statement to query the underlying DataTable for which rows are checked. From here you can loop thru the rows and by using a SQL INSERT statement send data to the backend database. If the data was existing and you want to update data then use the primary key (IDentifier) in this case to use a SQL UPDATE statement to save changes to the backend database.

    Public Class Form1
        WithEvents bsPeople As New BindingSource
        Private Sub Form1_Load( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
            Dim dt = GetMockedData()
            ' Add column to process in the DataGridView
            ' as a DataGridViewCheckBox column
            dt.Columns.Add( _
                New DataColumn With _
                { _
                    .DataType = GetType(Boolean), .ColumnName = "Process" _
                } _
            )
            ' Position to first column is optional
            dt.Columns("Process").SetOrdinal(0)
            ' give each row a value for the injected column
            For Each row As DataRow In dt.Rows
                row("Process") = False
            Next
            dt.AcceptChanges()
            bsPeople.DataSource = dt
            DataGridView1.DataSource = dt
        End Sub
        ''' <summary>
        ''' This would be your data from your table in a database
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function GetMockedData() As DataTable
            Dim table As New DataTable()
            table.Columns.Add("Identifier", GetType(Integer))
            table.Columns.Add("FirstName", GetType(String))
            table.Columns.Add("LastName", GetType(String))
            table.Rows.Add(10, "John", "Smith")
            table.Rows.Add(20, "Mary", "Willson")
            table.Rows.Add(30, "Frank", "Mills")
            table.Rows.Add(40, "Amy", "Jones")
            Return table
        End Function
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim Items =
                (
                    From T In CType(DataGridView1.DataSource, DataTable).AsEnumerable
                    Where T.Field(Of Boolean)("Process") = True
                    Select T
                ).ToList
            If Items.Count > 0 Then
                For Each item In Items
                    '
                    ' Use a SQL INSERT statement to place data into your database.
                    '
                    Console.WriteLine("[{0}] [{1}] [{2}]", item("Identifier"), item("FirstName"), item("LastName"))
                Next
            Else
                MessageBox.Show("Nothing checked")
            End If
        End Sub
    End Class


    KSG

    Wednesday, April 4, 2012 11:14 AM
  • hello

     i am using c sharp.

    i have set the checkboxcolumn true value to 1 and checkboxcolumn false value to 1             The problem here is only last checked row will be inserted into the database. i want to       insert all checked rows.   

    here is mycode:

                                          

       try
                {
                    SqlConnection con = new SqlConnection("data source = localhost\\SQLEXPRESS;initial catalog = mgrsch_schools; integrated security = true");
                    con.Open();
                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {

                        if (Convert.ToBoolean(row.Cells[0].Value = 1))
                        {
                            //dataGridView1.Rows.Insert(row);
                            string col = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();
                            SqlCommand cmd = new SqlCommand("insert into teachersubjectoption(subject,name,teacherid)values('" + col + "','" + comboBox1.Text + "','" + label3.Text + "')", con);
                            cmd.ExecuteNonQuery();

                        }
                        MessageBox.Show("record inserted");


                    }



                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }

    i have set the checkboxcolumn true value to 1 and checkboxcolumn false value to 1             The problem here is only last checked row will be inserted into the database. i want to       insert all checked rows.   

    Wednesday, April 4, 2012 11:41 AM
  • hello

     i am using c sharp.

    i have set the checkboxcolumn true value to 1 and checkboxcolumn false value to 1             The problem here is only last checked row will be inserted into the database. i want to       insert all checked rows.   

    here is mycode:

                                          

       try
                {
                    SqlConnection con = new SqlConnection("data source = localhost\\SQLEXPRESS;initial catalog = mgrsch_schools; integrated security = true");
                    con.Open();
                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {

                        if (Convert.ToBoolean(row.Cells[0].Value = 1))
                        {
                            //dataGridView1.Rows.Insert(row);
                            string col = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();
                            SqlCommand cmd = new SqlCommand("insert into teachersubjectoption(subject,name,teacherid)values('" + col + "','" + comboBox1.Text + "','" + label3.Text + "')", con);
                            cmd.ExecuteNonQuery();

                        }
                        MessageBox.Show("record inserted");


                    }



                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }

    i have set the checkboxcolumn true value to 1 and checkboxcolumn false value to 1             The problem here is only last checked row will be inserted into the database. i want to       insert all checked rows.   

    If you don't mind me asking, a) why do you not treat the CheckBoxColum as a Boolean b) is the DataGridView DataSource data bound or not? Also in your last statement you indicate that True and False are both set to 1, that does not making any sense.

    KSG

    Wednesday, April 4, 2012 1:08 PM
  • Here is another example, this time in C Sharp. I did not append a DataColumn to the DataTable this time yet you can always change it to do that. Note in Button1 click event I show getting CheckBox values from the DataGridView first then from the DataSource of the DataGridView which is a BindingSource component who's DataSource is the DataTable.

    What you cannot do from querying the DataGridView directly is obtain a value for a hidden column as is the Identifier column.

    public class frmMainForm
    {
    	WithEvents bsData As new BindingSource();
    	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles base.Load();
    		bsData.DataSource = GetMockedData();
    		DataGridView1.DataSource = bsData;
    	}
    	private void Button1_Click(object sender, System.EventArgs e)
    	{
    		var CheckedRows = (
    				from R in DataGridView1.Rows.Cast<DataGridViewRow>()
    				where ! R.IsNewRow && Convert.ToBoolean(R.Cells("Process").Value)).ToList
    			select R;
    		Console.WriteLine("Rows taken from DataGridView");
    		foreach (var row in CheckedRows)
    		{
    			Console.WriteLine(row.Cells("FullName").Value);
    		}
    		Console.WriteLine();
    		Console.WriteLine("Rows taken from DataSource");
    		var Items = (
    				from T in ((DataTable)bsData.DataSource).AsEnumerable
    				where Convert.ToBoolean(T["Process"])).ToList
    			select T;
    		if (Items.Count > 0)
    		{
    			foreach (var item in Items)
    			{
    				//
    				// Use a SQL INSERT statement to place data into your database.
    				//
    				Console.WriteLine("[{0}] [{1}]", item("Identifier"), item("FullName"));
    			}
    		}
    		else
    		{
    			MessageBox.Show("Nothing checked");
    		}
    	}
    	public frmMainForm()
    	{
    		//INSTANT C# NOTE: Converted event handler wireups:
    		Button1.Click += new System.EventHandler(Button1_Click);
    	}
    }


    KSG

    • Marked as answer by Bob Wu-MT Monday, April 9, 2012 8:37 AM
    Wednesday, April 4, 2012 1:43 PM