locked
Inserting values from datagridview to sql database c#.net RRS feed

  • Question

  • hi, I have two tables in sql database ie, product and innvoice.

    i am fetching selected data from table.product and inserted in datagridview.

    now i want to store data from datagridview to table.innvoice. this all operation takes on one button click.

    here is my code:

                         
            private void button5_Click_2(object sender, EventArgs e) //add produt
            {
                SqlConnection con = new SqlConnection(@"Persist Security Info=False;User ID=usait;password=123;Initial Catalog=givenget;Data Source=RXN-PC\ROSHAAN");
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter("SELECT product.p_name,product.p_category, product.sale_price FROM product where p_code='" + textBox16.Text + "'", con);
                DataTable dt = new DataTable();
    
                da.Fill(dt);
                dataGridView3.DataSource = dt;
    
    // here i want to insert values from datagridview3 to table innvoice.
                }

    Table innvoice have 4 columns, p_code,p_name,p_category and p_price

    Saturday, June 15, 2013 11:15 AM

Answers

  • You should use SqlCommandBuilder to automatically generate CRUD methods for SqlDataAdapter and then insert a new record in dataGridView and then Update the DataTable, thus the newly-built record will be synchronizedly inserted into real physical datatable.

    And another way is that you can just choose the dataGridView on the UI winform and click the tiny arrow onto its right corner, and step according to the wizard to bind a table from existing database.


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    • Marked as answer by Bob Shen Monday, July 8, 2013 8:43 AM
    Saturday, June 15, 2013 11:23 AM
  • Make your DataTable a member of your Form class so that you can access it outside of the button Click event handler. You can't put the code to insert the data from the grid in the same button click, because the user hasn't entered anything into the grid. So, you'll need another button called "Save". Here's what the two button click event handlers should look like:

    // Declare the DataTable globally to the Form, instead of local to the event handler // Might as well put the ConnectionString here too. private DataTable MyTable; private string MyConnectionString = @"Persist Security Info=False;User ID=usait;password=123;Initial Catalog=givenget;Data Source=RXN-PC\ROSHAAN"; private void button5_Click_2(object sender, EventArgs e) //add produt { SqlConnection con = new SqlConnection(MyConnectionString); // con.Open(); // don't need the Open, the Fill will open and close the connection automatically SqlDataAdapter da = new SqlDataAdapter("SELECT product.p_name,product.p_category, product.sale_price FROM product where p_code='" + textBox16.Text + "'", con); MyTable = new DataTable(); da.Fill(MyTable); dataGridView3.DataSource = MyTable; } private void buttonSave_Click_2(object sender, EventArgs e) // save to invoice { SqlConnection con = new SqlConnection(MyConnectionString); string SqlCmdText = "INSERT INTO invoice (p_code, p_name, p_category, p_price) " + VALUES (@code, @name, @category, @price)"; SqlCommand sc = new SqlCommand(SqlCmdText, con); con.Open(); foreach (DataRow row in MyTable.Rows) { sc.Parameters.Clear(); sc.Parameters.AddWithValue("@code", row["p_code"]); sc.Parameters.AddWithValue("@name", row["p_name"]); sc.Parameters.AddWithValue("@category", row["p_category"]); sc.Parameters.AddWithValue("@price", row["p_price"]); sc.ExecuteNonQuery(); } con.Close(); }



    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Sunday, June 16, 2013 5:13 PM
    • Marked as answer by Bob Shen Monday, July 8, 2013 8:43 AM
    Sunday, June 16, 2013 5:12 PM
  • You have not mentioned which version of SQL Server you are using. If you are using SQL Server 2008, you can make use of Table-Valued Parameters.

    You can directly pass DataTable to SQL Server Table Parameter.

    Table Value Parameter in SQL Server 2008


    • Marked as answer by Bob Shen Monday, July 8, 2013 8:43 AM
    Sunday, June 16, 2013 6:00 PM

All replies

  • You should use SqlCommandBuilder to automatically generate CRUD methods for SqlDataAdapter and then insert a new record in dataGridView and then Update the DataTable, thus the newly-built record will be synchronizedly inserted into real physical datatable.

    And another way is that you can just choose the dataGridView on the UI winform and click the tiny arrow onto its right corner, and step according to the wizard to bind a table from existing database.


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    • Marked as answer by Bob Shen Monday, July 8, 2013 8:43 AM
    Saturday, June 15, 2013 11:23 AM
  • Make your DataTable a member of your Form class so that you can access it outside of the button Click event handler. You can't put the code to insert the data from the grid in the same button click, because the user hasn't entered anything into the grid. So, you'll need another button called "Save". Here's what the two button click event handlers should look like:

    // Declare the DataTable globally to the Form, instead of local to the event handler // Might as well put the ConnectionString here too. private DataTable MyTable; private string MyConnectionString = @"Persist Security Info=False;User ID=usait;password=123;Initial Catalog=givenget;Data Source=RXN-PC\ROSHAAN"; private void button5_Click_2(object sender, EventArgs e) //add produt { SqlConnection con = new SqlConnection(MyConnectionString); // con.Open(); // don't need the Open, the Fill will open and close the connection automatically SqlDataAdapter da = new SqlDataAdapter("SELECT product.p_name,product.p_category, product.sale_price FROM product where p_code='" + textBox16.Text + "'", con); MyTable = new DataTable(); da.Fill(MyTable); dataGridView3.DataSource = MyTable; } private void buttonSave_Click_2(object sender, EventArgs e) // save to invoice { SqlConnection con = new SqlConnection(MyConnectionString); string SqlCmdText = "INSERT INTO invoice (p_code, p_name, p_category, p_price) " + VALUES (@code, @name, @category, @price)"; SqlCommand sc = new SqlCommand(SqlCmdText, con); con.Open(); foreach (DataRow row in MyTable.Rows) { sc.Parameters.Clear(); sc.Parameters.AddWithValue("@code", row["p_code"]); sc.Parameters.AddWithValue("@name", row["p_name"]); sc.Parameters.AddWithValue("@category", row["p_category"]); sc.Parameters.AddWithValue("@price", row["p_price"]); sc.ExecuteNonQuery(); } con.Close(); }



    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Sunday, June 16, 2013 5:13 PM
    • Marked as answer by Bob Shen Monday, July 8, 2013 8:43 AM
    Sunday, June 16, 2013 5:12 PM
  • You have not mentioned which version of SQL Server you are using. If you are using SQL Server 2008, you can make use of Table-Valued Parameters.

    You can directly pass DataTable to SQL Server Table Parameter.

    Table Value Parameter in SQL Server 2008


    • Marked as answer by Bob Shen Monday, July 8, 2013 8:43 AM
    Sunday, June 16, 2013 6:00 PM
  • There are many ways to do what you want to do.  Here's one idea for you.

    http://csharp.net-informations.com/datagridview/csharp-datagridview-database-operations.htm

    Here are some more options for you ton consider.

    http://www.nullskull.com/q/10352740/export-the-data-of-datagridview-into-the-sql-server.aspx


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, July 18, 2013 9:11 PM