We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Datagrid only update first row in MySql database

Thread ID:

Created:

Updated:

Platform:

Replies:

149029 Nov 12,2019 11:26 AM UTC May 6,2020 05:41 AM UTC WinForms 16
loading
Tags: SfDataGrid
Carlos
Asked On November 12, 2019 11:26 AM UTC

Hi,

I create a small project with sfDataGrid, code bellow

If I use this strategy to update the database with the NetFramework DataGridView component or with DevExpress GridControl, all work fine, but with sfDataGrid control, only when I change the first row in the grid, the database is updated, all other rows don't update the database, for example, if the row with id=2 is the second row, the changes are not updated in the database, but after sort, the row with id=2 is the first row, then the changes are updated to the database.

I really need help, please.

using MySql.Data.MySqlClient;
using Syncfusion.Windows.Forms;
using System;
using System.Data;

using System.Windows.Forms;

namespace WindowsFormsApp7
{
    public partial class Form1 : MetroForm
    {
        private const string ConnectionString = "server=127.0.0.1;port=3306;database=lictestetopg;uid=root2;password=root2;";

        private MySqlDataAdapter da = null;
        private MySqlCommandBuilder cmdb = null;
        private DataTable dt = null;
        private BindingSource bs = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                string query = $"SELECT email, password, name, age, status FROM users";
                da = new MySqlDataAdapter(query, ConnectionString);
                cmdb = new MySqlCommandBuilder(da);
                dt = new DataTable();
                bs = new BindingSource();
                da.Fill(dt);
                bs.DataSource = dt;
                sfDataGrid1.DataSource = bs;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "FAC", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void UpdateDataToDatabase()
        {
            try
            {
                bs.SuspendBinding();
                int rowsUp = da.Update(dt);

                if (rowsUp == 0)
                {
                    dt.RejectChanges();
                }
                else
                {
                    dt.AcceptChanges();
                }
            }
            catch (System.Data.DBConcurrencyException concEx)
            {
                dt.RejectChanges();

                MessageBox.Show(concEx.Message, "FAC", MessageBoxButtons.OK, MessageBoxIcon.Error);

                sfDataGrid1.AllowEditing = false;
            }
            catch (MySqlException sqlEx)
            {
                dt.RejectChanges();

                MessageBox.Show(sqlEx.Message, "FAC", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (System.Exception ex)
            {
                dt.RejectChanges();

                MessageBox.Show(ex.Message, "FAC", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                bs.ResumeBinding();
            }
        }

        private void sfDataGrid1_RowValidated(object sender, Syncfusion.WinForms.DataGrid.Events.RowValidatedEventArgs e)
        {
            UpdateDataToDatabase();
        }
    }
}

Carlos
Replied On November 12, 2019 11:54 AM UTC

Hi,

The problem is with sfDataGrid1_RowValidated event, with sfDataGrid1_CurrentCellEndEdit event, work fine.

I don't want update the database when the cell is changed, only when the entire row is validated or updated, what the best event to this?

Thanks.

Shobika Palani [Syncfusion]
Replied On November 12, 2019 01:00 PM UTC

Hi Carlos, 

Thank you for contacting Syncfusion Support. 

We have analyzed your query to update the database when cell value changes and only when entire row is updated. This requirement can be achieved by setting flag in RowValidated event and updating the database in CurrentCellEndEdit event based on the flag. Please refer to the below code snippet  
bool isRowValidated; 
private void SfDataGrid_RowValidated(object sender, Syncfusion.WinForms.DataGrid.Events.RowValidatedEventArgs e) 
        { 
            isRowValidated = true; 
        } 
private void SfDataGrid_CurrentCellEndEdit(object sender, Syncfusion.WinForms.DataGrid.Events.CurrentCellEndEditEventArgs e) 
        { 
            if (isRowValidated) 
            { 
                UpdateDataToDatabase(); 
                isRowValidated = false; 
            } 
        } 

Please let us know, if you require any further assistance on this. 

Regards,
Shobika. 


Carlos
Replied On November 12, 2019 05:35 PM UTC

Hi,

Thanks, now work, but now how I add a row to the database? I use the AddNewRowPosition equal to FixedTop, how I get the newly added row to commit to database, what event fires this action?

Thanks, and sorry for my English.

Mohanram Anbukkarasu [Syncfusion]
Replied On November 13, 2019 10:18 AM UTC

Hi Carlos, 

Thanks for your update. 

You can achieve your requirement to commit the newly added row to the database by using SfDataGrid.View.Records.CollectionChanged event. Please refer the following code example. 

Code example :  

this.sfDataGrid.View.Records.CollectionChanged += OnRecords_CollectionChanged; 
 
private void OnRecords_CollectionChanged(object sender, NotifyCollectionChangedEventArgs e) 
{ 
     if (e.Action == System.Collections.Specialized.NotifyCollectionChangedAction.Add) 
         UpdateDataToDatabase(); 
} 
    
Please let us know if you need further assistance from us. 

Regards, 
Mohanram A. 


Carlos
Replied On November 13, 2019 12:47 PM UTC

Hi,

Now when after add the row the application breaks with a error in Program.cs, in line

Application.Run(new Form1());

The error is:

"An unhandled exception of type 'System.InvalidOperationException' occurred in Syncfusion.SfDataGrid.WinForms.dll
RowData is null in Find is not valid operation"

but the row is inserted in the database




Mohanram Anbukkarasu [Syncfusion]
Replied On November 14, 2019 01:09 PM UTC

Hi Carlos,  

Thanks for your update.   

Please provide us the stack trace details of the exception occurred in your application. It will be helpful for us to find the possibilities for the exception and to provide a proper solution at earlier.   

Regards,  
Mohanram A.  


François-Xavier
Replied On April 29, 2020 07:47 PM UTC

Hi,

I'm sorry to update this post but i beginning with syncfusion and the code of carlos is very useful.

But i have the same problem when i add new row.

"An unhandled exception of type 'System.InvalidOperationException' occurred in Syncfusion.SfDataGrid.WinForms.dll
RowData is null in Find is not valid operation"

My stack trace is :

   à Syncfusion.WinForms.DataGrid.Interactivity.SelectedItemsCollection.FindRowData(Object rowData)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.AddSelection(Object recordEntry, Int32 rowIndex)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.AddSelectionForSelectedItem(RowColumnIndex rowColumnIndex, RowColumnIndex previousRowColumnIndex, Boolean isDragSelection, Boolean isNavigationSelection)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.ProcessSelection(RowColumnIndex rowColumnIndex, RowColumnIndex previousRowColumnIndex, Boolean isDragSelection, Boolean isNavigationSelection)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.ProcessSelectionAndCurrentCell(RowColumnIndex rowColumnindex)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.ProcessEnterKey()
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.HandleKeyOperations(KeyEventArgs args)
   à Syncfusion.WinForms.DataGrid.Renderers.GridTextBoxCellRenderer.OnKeyDown(DataColumnBase dataColumn, RowColumnIndex rowColumnIndex, KeyEventArgs e)
   à Syncfusion.WinForms.DataGrid.GridHandler.OnKeyDown(Object sender, KeyEventArgs e)
   à Syncfusion.WinForms.DataGrid.Renderers.GridTextBoxCellRenderer.OnUiElementKeyDown(Object sender, KeyEventArgs e)
   à System.Windows.Forms.Control.OnKeyDown(KeyEventArgs e)
   à System.Windows.Forms.Control.ProcessKeyEventArgs(Message& m)
   à System.Windows.Forms.Control.ProcessKeyMessage(Message& m)
   à System.Windows.Forms.Control.WmKeyChar(Message& m)
   à System.Windows.Forms.Control.WndProc(Message& m)
   à System.Windows.Forms.TextBoxBase.WndProc(Message& m)
   à System.Windows.Forms.TextBox.WndProc(Message& m)
   à System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   à System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   à System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   à System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   à System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   à System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   à System.Windows.Forms.Application.Run(Form mainForm)

Can you help me?


Vijayarasan Sivanandham [Syncfusion]
Replied On April 30, 2020 02:20 PM UTC

Hi Francois-Xavier,

Thank you for contacting Syncfusion support.

Can you please confirm, which version you are using currently?

Regards,
Vijayarasan S
 


François-Xavier
Replied On April 30, 2020 02:25 PM UTC

Hi Vijayarasan,

I'm using essential studio version 18.1.0.42 with vs 2019 and .net framework 4.6

Vijayarasan Sivanandham [Syncfusion]
Replied On April 30, 2020 05:05 PM UTC

Hi Francois-Xavier,

Thanks for the update
 

We have provide the SfDataGrid.Winforms.dll custom assembly for check the issue. Please find the custom assembly from below link,
 
 
Custom assemblies: https://www.syncfusion.com/downloads/support/forum/149029/ze/18.1.0.42-1628899108

We appreciate your patience until then.
 
 
Regards,
Vijayarasan S


François-Xavier
Replied On April 30, 2020 06:41 PM UTC

Vijayarasan,

Always same error :

à Syncfusion.WinForms.DataGrid.Interactivity.SelectedItemsCollection.FindRowData(Object rowData)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.AddSelection(Object recordEntry, Int32 rowIndex)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.AddSelectionForSelectedItem(RowColumnIndex rowColumnIndex, RowColumnIndex previousRowColumnIndex, Boolean isDragSelection, Boolean isNavigationSelection)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.ProcessSelection(RowColumnIndex rowColumnIndex, RowColumnIndex previousRowColumnIndex, Boolean isDragSelection, Boolean isNavigationSelection)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.ProcessSelectionAndCurrentCell(RowColumnIndex rowColumnindex)
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.ProcessEnterKey()
   à Syncfusion.WinForms.DataGrid.Interactivity.RowSelectionController.HandleKeyOperations(KeyEventArgs args)
   à Syncfusion.WinForms.DataGrid.Renderers.GridTextBoxCellRenderer.OnKeyDown(DataColumnBase dataColumn, RowColumnIndex rowColumnIndex, KeyEventArgs e)
   à Syncfusion.WinForms.DataGrid.GridHandler.OnKeyDown(Object sender, KeyEventArgs e)
   à Syncfusion.WinForms.DataGrid.Renderers.GridTextBoxCellRenderer.OnUiElementKeyDown(Object sender, KeyEventArgs e)
   à System.Windows.Forms.Control.OnKeyDown(KeyEventArgs e)
   à System.Windows.Forms.Control.ProcessKeyEventArgs(Message& m)
   à System.Windows.Forms.Control.ProcessKeyMessage(Message& m)
   à System.Windows.Forms.Control.WmKeyChar(Message& m)
   à System.Windows.Forms.Control.WndProc(Message& m)
   à System.Windows.Forms.TextBoxBase.WndProc(Message& m)
   à System.Windows.Forms.TextBox.WndProc(Message& m)
   à System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   à System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   à System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   à System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   à System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   à System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   à System.Windows.Forms.Application.Run(Form mainForm)

I send you a screenshot of SfDatagrid properties

Thank you for your help.

Regards,

Attachment: Capture_bug_dll_sfdatagrid_791f08d1.rar

Mohanram Anbukkarasu [Syncfusion]
Replied On May 1, 2020 01:34 PM UTC

Hi François-Xavier, 

Sorry for the inconvenience. 

We suspect that the custom assembly we have provided may not be referred properly. Due to some technical hurdles we are unable to provide patch on that day. Now we have created a patch in your version 18.1.0.42.  


Kindly apply this patch and let us know whether the issue still exists. 

Regards, 
Mohanram A. 


François-Xavier
Replied On May 1, 2020 01:50 PM UTC

Hi Mohanram,

I've change the dll and execute the assembly patch.

I've have always an error but the stack trace is different :

   à Syncfusion.WinForms.DataGrid.Interactivity.SelectedItemsCollection.FindRowData(Object rowData)
   à Syncfusion.WinForms.DataGrid.RowGenerator.CheckForSelection(DataRowBase row)
   à Syncfusion.WinForms.DataGrid.RowGenerator.UpdateRow(IEnumerable`1 rows, Int32 rowIndex)
   à Syncfusion.WinForms.DataGrid.RowGenerator.EnsureRows(VisibleLinesCollection visibleRows)
   à Syncfusion.WinForms.DataGrid.GridPainter.EnsureItems(Boolean ensureColumns)
   à Syncfusion.WinForms.DataGrid.GridPainter.OnPaint(PaintEventArgs e, Size clientSize)
   à Syncfusion.WinForms.DataGrid.TableControl.OnPaint(PaintEventArgs e)
   à System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs e, Int16 layer)
   à System.Windows.Forms.Control.WmPaint(Message& m)
   à System.Windows.Forms.Control.WndProc(Message& m)
   à Syncfusion.WinForms.Controls.SfScrollControl.WndProc(Message& msg)
   à System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   à System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   à System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Regards,

Vijayarasan Sivanandham [Syncfusion]
Replied On May 4, 2020 05:51 PM UTC

Hi Francois-Xavier,  
   
Thank you for your update.  
   
We have prepared the test patch in 18.1.0.42 version. You can download the patch from the following location. Can you please check this test patch and revert us whether your reported issue was resolved or not?  
Regards,
Vijayarasan S



François-Xavier
Replied On May 5, 2020 08:15 AM UTC

Hi Vijayarasan,

All work fine !!!!

Thank you very much for your help.

See you soon for new problem ;)

Regards,

Fx

Vijayarasan Sivanandham [Syncfusion]
Replied On May 6, 2020 05:41 AM UTC

Hi Francois-Xavier, 
 
Thanks for the update. 
 
We are glad to know that the reported problem has been resolved at your end. Please let us know if you have any further queries on this. We are happy to help you😊. 
 
Regards, 
Vijayarasan S 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon