how to insert a datagridview value to database on button click in c#

Question

I have two controls in my winform ,that is textboxes and datagridview .textboxes data entered by user save in one table(purchase) and datagridview data entered is save in another table (purchasedetail).My problem is textboxes values are saving in purchase table but datagridview values are not saving to database.

here is my save button code:

 private void SAVE(object sender, EventArgs e)
        {

            try
            {  
                con.Open();
                cmd = new SqlCommand("insert into Purchase(purchase_id,purchase_date,ref_no,total,total_wrds) values(@purchase_id,@purchase_date,@ref_no,@total,@total_wrds)", con);

                cmd.Parameters.AddWithValue("@purchase_id", textid.Text);
                cmd.Parameters.AddWithValue("@purchase_date", dateTimePicker1.Value);
                cmd.Parameters.AddWithValue("@ref_no", textrno.Text);
                cmd.Parameters.AddWithValue("@total", texttotal.Text);
                cmd.Parameters.AddWithValue("@total_wrds", textinwrds.Text);

                cmd.ExecuteNonQuery();
                foreach (DataGridViewRow row in datagrid.Rows)
                {

                    if (!row.IsNewRow)
                    {

                           using(SqlCommand cmd11 = new SqlCommand("insert into Purchasedetail(product_id, product_name,qty,price,tax,discount,total)values(@product_id, @product_name,@qty,@price,@tax,@discount,@total)", con))

                           {
                            cmd11.Parameters.AddWithValue("@product_id", row.Cells[0].Value);
                            cmd11.Parameters.AddWithValue("@product_name", row.Cells[1].Value);
                            cmd11.Parameters.AddWithValue("@qty", row.Cells[2].Value);
                            cmd11.Parameters.AddWithValue("@price", row.Cells[3].Value);
                            cmd11.Parameters.AddWithValue("@tax", row.Cells[4].Value);
                            cmd11.Parameters.AddWithValue("@discount", row.Cells[5].Value);
                            cmd11.Parameters.AddWithValue("@total", row.Cells[6].Value);
                            cmd11.ExecuteNonQuery();
                            datagrid.Refresh();

                            //row.ReadOnly = true;
                            //clm.ReadOnly = true;
                            MessageBox.Show("Added Sucessfully", "OUTPUT", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            return;
                           }

                    }



                }



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

            finally
            {
                con.Close();
            }

        }

Zgrkpnr · Accepted Answer · 2014-05-04 10:59:06Z

2

I assume you want to save your DataGrid table to a table in your database. With this answer I recommend you to work with your data in datagrid, not textboxes, if it is not very necessary. First I am explaining the code. Since your temporary DG table will be "added" you need to clear it first. Then turn your itemssource into a table and it is ready to be saved. Use an adapter to update the table.

  try
                {
                    SqlCommand ClearTableCommand = new SqlCommand("delete from " + CurrentTableName + ";", myconnection);
                    ClearTableCommand.ExecuteNonQuery();
                    DataTable myDT;
                    DataView myview;
                    myview = (DataView)dataGrid1.ItemsSource;
                    myDT = myview.ToTable(CurrentTableName);



                using (SqlDataAdapter Adapter1 = new SqlDataAdapter("select * from " + CurrentTableName + "", myconnection))
                {

                    Adapter1.UpdateCommand = new SqlCommandBuilder(Adapter1).GetUpdateCommand(true);
                    Adapter1.AcceptChangesDuringFill = false;
                    Adapter1.AcceptChangesDuringUpdate = true;
                    Adapter1.Update(myDT);

                }

            }
            catch (Exception ex)
            {

                System.windows.MessageBox.Show(ex.Message);
            }

edited May 4, 2014 at 10:59

answered May 4, 2014 at 10:31

Zgrkpnr

1,2514 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3467599 Over a year ago

I have bounded datagridview,within one button click i want both textboxes and datagridview values will save to different tables of same database.how can i do sir?

Zgrkpnr Over a year ago

In your quesiton you asked how to save datagrid to table. This Code tells you how. Now, you can't save datagrid and textboxes with one button. Gridview updates the table where as textboxes inserts new data without update. Of course you can change vice versa but it would be an unnecessary work. You need to be more clear about what you want.

Collectives™ on Stack Overflow

how to insert a datagridview value to database on button click in c#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related