Add new row to datagridview

[MUHAMMED DÖNMEZ]

Hello syncfusion team,

I am using Datagrid view to add some data from combox and text fields then from datagridview to my database..

In past I used normal Datagridview to add and its simply added without any problems, but now I am using syncfusion datagridview and I have some problems I hope to help..

I wiil attach some pictures for my code and my design to let you know what I am doing excatly..

This is my form regardless its in arabic ..

and this is the datagridview constructure

and this is my code ...

The problem that when I added the rows only appear in dgv system.string[] like this ,

and when I add only one field like 

Dgv.View.Records.Add(xRow[1]);     

for example all the columns had the same value

can you help with this..

And I want to retrive the data from dgv later to save it in my database..

I am using visual studio 2022 and c#

Thanks for respond, hope to hear form you sooner..

[Malini Selvarasu]

Hi  MUHAMMED DÖNMEZ,

Thank you for providing detailed information about your requirements and the issues you are facing. This clarity has greatly helped us understand your needs.

We have reviewed the scenario you reported. It appears that you have attempted to add records at runtime using the `SfDataGrid.View.Records.Add()` method. We would like to inform you that handling the View.Records collection is not recommended. Instead, you can add the records directly to the DataGrid's DataSource object.

Additionally, we noticed that you are using unbound columns, which are not linked to data objects from the underlying data source. To update the data for unbound columns, please utilize the QueryUnboundColumnInfo event. This event allows for the proper population of data within unbound columns.

 To assist you further, we have prepared a sample demonstrating this approach and have attached it for your review. Kindly examine the attached sample and code snippet. We have also included a user guide link regarding unbound columns for your reference.

code snippet:

  private void SfDataGrid1_QueryUnboundColumnInfo(object sender, Syncfusion.WinForms.DataGrid.Events.QueryUnboundColumnInfoArgs e)

  {

      if (e.UnboundAction == UnboundActions.QueryData)

      {

          var currentRowIndex=sfDataGrid1.TableControl.ResolveToRowIndex(e.Record);

          var recordIndex= sfDataGrid1.TableControl.ResolveToRecordIndex(currentRowIndex);

          var dataRow = dataTable.Rows[recordIndex];

          e.Value = dataRow[e.Column.MappingName];

      }

  }

private void Add_Click(object sender, EventArgs e)

{

    dataTable.Rows.Add(OrderID.Text,

                CustomerName.SelectedItem.ToString(),

                CustomerID.SelectedItem.ToString(),

                Country.Text,

                UnitPrice.Text);

}

UG Link : Unbound Column in Windows Forms DataGrid control | Syncfusion

If we have misunderstood your requirement, please provide us with more details about your requirement or the issue you are facing. This additional information will help us better understand the problem and proceed to provide an appropriate solution.

Thank you for your understanding and cooperation.

Regards,

Malini Selvarasu

Attachment: SfDataGrid_Demo_525b0fac.zip

[MUHAMMED DÖNMEZ]

Thank you for your update.. I hope that my codes is clear..

I will try your solutions and reply to you asson possible..

Regards 

[MUHAMMED DÖNMEZ]

Hello again .

Thanks alot it works perfectly, but I still have two issues..

Do you have any idea to remove a selection row after added,

and also store it in database 

I have try to store it in database but only one record stored (the last record in dgv)..

This is my dbClass:

public class ClsDb
{
    public ClsDb()
    {


    }


    #region Connection


    MySqlConnection xMainConn = null;

    public bool CheckConn()
    {
        try
        {
            SetDb.Default.DbFullPath = "Server='" + SetDb.Default.Server +
                                        "';Database='" + SetDb.Default.DbName +
                                        "';Uid='" + SetDb.Default.User +
                                        "';Pwd='" + SetDb.Default.Pass +
                                        "';CharSet='" + SetDb.Default.Charset +
                                        "';Port='" + SetDb.Default.Port + "'";
            xMainConn = new MySqlConnection(SetDb.Default.DbFullPath);
            xMainConn.Close();
            xMainConn.Open();
            return true;
        }
        catch
        {
            return false;
        }
    }


