C# Updating Database Table with DataGridView

Question

Question for you:

I am using Visual Studio 2015 to make a Windows Form Application. The goal is just to be able to edit a simple table in a netezza database using a data grid viewer control.

I put the data grid viewer control on my form, created a data source that points to the table, and then pointed the data grid view to that table. It works fine when I run it and displays the 10 or so rows of data perfectly.

Is there an easy way to update the data from the datagridview? I see there are editing/locking options but even with all of them set appropriately, if I edit a value in the cell of the grid and hit enter, it does not go back and update the database. Do I need to manually code for this? I wasn't sure if the grid has a way to do it automatically. In the code I checked all the methods for the tableadapter and didn't find a .Update or anything like that.

some advice would be greatly appreciated. Thank you!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SBTForceClose
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();

    }
    private void LoadGrid()
    {
        dataGridView1.DataSource = dataSet1.LKP_SBT_FORCE_CLOSE;
        dataGridView1.Refresh();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        LoadGrid();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'dataSet1.LKP_SBT_FORCE_CLOSE' table. You can move, or remove it, as needed.
        this.lKP_SBT_FORCE_CLOSETableAdapter.Fill(this.dataSet1.LKP_SBT_FORCE_CLOSE);

    }

    private DataRow LastDataRow = null;

    private void UpdateRowToDatabase()
    {
        if (LastDataRow != null)
        {
            if (LastDataRow.RowState == DataRowState.Modified)
            {
                this.lKP_SBT_FORCE_CLOSETableAdapter.Update(this.LastDataRow);
            }
        }

    }

    private void lKPSBTFORCECLOSEBindingSource_PositionChanged(object sender, EventArgs e)
    {
        BindingSource thisBindingSource = (BindingSource)sender;
        DataRow ThisDataRow = ((DataRowView)thisBindingSource.Current).Row;
        if (ThisDataRow == LastDataRow)
        {
            throw new ApplicationException();
        }
        UpdateRowToDatabase();
        LastDataRow = ThisDataRow;
    }
}
}

score 2 · Answer 1 · answered Feb 08 '17 at 14:52

There is no built in solution to solve what you are trying to accomplish. And you will have to write several lines of code to make it work. Here is a link that will drive you along the whole process:

https://www.codeproject.com/Articles/12846/Auto-Saving-DataGridView-Rows-to-a-SQL-Server-Data

Anyway, IMHO I will not recommend going to the database whenever the user changes a value on your DataGridView since it will make a call to the database everytime the user changes something and move through the control. This may not be a problem in a little application, but as your application grows, I prefer to have this things under control. I find a better approach having a SAVE button or firing an event when the input process has finished, and doing my database work on that instance.

So, I followed this guide and it doesn't throw any errors, however it doesn't update the database. I just updated my post to have my code for the form. I put breakpoints throughout and when I edit a cell in the datagridviewer it does go to the updatedaterowtodatabase method however it doesn't update the table. Am I doing something wrong? It seems like it's all working fine but it won't update the darn table! so frustrating! — user3494110, Feb 08 '17 at 17:12

score 2 · Answer 2 · edited May 23 '17 at 12:33

2

This is possible by the usage of a DataAdapter. You can find the documentation at Updating Data Sources with DataAdapters.

Also there is a short example at Update database with changes made to DataTable… confusion.

Possible drivers can be found at IBM.

edited May 23 '17 at 12:33

Community

1
1

answered Feb 08 '17 at 14:52

Fruchtzwerg

10,999
12
40
49

score 0 · Answer 3 · answered Feb 08 '17 at 15:12

well the best way is to create a buttun

    System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BD\RepertoireDrapor.accdb";
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE Contact SET VAL1 = @VAL1, VAL2 = @VALE2 WHERE VAL3 = @VAL3";
            cmd.Parameters.AddWithValue("@VAL1", VAL1);
            cmd.Parameters.AddWithValue("@VAL2", VAL2);
            cmd.Parameters.AddWithValue("@VAL3", VAL3);
            cmd.Connection = conn;
            conn.Open();
            cmd.ExecuteNonQuery();

And on your datagridview you recover values like

 private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {

        dataGridView1.RowsDefaultCellStyle.SelectionBackColor = Color.Red;
        rowselect = e.RowIndex;
        VAL3 = dataGridView1.Rows[rowselect].Cells[0].Value.ToString();
        VAL1 = dataGridView1.Rows[rowselect].Cells[1].Value.ToString();
        VAL2= dataGridView1.Rows[rowselect].Cells[2].Value.ToString();

    }

Hope its will help you

C# Updating Database Table with DataGridView

3 Answers3