Updating DATAGRID row to save to SQL

Question

Hi Guys I am trying to understand how to save and edited row to the database

private void BudgetGrid_RowEditEnding(object sender,
    DataGridRowEditEndingEventArgs e)
{
    SqlCommand gridcmd = new SqlCommand();
    SqlConnection rwConn = null;
    rwConn = new SqlConnection("server=localhost;" +
    "Trusted_Connection=yes;" + "database=Production; " + "connection
    timeout=30");
    gridcmd.Connection = rwConn;
    rwConn.Open();
    //gridcmd.CommandText =
    //"SELECT Id, Name, Quantity, Rate, Time FROM Budget";
    gridcmd.CommandText =
    "UPDATE Budget SET Id = @id, Name = @Name, " +
    "Quantity = @Qty, Rate = @Rte WHERE Time = @Time";

    SqlDataAdapter gridda = new SqlDataAdapter(gridcmd);
    string strId = "@id".ToString();
    int intID;
    bool bintID = Int32.TryParse(strId, out intID);
    string strName = "@Name".ToString();
    string strQty = "@Qty".ToString();
    int intQty;
    bool bintQty = Int32.TryParse(strQty, out intQty);
    string strRte = "@Rte".ToString();
    int intRte;
    bool bintRte = Int32.TryParse(strRte, out intRte);
    string strTime = "@Time".ToString();
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@id", SqlDbType.Int));
    gridda.SelectCommand.Parameters["@id"].SqlValue = intID;
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@Name", SqlDbType.VarChar));
    gridda.SelectCommand.Parameters["@Name"].SqlValue = strName;
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@Qty", SqlDbType.Int));
    gridda.SelectCommand.Parameters["@Qty"].SqlValue = strQty;
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@Rte", SqlDbType.Int));
    gridda.SelectCommand.Parameters["@Rte"].SqlValue = strRte;
    gridda.SelectCommand.Parameters.Add(
        new SqlParameter("@Time", SqlDbType.VarChar));
    gridda.SelectCommand.Parameters["@Time"].SqlValue = strTime;
    DataTable griddt = new DataTable("Budget");
    gridda.Fill(griddt);
    gridda.UpdateCommand =
        new SqlCommandBuilder(gridda).GetUpdateCommand();
    BudgetGrid.ItemsSource = griddt.DefaultView;
    gridda.Update(griddt);
    rwConn.Close();
}

it displays fine. I can edit its but when I click on the other tab it does not update it goes back to the original data.

Most of the code I have been going through its either out dated.. or not what I am looking for.

so here is the database

and here is the app

so basically if i hit tab to the next row. under the event BudgetGrid_RowEditEnding it should update the database.. but now its not.

could you provide more details?I cannot see your update query. — rai nalasa, May 09 '16 at 13:03
i see everyone using update query in SQL but when i user `cmd = new SqlCommand("UPDATE Budget", uniConn);` it just gives an error — Jared Barrett, May 09 '16 at 13:16

Kay Lee · Accepted Answer · 2016-05-11T08:11:47.217

Just copy below codes. I've created all the thing of you and tested successfully. Rather than the first way, I tried to let you go more popular way. Therefore, it took me time to adopt..

Hope this helps you !

SqlDataAdapter da;
DataTable dt;

    private void Window_Loaded(object sender, RoutedEventArgs e)
    {
        SqlConnection Conn = new SqlConnection();
        Conn.ConnectionString = yourConnectionString;
        Conn.Open();

        SqlCommand gridcomm = new SqlCommand();
        gridcomm.Connection = Conn;

        gridcomm.CommandText = "SELECT Id, Name, Quantity, Rate, Time FROM Budget";

        da = new SqlDataAdapter(gridcomm);

        SqlDataReader gridreader = gridcomm.ExecuteReader();
        while (gridreader.Read())
        {
        }
        gridreader.Close();

        dt= new DataTable("Budget");
        da.Fill(dt);

        dataGrid_Budget.ItemsSource = dt.DefaultView;

        Conn.Close();

    }

    private void dataGrid_Budget_RowEditEnding(object sender, System.Windows.Controls.DataGridRowEditEndingEventArgs e)
    {
        DataGridRow editedrow = e.Row;

        int row_index = (DataGrid)sender).ItemContainerGenerator.IndexFromContainer(editedrow);

        for (int k=0;k< 5;k++)
        {
            DataGridCell cell = GetCell(row_index, k);
            TextBlock tb = cell.Content as TextBlock;

            if (k==1)
            {
                dt.Rows[row_index][k] = tb.Text;
            }
            else if (k == 4)
            {
                if (tb.Text != "")
                {
                    dt.Rows[row_index][k] = Convert.ToDateTime(tb.Text);
                }
            }
            else
            {
                dt.Rows[row_index][k] = Convert.ToInt32(tb.Text);
            }
        }

        da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();

        da.Update(dt);
    }




    public DataGridCell GetCell(int row, int column)
    {
        DataGridRow rowContainer = GetRow(row);

        if (rowContainer != null)
        {
            DataGridCellsPresenter presenter = GetVisualChild<DataGridCellsPresenter>(rowContainer);

            DataGridCell cell = (DataGridCell)presenter.ItemContainerGenerator.ContainerFromIndex(column);
            if (cell == null)
            {
                dataGrid_Budget.ScrollIntoView(rowContainer, dataGrid_Budget.Columns[column]);
                cell = (DataGridCell)presenter.ItemContainerGenerator.ContainerFromIndex(column);
            }
            return cell;
        }
        return null;
    }

    public DataGridRow GetRow(int index)
    {
        DataGridRow row = (DataGridRow)dataGrid_Budget.ItemContainerGenerator.ContainerFromIndex(index);
        if (row == null)
        {
            dataGrid_Budget.UpdateLayout();
            dataGrid_Budget.ScrollIntoView(dataGrid_Budget.Items[index]);
            row = (DataGridRow)dataGrid_Budget.ItemContainerGenerator.ContainerFromIndex(index);
        }
        return row;
    }

    public static T GetVisualChild<T>(Visual parent) where T : Visual
    {
        T child = default(T);
        int numVisuals = VisualTreeHelper.GetChildrenCount(parent);
        for (int i = 0; i < numVisuals; i++)
        {
            Visual v = (Visual)VisualTreeHelper.GetChild(parent, i);
            child = v as T;
            if (child == null)
            {
                child = GetVisualChild<T>(v);
            }
            if (child != null)
            {
                break;
            }
        }
        return child;
    }

