CODEWITHSUNDEEP
c#databasewinformsvisual-studio-2015
user3494110
user3494110

Reputation: 427

C# Updating Database Table with DataGridView

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

Upvotes: 0

Views: 5304

Answers (3)

Fawzidi
Fawzidi

Reputation: 74

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

Upvotes: 0

Fruchtzwerg
Fruchtzwerg

Reputation: 11399

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.

Upvotes: 2

NicoRiff
NicoRiff

Reputation: 4883

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.

Upvotes: 2

Related Questions