    #endregion


    #region Data Operation (Select + RunSql)


    //SELECT
    public void Select(string xSql, MySqlParameter[] xPar, DataTable xDt)
    {
        // Create Conn
        using (MySqlConnection xConn = new MySqlConnection(SetDb.Default.DbFullPath))
        {
            //Create Command
            using (MySqlCommand xCmd = new MySqlCommand())
            {
                xCmd.Connection = xConn;
                xCmd.CommandType = CommandType.Text;
                xCmd.CommandText = xSql;
                //Par Loop
                if (xPar != null)
                {
                    xCmd.Parameters.AddRange(xPar);
                }
                //Create DataAdaptor
                using (MySqlDataAdapter xDa = new MySqlDataAdapter(xCmd))
                {
                    xDa.Fill(xDt);
                }
            }
        }
    }


    //Run Sql
    public void RunSql(string xSql, MySqlParameter[] xPar)
    {
        // Create Conn
        using (MySqlConnection xConn = new MySqlConnection(SetDb.Default.DbFullPath))
        {
            //Create Command
            using (MySqlCommand xCmd = new MySqlCommand())
            {
                xConn.Open();
                xCmd.Connection = xConn;
                xCmd.CommandType = CommandType.Text;
                xCmd.CommandText = xSql;
                //Par Loop
                if (xPar != null)
                {
                    xCmd.Parameters.AddRange(xPar);
                }
                //Execute
                xCmd.ExecuteNonQuery();
            }
        }
    }


    //Run Sql
    public Int64 RunSqlScalar(string xSql, MySqlParameter[] xPar)
    {
        // Create Conn
        using (MySqlConnection xConn = new MySqlConnection(SetDb.Default.DbFullPath))
        {
            //Create Command
            using (MySqlCommand xCmd = new MySqlCommand())
            {
                xConn.Open();
                xCmd.Connection = xConn;
                xCmd.CommandType = CommandType.Text;
                xCmd.CommandText = xSql;
                //Par Loop
                if (xPar != null)
                {
                    xCmd.Parameters.AddRange(xPar);
                }
                //Execute
                object xResult = xCmd.ExecuteScalar();
                if (xResult != null && xResult != DBNull.Value)
                {
                    return Convert.ToInt64(xResult);
                }
                else
                {
                    return 0;
                }
            }
        }
    }


    #endregion
}


And this is my sqClasses:

​
public class Sales : ClsStatic
{


    #region Properties and Fields


    public Int64 SaleID { get; set; }


    public Int64 SaleCustomerID { get; set; }


    public Int64 SaleProductID { get; set; }


    public double SaleNewCustomerPrice { get; set; }


    public double SaleNewProductPrice { get; set; }


    public double SaleFinalPrice { get; set; }


    public int SaleQuantity { get; set; }


    public DateTime SaleDate { get; set; }


    /* Extra Properties */


    #endregion


    #region CheckIn / CheckUp / Insert / Update / Save


   


    public void Insert(DataGridView xDgvN)
    {
        string xSqlStr = "INSERT INTO tblsales " +
                        "(SaleUserID, SaleProductID, SaleNewCustomerPrice, " +
                        "SaleNewProductPrice, SaleFinalPrice, SaleQuantity) " +
                        "VALUES " +
                        "(@SaleUserID, @SaleProductID, @SaleNewCustomerPrice, " +
                        "@SaleNewProductPrice, @SaleFinalPrice, @SaleQuantity)";
        for (int i = 0; i < xDgvN.Rows.Count; i++)
        {
            MySqlParameter[] xPar = new MySqlParameter[6];
            xPar[0] = new MySqlParameter("SaleUserID", MySqlDbType.Int64) { Value = SaleCustomerID };
            xPar[1] = new MySqlParameter("SaleProductID", MySqlDbType.Int64) { Value = SaleProductID };
            xPar[2] = new MySqlParameter("SaleNewCustomerPrice", MySqlDbType.Double) { Value = SaleNewCustomerPrice };
            xPar[3] = new MySqlParameter("SaleNewProductPrice", MySqlDbType.Double) { Value = SaleNewProductPrice };
            xPar[4] = new MySqlParameter("SaleFinalPrice", MySqlDbType.Double) { Value = SaleFinalPrice };
            xPar[5] = new MySqlParameter("SaleQuantity", MySqlDbType.Int32) { Value = SaleQuantity };
            xClsDb.RunSql(xSqlStr, xPar);
        }
    }
    
