How to insert new added rows of data from datagridview into database?

c# database datagridview

17,270

Solution 1

Heres my Insert,cancel and delete statement:

    protected void gv_RowCommand(object sender, GridViewCommandEventArgs e)
        {

            if (e.CommandName == "Insert") //- this is needed to explain that the INSERT command will only work when INSERT is clicked
            {
                gv.DataBind();

                DataTable d = dbcon.GetDataTable("SELECT * FROM CIS.CIS_TRANS ORDER BY ID DESC", "ProjectCISConnectionString");

                string transCode = "", fundCode = "", BSA_CD = "", DP_TYPE = "";

                if (d.Rows.Count > 0)
                {
                    transCode = d.Rows[0]["TRANS_CD"].ToString();
                    fundCode = d.Rows[0]["FUND_CD"].ToString();
                    BSA_CD = d.Rows[0]["BSA_CD"].ToString();
                    DP_TYPE = d.Rows[0]["DP_TYPE"].ToString();

                    if (transCode.Trim().Length > 0)
                    {
                        dbcon.Execute("INSERT INTO CIS.CIS_TRANS (ID,TRANS_CD) VALUES(CIS.S_CIS_TRANS.nextval,'')", "ProjectCISConnectionString");

                        gv.DataBind();
                    }
                }
gv.EditIndex = gv.Rows.Count - 1;

        }
        else if (e.CommandName == "Cancel")
        {
            DataTable d = dbcon.GetDataTable("SELECT * FROM CIS.CIS_TRANS ORDER BY ID DESC", "ProjectCISConnectionString");

            string transCode = "";

            if (d.Rows.Count > 0)
            {
                transCode = d.Rows[0]["TRANS_CD"].ToString();

                if (transCode.Trim().Length == 0)
                {
                    dbcon.Execute(string.Format("DELETE CIS.CIS_TRANS WHERE ID = '{0}'", d.Rows[0]["ID"]), "ProjectCISConnectionString");

                    gv.DataBind();
                }
            }

Solution 2

do you have a sqlcommandbuilder, dataAdapter, and dataTable declared?

for example:

SQLConnection con = (your connection);
SQLDataAdapter = sda;
SQLCommandBuilder = scb;
DataTable = dt;
private void btnEnter_Click(object sender, EventArgs e)


da = new SqlDataAdapter("SELECT * FROM [table] WHERE [columnA]='" + txtData.Text + "' OR [columnB]='" + txtData.Text + "' OR [ColumnC]='" + txtData.Text + "' OR [ColumnD]='" + txtData.Text + "'", con);
            ds = new DataSet();
            dt = new DataTable();
            ds.Clear();
            da.Fill(dt);
            dg.DataSource = dt;

            con.Open();
            con.Close();

private void btnUpdate_Click(object sender, EventArgs e)
    {
        //when button is clicked, the SQL Database gets updated with the data that is plugged into the datagridview.
        scb = new SqlCommandBuilder(da);
        da.Update(dt);

    }

17,270

Author by

J.S.

Updated on July 17, 2022

Comments

J.S. almost 2 years

I have a datagridview, and some data in the table. The datagridview allows users to enter new rows of data

Question here:

I would like to know how can I get the new inserted rows of data(no matter how many rows have been added) into database without adding the existing data which will then be duplicated.

Anyone?

EDIT: im using sql database, and this is my datagridview.

enter image description here

the data shown is already inside database, now, what if users insert NEW MULTIPLE rows of data into the datagridview? what is the code should I put?

My code as below :

private void button1_Click(object sender, EventArgs e)
        {
            con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = "Data Source=tcp:SHEN-PC,49172\\SQLEXPRESS;Initial Catalog=LSEStock;Integrated Security=True";
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter();

            for (int i = 0; i<dataGridView1.Rows.Count; i++ )
            {

                String insertData = "INSERT INTO CostList(SupplierName, CostPrice, PartsID) VALUES (@SupplierName, @CostPrice, @PartsID)" ;
                SqlCommand cmd = new SqlCommand(insertData, con);
                cmd.Parameters.AddWithValue("@SupplierName", dataGridView1.Rows[i].Cells[0].Value);
                cmd.Parameters.AddWithValue("@CostPrice", dataGridView1.Rows[i].Cells[1].Value);
                cmd.Parameters.AddWithValue("@PartsID", textBox1.Text);
                da.InsertCommand = cmd;
                cmd.ExecuteNonQuery();
            }

            con.Close();