Please note that if you move another row before you complete the editing, you will see FormatException but will be ok if you click another row after completion of the editing. You can wrap the code with try{} and then catch(FormatException){MessageBox.Show("Please complete the edit, first !");} or you can do research on Google by good keywords. — Kay Lee, May 10 '16 at 14:33
there is few thing in red.. that it doesnt work.. but you gave... me alot more than i was asking for :D thanks bud. i can see how and where to go about it. — Jared Barrett, May 11 '16 at 07:06
Thanks and marking is enough. Can you tell me the names of red color? If you put mouse on it, Visual Studio automatically suggest you to correct the problems. In additional 3 methods, the red colored things can be disappeared by just following the suggestion by Visual Studio which is adding some needed assembly and then using namespace of it to use 3 additional methods. — Kay Lee, May 11 '16 at 07:15
I upvoted your question to cheer up for your project. If there's something you want to know about this case, ask whatever, whenever you want. Thank you. — Kay Lee, May 11 '16 at 07:19

score 0 · Answer 2 · edited Aug 02 '21 at 10:58

0

SqlConnection uniConn = null;
SqlCommand cmd = null;
SqlDataAdapter sda = null;
DataTable dt = new DataTable();
uniConn = new SqlConnection(
    "server=localhost;" + "Trusted_Connection=yes;" +
    "database=Production; " + "connection timeout=30");
cmd = new SqlCommand("UPDATE Budget(id, Name, Quantity, Rate, Time)",
    uniConn);
uniConn.Open();
sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
BudgetGrid.ItemsSource = dt.DefaultView;
uniConn.Close();

Did you forget to close the connection?

edited Aug 02 '21 at 10:58

Toni

1,555
4
15
23

answered May 09 '16 at 13:20

rai nalasa

849
1
12
32

