How to update MySql Database from DataGrid on C#?

update and insert data in database from datagridview
update data from datagridview to database c#
how to save datagridview data to database in c# windows application
how to update row in datagridview in c# windows application
c# datagridview edit cell update database
insert update and delete data with datagridview in windows form using c#
how to update database in c#
insert multiple rows from datagridview into database

I'm trying to update MySQL DB from DataGrid, but it isn't working. The added data is not in the database, and there are no errors, why this happens. I have been trying to solve this problem for several days now.

private void dtGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
        {    
        MySqlConnection conn = DBUtils.GetDBConnection();
        string table = "brands";
        string sql = "SELECT * FROM "+table;
        MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(sql, conn);
        conn.Open();
        MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);
        myDataAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
        myDataAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
        myDataAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
        DataSet myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet, table);
        myDataAdapter.AcceptChangesDuringUpdate = true;
        myDataAdapter.Update(myDataSet, table);
        conn.Close();
    }

@Eugene

so here:

DataTable dt = new DataTable();
DataSet ds = new DataSet();
dt = ((DataView)dtGrid.ItemsSource).ToTable() as DataTable;
ds.Tables.Add(dt);

then populating it again with

myDataAdapter.Fill(ds, table);

You are creating a new datatable, then adding an existing data table to it. That is why all rows are getting added again.

Try changing your code to. XAML:

<Grid>
    <TabControl Height="310" HorizontalAlignment="Left" Margin="-1,-1,0,0" Name="tabControl1" VerticalAlignment="Top" Width="763">
        <TabItem Header="Brands" Name="tabBrands">
            <DataGrid ItemsSource="{Binding}" Name="dtGrid" Height="280" Width="750" BeginningEdit="dtGrid_BeginningEdit" RowEditEnding="dtGrid_RowEditEnding" AutoGenerateColumns="True" AutoGeneratingColumn="dtGrid_AutoGeneratingColumn" />               
        </TabItem>
        <TabItem Header="Later" Name="tabItem2">

        </TabItem>
    </TabControl>

</Grid>

C#

public partial class MainWindow : Window
{

    private MySqlDataAdapter myDataAdapter;        
    private DataSet myDataSet;
    private MySqlCommandBuilder myBuilder;
    private MySqlConnection myConn = DBUtils.GetDBConnection();

    public MainWindow()
    {
        InitializeComponent();
        myConn.Open();
        myDataAdapter = new MySqlDataAdapter {SelectCommand=new MySqlCommand() {Connection=myConn, CommandText= "SELECT * FROM brands" } };
        myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet, "brands");           
        dtGrid.DataContext = myDataSet.Tables["brands"].DefaultView;
    }

    private void dtGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)
    {
        myBuilder  = new MySqlCommandBuilder(myDataAdapter);
        DataRowView myDRV = (DataRowView)dtGrid.SelectedItem;
        myDRV.BeginEdit();
    }

    private void dtGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
    {
        DataRowView myDRV = (DataRowView)dtGrid.SelectedItem;
        myDRV.EndEdit();
        myDataAdapter.UpdateCommand = myBuilder.GetUpdateCommand();
        myDataAdapter.Update(myDataSet, "brands");
    }

    private void dtGrid_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
    {
        var tc = e.Column as System.Windows.Controls.DataGridTextColumn;
        var b = tc.Binding as System.Windows.Data.Binding;

        b.UpdateSourceTrigger = UpdateSourceTrigger.PropertyChanged;
        b.ValidatesOnDataErrors = true;
        b.NotifyOnValidationError = true;
    }

}

this loses the buttons, but you can both edit and add rows now without the need for buttons. To add a row go to the blank row at the bottom of the DataGrid.

Update and Insert data in database from datagridview, In this video tutorial you will learn how to update database table from datagrid view. You can Duration: 7:27 Posted: Jul 29, 2013 Looking for mysql online database? Search now! eTour.com is the newest place to search, delivering top results from across the web.


How To Insert Data Into Database and Show in DataGridView In C# , Insert, Update and Delete from DataGridView to sql server,MySql Sql database. Microsoft Duration: 20:21 Posted: Jan 18, 2017 declare you DataAdapter, DataSet and DataView at the top of your page. Use the BeginningEdit handler of your dtGrid to initialise your MySqlCommandBuilder on your declared DataAdapter. Use the RowEditEnding to run the DataAdapter.Update command. e.g.


