How to save changes to DataView to Database on button click?

Question

I'm new to Asp.net and I'm trying to figure out how to update a value in a SqlDataSource programmatically. Here's my button click listener:

        protected void ApproveLoanButton_Click(object sender, EventArgs e)
    {
        DataView dv = (DataView)DetailsSqlDataSource.Select(DataSourceSelectArguments.Empty);
        dv.AllowEdit = true;
        using (var dt = dv.ToTable())
        {
            var oldValue = dt.Rows[0]["IsApproved"].ToString();
            dt.Rows[0]["IsApproved"] = true;
            var newValue = dt.Rows[0]["IsApproved"].ToString();
            dt.AcceptChanges();
            GridView1.DataBind();
            DetailsView1.DataBind();
        }         

    }

The oldValue is false and the newValue is true so I am changing the value but it doesn't save to the database when I call AcceptChanges(). What am I doing wrong here? I've already spent hours on this. Thanks for your help!

Answer 1

I do not have what your DetailsSqlDataSource is, or what your database structure is like, but you would obviously need to write a few Save and Read methods.

If you did that, you would have something like this:

    protected void ApproveLoanButton_Click(object sender, EventArgs e)
    {
        DataView dv = (DataView)DetailsSqlDataSource.Select(DataSourceSelectArguments.Empty);
        dv.AllowEdit = true;
        using (var dt = dv.ToTable())
        {
            var oldValue = dt.Rows[0]["IsApproved"].ToString();
            if (-1 < SaveApproved((int)dt.Rows[0]["ID"], true))
            {
                dt.Rows[0]["IsApproved"] = true;
                var newValue = dt.Rows[0]["IsApproved"].ToString();
                dt.AcceptChanges();
                DetailsSqlDataSource = GetTable();
                GridView1.DataBind();
                DetailsView1.DataBind();
            }
        }

    }

You will need to modify these to work, but here is a sample:

    private const string SQL_CONNECTION = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

    private DataTable GetTable()
    {
        var table = new DataTable();
        using (var con = new System.Data.SqlClient.SqlConnection(SQL_CONNECTION))
        {
            con.Open();
            using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM MyTable;", con))
            {
                table.Load(cmd.ExecuteReader());
            }
        }
        return table;
    }

    private int SaveApproved(int rowID, bool approved)
    {
        using (var con = new System.Data.SqlClient.SqlConnection(SQL_CONNECTION))
        {
            con.Open();
            using (var cmd = new System.Data.SqlClient.SqlCommand("UPDATE MyTable SET IsApproved=@IsApproved WHERE ID=@ID;", con))
            {
                cmd.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = approved;
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = rowID;
                return cmd.ExecuteNonQuery();
            }
        }
    }