    #endregion


    #region Apply Filter


    public void AppliFilter(SfDataGrid Dgv)
    {
        if (SearchingFor == string.Empty)
        {
            Dgv.DataSource = DtGet;
        }
        else
        {
            if (DtGet == null) return;
            using (DataView xDv = new DataView(DtGet))
            {
                xDv.RowFilter = "ProductName LIKE '%" + SearchingFor + "%' OR " +
                                "ProductPrcie LIKE '%" + SearchingFor + "%'";
                Dgv.DataSource = xDv.ToTable();
            }
        }
    }


    #endregion

}
I'm using BackGroundWorker (DoWork and RunWorkerCompleted)

[Malini Selvarasu]

Hi  MUHAMMED DÖNMEZ,

Query 1 - How to remove a selection row after added?
We have reviewed the scenario you provided. However, we are a bit unclear about your requirement for the remove a selection row after added. Could you please provide additional details to elaborate on your needs? This information will enable us to offer a more precise and effective solution.

Query 2 - How to store DataGrid records in a database?

Thank you for sharing the code snippets for storing records in the database. Based on the provided information, it is clear that your goal is to add DataGrid records to the database.

Upon reviewing your code, it appears that you are utilizing direct property access to retrieve values. This approach results in only the last record being added to the database, as the properties contain the most recently updated value.

To address this issue, we recommend using `DataGrid.View.Records` for retrieving the DataGrid values to add the records to the database. We have modified your Insert method accordingly and prepared a sample to demonstrate this approach. Please find the attached sample and code snippet for your review.

Code snippet:

public void Insert()

        {

            string insertCommandText = @"

            INSERT INTO YourTableName (OrderID, CustomerName, CustomerID, UnitPrice, Country)

            VALUES (@OrderID, @CustomerName, @CustomerID, @UnitPrice, @Country)";

            using (SqlConnection connection = new SqlConnection(@"your_connection_string_here")) ;

            {

                for (int i = 0; i < sfDataGrid1.View.Records.Count; i++)

                {

                    var record = ((sfDataGrid1.View.Records[i] as RecordEntry).Data as DataRowView).Row.ItemArray;

                    SqlParameter[] xpr = new SqlParameter[5];

                    xpr[0] = new SqlParameter("OrderID", SqlDbType.Int) { Value = record[0] };

                    xpr[1] = new SqlParameter("CustomerName", SqlDbType.VarChar) { Value = record[1] };

                    xpr[2] = new SqlParameter("CustomerID", SqlDbType.VarChar) { Value = record[2] };

                    xpr[3] = new SqlParameter("UnitPrice", SqlDbType.Int) { Value = record[3] };

                    xpr[4] = new SqlParameter("Country", SqlDbType.VarChar) { Value = record[4] };

                    RunSql(insertCommandText, xpr);

               

                }

            }

        }

If we have misunderstood your requirement, kindly provide more details so that we can assist you further. Thank you for your understanding and cooperation.

Regards,

Malini Selvarasu

Attachment: SfDataGrid_Demo_fd86bb8.zip

[MUHAMMED DÖNMEZ]

Thank you so much for your update .. I will try the code for query2 (insert to data base)..