Please mark this as an answer if this code example helped you. – rai nalasa May 09 '16 at 13:24
this is not what i am looking for.. ill show you. update the post. – Jared Barrett May 09 '16 at 13:35
did you forgot to close the connection? – rai nalasa May 09 '16 at 13:38
no you can see the connection is closed `uniConn.Close();` – Jared Barrett May 09 '16 at 13:41
I added that on my answer.You don't have `uniConn.Close();` In your actual post. – rai nalasa May 09 '16 at 13:43
my bad :D ill try quickly. – Jared Barrett May 09 '16 at 13:47
nope the error `{"Must declare the scalar variable \"@UPDATE\"."}` is now given which is caused by not having `SET id= @u_id` – Jared Barrett May 09 '16 at 13:48
did you try to add **close** on all your **open** statement?cause I can see two **Open** on your codes. – rai nalasa May 09 '16 at 13:50
one is on `Budget_GridLoaded` – rai nalasa May 09 '16 at 13:52
Also try to add where clause – rai nalasa May 09 '16 at 13:53
I have closed them both. but its not throwing an error for that. i just want to do what Kay Lee said with updating the row. – Jared Barrett May 09 '16 at 13:59

score 0 · Answer 3 · answered May 09 '16 at 13:27

0

Your SQL syntax has to be corrected like,

SqlCommand update_comm = new SqlCommand();
update_comm.Connection = Conn;
update_comm.CommandText = "UPDATE Budget SET id= @u_id, Name= @u_name  WHERE person= @psn";

var update_da = new SqlDataAdapter(update_comm);
update_da.SelectCommand.Parameters.Add(new SqlParameter("@u_id", SqlDbType.Int));
update_da.SelectCommand.Parameters["@u_id"].Value = yourintvalue;

update_da.SelectCommand.Parameters.Add(new SqlParameter("@u_name", SqlDbType.NVarChar));
update_da.SelectCommand.Parameters["@u_name"].Value = yourstringvalue;

update_da.SelectCommand.Parameters.Add(new SqlParameter("@psn", SqlDbType.NVarChar));
update_da.SelectCommand.Parameters["@psn"].Value = yourstringvalue;

var update_ds = new DataSet();
update_da.Fill(update_ds);

'UPDATE' should be used with 'SET' together.

And if you want to update the actual SQL database with the value of edited rows of DataGrid, please try this.

da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();

da.Update(griddt);

answered May 09 '16 at 13:27

Kay Lee

922
1
12
40

i dont understand the WHERE part.. if i want it to take the entire row... and update it il post a pic – Jared Barrett May 09 '16 at 13:33
the second part is what i want to do but where is DA declared? and where would i used it? – Jared Barrett May 09 '16 at 13:46
hmm..second part might be applied to update entire table. Because you're trying with RowEditEnding, the WHERE should be id= @k_id to indicate the row edited. My suggestion is to declare datatable as global variable in the field of a class(outside of a Event) to share the value between Events or Methods. And then you edit the DataGrid(datatable is inside), and run UPDATE command with edited value like dt.Rows[0][1] which means a value of first row and second column(zero based index). Update manually and load the datas from SQL database manually. Otherwise, you need to try on second part. – Kay Lee May 09 '16 at 14:03
I'm not familliar with SqlCommandBuilder. Sorry but the where should be local as you use or as global variable I commented. The direction of my application code went another way making me unfamiliar with this. However, if you're interested, you need to do research and can refer to my case as here http://stackoverflow.com/questions/35835567/how-to-change-values-of-column-of-datatable-and-show-only-in-datagrid-not-updati I loaded DataTable into DataGrid and changed the values and ran the SqlCommandBuilder code. The actual SQL database was updated as of the title of my question. – Kay Lee May 09 '16 at 14:16
I recommend you to get the DataTable dt = new DataTable(); out of loaded event and to declare in the field of Class as global variable so that you can utilize the initial values of DataTable or edited values. If you update SQL database manually and load the datas of SQL database into DataGrid(DataTable) manually, this should work because it's basic and direct way. Now, I also tested declaring DataTable outside event as a field of Class like DataTable dt_budget; – Kay Lee May 10 '16 at 10:02
You only need to declare the type as DataTable only 1 time in the field of Class and then you can simply use like dt_budget.Rows[0][1] everywhere as explained already. Get the edited values through this way and run UPDATE query with the obtained values as value of parameters. To load the datas again, extract only codes from loaded event and paste it just below the end of SQL UPDATE query in the same button click event – Kay Lee May 10 '16 at 10:03
Umm bud, could you show me with an example in code so i can better understand what it is you mean? – Jared Barrett May 10 '16 at 11:02
Ok, please wait 1 hour because I'm taking dinner. – Kay Lee May 10 '16 at 11:28

Updating DATAGRID row to save to SQL

3 Answers3

Linked