CODEWITHSUNDEEP
c#databasedatagridview
J.S.
J.S.

Reputation: 83

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

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();

Upvotes: 3

Views: 18308

Answers (4)

DHB
DHB

Reputation: 161

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);

    }

Upvotes: 1

briskovich
briskovich

Reputation: 690

I do something like this. Quick easy and seems to work fine : 

cmd1.Connection = this.sqlConnection1;
this.sqlConnection1.Open();

 foreach (GridViewRow row in your_grid.Rows)
        {
            Label id = (Label)row.FindControl("your_control"); //what ever control you are using

            SqlCommand cmd1 = new SqlCommand();

            cmd1.CommandText = "insert into your_table values (@p,@p1,@p2)";

            cmd1.Parameters.Add("@p", SqlDbType.VarChar).Value = your_control.Text;
            cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = your_control.Text;
            cmd1.Parameters.Add("@p2", SqlDbType.VarChar).Value = your_control.Text;


            cmd1.CommandType = CommandType.Text;


            cmd1.ExecuteNonQuery();

        }
            this.sqlConnection1.Close();

Upvotes: 0

LOZ
LOZ

Reputation: 1177

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();
                }
            }

Upvotes: 1

LOZ
LOZ

Reputation: 1177

what kind of database you have? Here is my source code for oracle database. If its a SQL database replace the : with @.

Source code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ProjectCISConnectionString %>" ProviderName="<%$ConnectionStrings:ProjectCISConnectionString.ProviderName %>"
            SelectCommand="SELECT * FROM CIS.CIS_TRANS ORDER BY ID ASC" 
            DeleteCommand="DELETE FROM CIS.CIS_TRANS WHERE ID = :ID"
            InsertCommand="INSERT INTO CIS.CIS_TRANS (TRANS_CD,FUND_CD,BSA_CD,DP_TYPE,TRANS_CD_DESC) VALUES (:TRANS_CD,:FUND_CD,:BSA_CD,:DP_TYPE,:TRANS_CD_DESC)"
            UpdateCommand="UPDATE CIS.CIS_TRANS SET TRANS_CD = :TRANS_CD, FUND_CD = :FUND_CD, BSA_CD = :BSA_CD, DP_TYPE = :DP_TYPE, TRANS_CD_DESC =:TRANS_CD_DESC WHERE ID = :ID">

        </asp:SqlDataSource>

Your also going to need a query page and some other stuff, please let me know more information.

Upvotes: 0

Related Questions