Update sql database using dataset

Question

I need some help to complete this. I have searched and tried several ways but is not enetring in my head yet. It is not homework! I am a self learner.

I managed to populate a grid selecting the table from a dropdown:

private void radDropDownList1_SelectedIndexChanged(object sender, Telerik.WinControls.UI.Data.PositionChangedEventArgs e)
    {
        if (radDropDownList1.SelectedIndex > 0)
        {
            radGridView1.Visible = true;
            label8.Text = radDropDownList1.SelectedValue.ToString();
            DataTable dt = new DataTable();

            var selectedTable = radDropDownList1.SelectedValue;  //Pass in the table name
            string query = @"SELECT * FROM " + selectedTable;

            using (var cn = new SqlConnection(connString))
            {
                cn.Open();
                try
                {
                    SqlCommand cmd = new SqlCommand(query, cn);
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                }
                catch (SqlException ex)
                {
                    //MessageBox.Show(ex.StackTrace);
                }
            }

            radGridView1.DataSource = dt;

    }

Now I am trying to write the event to update the sql table using the grid as datasource:

private void radGridView1_CellEndEdit(object sender, Telerik.WinControls.UI.GridViewCellEventArgs e)
    {   
        DataTable dt = (DataTable)radGridView1.DataSource;
            DataSet ds = new DataSet();
            ds.Tables[0] = dt;

        try
         {
            //**I am lost here!**
         }
        catch
        {

        }
    }

Could you please help? How can I now update the sql table?

You use another SqlCommand with the appropriate update statement... you haven't really given enough information about what you are trying to do for people to help you. — eddie_cat
– eddie_cat, Commented Oct 23, 2014 at 13:12
What do you intend to do with the data when you finish editing a grid cell? If you want to update the corresponding field in your database to what was updated in the grid, you could try keeping a reference to the "old" label and then update table set field = 'new data' where field = 'old data'. Or you could use the ID of the row if you are putting that into the data grid. Note you should parameterize your queries... people have explained this about five million times on here, just Google it and you will find haha :) — eddie_cat
– eddie_cat, Commented Oct 23, 2014 at 13:15
@eddie_cat I would like to update the sql table with the new value edited in the grid cell — user3855329
– user3855329, Commented Oct 23, 2014 at 13:17

T McKeown · Accepted Answer · 2014-10-23 13:22:09Z

1

I don't really like the way you are managing the updates but... this code here will send an update statement:

        using (var cn = new SqlConnection(connString))
        {
            cn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand("UPDATE YourTable SET Column = @Parm1 WHERE Col = @Parm2", cn);
                var parm1 = cmd.CreateParameter("Parm1");
                parm1.Value =  "SomeValue";
                var parm2 = cmd.CreateParameter("Parm2");
                parm2.Value =  "SomeOtherValue";
                cmd.Parameters.Add(parm1);
                cmd.Parameters.Add(parm2);
                int rowsAffected = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                //MessageBox.Show(ex.StackTrace);
            }
        }

answered Oct 23, 2014 at 13:22

T McKeown

12.9k1 gold badge27 silver badges35 bronze badges

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

4 Comments

user3855329 Over a year ago

Thanks a lot. Then with this code I do not need the datatable and the datset I was setting in my second part of the code in the question. Correct?

T McKeown Over a year ago

not sure, if you are using them for display then you would but my code above only requires that you provide the parameter values, so if you can get them without using those objects then you are right..

T McKeown Over a year ago

you may have to set the DbType property on these params and perhaps the Size etc... but this code will work if your SQL is valid.

user3855329 Over a year ago

Thanks. Now I need to get the values from the modified row/cell! However, I appreciated your directions.

Collectives™ on Stack Overflow

Update sql database using dataset

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related