@Simon, I did differently, at the moment it inserts all the values from the dtGrid, and not just the new ones, maybe I do not share it correctly. For for filling dbGrid:

        public MainWindow()
    {
        InitializeComponent();
        /*string sql = "SELECT * FROM brands";*/
        MySqlConnection conn = DBUtils.GetDBConnection();
        MySqlCommand cmd = new MySqlCommand("select * from brands", conn);
        conn.Open();
        DataTable dt = new DataTable();
        dt.Load(cmd.ExecuteReader());
        conn.Close();
        dtGrid.ItemsSource = dt.DefaultView;
    }

For Insert new items

        private void Insert_Click(object sender, RoutedEventArgs e)
    {
        MySqlConnection conn = DBUtils.GetDBConnection();
        string table = "brands";
        string sql = "SELECT * FROM " + table;
        MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(sql, conn);
        conn.Open();
        MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);
        DataSet myDataSet = new DataSet();
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        dt = ((DataView)dtGrid.ItemsSource).ToTable() as DataTable;
        ds.Tables.Add(dt);
        myDataAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
        myDataAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
        myDataAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
        myDataAdapter.Fill(ds, table);
        myDataAdapter.AcceptChangesDuringUpdate = true;
        myDataAdapter.Update(ds, table);
    }

XAML

<Window x:Class="TechD.MainWindow"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="TechDep" Height="390" Width="902">
<Grid>
    <TabControl Height="310" HorizontalAlignment="Left" Margin="-1,-1,0,0" Name="tabControl1" VerticalAlignment="Top" Width="763">
        <TabItem Header="Brands" Name="tabBrands">
            <DataGrid AutoGenerateColumns="True" Height="280" Name="dtGrid" Width="750" CanUserAddRows="True" ItemsSource="{Binding}" RowEditEnding="dtGrid_RowEditEnding" />
        </TabItem>
        <TabItem Header="Later" Name="tabItem2">

        </TabItem>
    </TabControl>
    <Button Content="Insert" Height="23" HorizontalAlignment="Left" Margin="774,21,0,0" Name="Insert" VerticalAlignment="Top" Width="90" Click="Insert_Click" />
    <Button Content="Button" Height="23" HorizontalAlignment="Left" Margin="774,50,0,0" Name="button2" VerticalAlignment="Top" Width="90" />
    <Button Content="Button" Height="23" HorizontalAlignment="Left" Margin="774,79,0,0" Name="button3" VerticalAlignment="Top" Width="90" />
    <Button Content="Button" Height="23" HorizontalAlignment="Left" Margin="774,108,0,0" Name="button4" VerticalAlignment="Top" Width="90" />
</Grid>

Insert Update and Delete in C# DataGridView, CRUD Operation in C# DataGridView from SQL Server DB. 3. Prevent C# Excercises https Duration: 45:28 Posted: Dec 4, 2017 A DataGridView control can be used to display data from a database. A table can be displayed from a database in a DataGridView, using the DataAdapter class and data logic. You can easily update a database from a DataGridView. Step 1 Create a Windows Forms Application Project, and place a DataGridView on the form. Step 2


Insert, Update and Delete Records in a C# DataGridView, Create a new Windows Forms application. Create a database (named Sample). Add a table tbl_Record. The following is the table schema for  This article shows how to insert, update, delete and display data in MySQL. I have updated the Article and source code on my personal blog. you can get it here . Introduction


Insert, Update, Delete, Display Data in MySQL Using C, This article shows how to insert, update, delete and display data in MySQL. I have updated the Article and source code on my personal blog. Instructions to Solve C# MySQL Connection Problem through MySQL Backup Database At Cognegic we will help you to take care of your C# MySQL association issue and furthermore enable you to recognize the root to cause behind your specialized issues. Here we give MySQL Enterprise Backup support and MySQL Remote Support to recoup from any blackouts.


JBoss: Developer's Guide, MySQL database The beosbank-africa-mysql-ds data source is pointing on the we need to ensure that the target JBoss datagrid endpoint is also up and running. standalone.sh -c=clustered.xml -bmanagement=127.0.0.1 -b=127.0.0.1  Use the following for “btnloaddata_Click"; bind the Data Grid with the Customer data. Now bind the DataGrid in the WPF 4.5 application using a MySQL Database. I hope this article is useful. If you have any other questions then please provide your comments below.