About query1 for deleting I wii attach a video about what exactly what I need ...

(by seleting the row or cell can delete it if added by mistake)

(by the way this video about old version,, normal datagridview but it exolains what I am doing)..

Thanks again for your respond , have a very nice day ..

Attachment: Recording_20240806_152711__my_video_c_79dc1f30.rar

[MUHAMMED DÖNMEZ]

And one more thing ,,I am using BackgroundWorker (assigning the values when button clicked then run the sql command in the Bgw_DoWork finally I have all errorr message or saved etc... in Bgw_RunWorkerCompleted...

In old version I retreive the data from dataGridView like this :

Dim xRowCount As Integer = Convert.ToInt32(DGV.Rows.Count - 1)

ReDim MyVar_CustomerID(xRowCount)

ReDim MyVar_ProductID(xRowCount)

ReDim MyVar_SaleNewCustomerPrice(xRowCount)

ReDim MyVar_SaleNewProductPrice(xRowCount)

ReDim MyVar_FinalPrice(xRowCount)

ReDim MyVar_ProductQnty(xRowCount)

For i As Integer = 0 To xRowCount

    MyVar_CustomerID(i) = Convert.ToInt64(DGV.Rows(i).Cells("CustomerID").Value.ToString)

    MyVar_ProductID(i) = Convert.ToInt64(DGV.Rows(i).Cells("ProductID").Value.ToString)

    MyVar_SaleNewCustomerPrice(i) = Convert.ToDouble(DGV.Rows(i).Cells("SaleNewCustomerPrice").Value.ToString)

    MyVar_SaleNewProductPrice(i) = Convert.ToDouble(DGV.Rows(i).Cells("SaleNewProductPrice").Value.ToString)

    MyVar_FinalPrice(i) = Convert.ToDouble(DGV.Rows(i).Cells("FinalPrice").Value.ToString)

    MyVar_ProductQnty(i) = Convert.ToInt32(DGV.Rows(i).Cells("ProductQnty").Value.ToString)

Next

Then send it to  Bgw_DoWork to run the sql command ...

but in sfdataGridview I can not find the same way to retrive these data like this :

 MyVar_CustomerID(i) = Convert.ToInt64(DGV.Rows(i).Cells("CustomerID").Value.ToString)

[Malini Selvarasu]

Hi  MUHAMMED DÖNMEZ,

Query - 1 - How to delete the selected records in SfDataGrid?

Thank you for providing the video reference and the detailed information about your requirements. Based on the provided information, we understand that you need to delete a record that was mistakenly added. This can be achieved by setting the `SfDataGrid.AllowDeleting` property to `true` and using the `DeleteSelectedRecords` method in `SfDataGrid` to delete the selected records. For further guidance, please refer to the user guide documentation linked below.

UG Link : DataManipulation | DataGrid | WinForms | Syncfusion

 Query - 2 In SfDataGrid, how can I retrieve data in a similar way to DataGridView?

In the SfDataGrid, the approach to retrieving cell values differs somewhat from the MS DataGridView. Instead of directly accessing cell values, SfDataGrid allows you to retrieve data from the underlying data source (i.e., the records). We recommend using `DataGrid.View.Records` to retrieve the DataGrid values for adding the records to the database.

If we have misunderstood your requirement, please provide us with more details about your requirement or the issue you are facing. This additional information will help us better understand the problem and proceed to provide an appropriate solution.

Thank you for your understanding and cooperation.

Regards,

Malini Selvarasu

[MUHAMMED DÖNMEZ]

Thank you for your reply,,

Last codes worked perfectly and excactly what I need, thanks again..

[Malini Selvarasu]

Hi MUHAMMED DÖNMEZ,

We are glad that your query was addressed. If you have any other queries, please create a new forum. We are closing this forum.

Regards,

Malini Selvarasu

[MUHAMMED DÖNMEZ]

Thank you so much .. you can close this forum and if I have any query I will create new one..

Agin thanks and regards..