none
Update/Insert Rows SQL Server Express RRS feed

  • Question

  • I need to "split" a row into two based on the values of two fields in the current row.  For example:

    pkfield                          top     bottom    sand    silt

    1017218:3624716          0         20         6.9     62.10

    1017218:3624475          30      112        44.0    40.50

    1017218:3624473          0        152        44.80  41.20

    1017218:3624596          0         71           9.5     67.50

    Criteria:  top = 0 AND bottom >= 100, then "split" the row WHERE:

    the value of the bottom column is changed to constant:  20 and INSERT a new row where the value of

    bottom = current value minus 20; the values of the other fields is the average of the values of the current row and the row immediately below it.

    In the above example, the row with pkfield = 1017218:3624473 is "split" into:

    pkfield                          top     bottom    sand                         silt

    1017218:3624473          0        20        44.80                      41.20   

    1017218:3624473          0        132        =(44.80 + 9.5)/2     = (41.20 + 67.50)/2

    Appreciate any advise.

     

    Monday, April 19, 2010 9:25 PM

Answers

  • Hi Marilyn,

     

    Have you run this section?   It will add all the newly split rows into the original table.  

    ==============================================================================

                // Add all the new rows into the table

                foreach (var row in insertedRows)

                {

                    table.Rows.Add(row);

                }

    ==============================================================================

    However, please note that if the pkfield is the primary key of the table, splitting the rows will generate duplicate keys, right?    It will cause some problems when we updating the database with the DataTable we get.  

     

    Please feel free to let me know if you have any questions.  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by deskcheck1 Wednesday, April 21, 2010 2:27 PM
    Wednesday, April 21, 2010 2:17 AM
    Moderator

All replies

  • Hello,

     

    Welcome to ADO.NET DataSet forum!

     

    Since you posted the question in DataSet forum, I suppose you have loaded the data into a DataTable and you want to split some rows programmatically.  Using a FOR loop to handle this is quite straightforward. 

    =================================================================================

                // List to hold all the newly inserted rows

                List<DataRow> insertedRows = new List<DataRow>();

     

                for (int i = 0; i < table.Rows.Count - 1; i++)

                {

                    // Check each row (top = 0 and bottom >= 100)

                    if ((int)table.Rows[i]["top"] == 0 &&

                        (int)table.Rows[i]["bottom"] >= 100)

                    {

                        // The newly inserted row

                        DataRow row = table.NewRow();

                        row.ItemArray = new object[]

                        {

                            table.Rows[i]["pkfield"],

                            table.Rows[i]["top"],

                            (int)table.Rows[i]["bottom"] - 20,

                            ((decimal)table.Rows[i]["sand"] + (decimal)table.Rows[i + 1]["sand"]) / 2,

                            ((decimal)table.Rows[i]["silt"] + (decimal)table.Rows[i + 1]["silt"]) / 2,

                        };

                        insertedRows.Add(row);

     

                        // Set the bottom column of the original row

                        table.Rows[i]["bottom"] = 20;

                    }

                }

     

                // Add all the new rows into the table

                foreach (var row in insertedRows)

                {

                    table.Rows.Add(row);

                }

    =================================================================================

     

    Does it solve the problem?   If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, April 20, 2010 5:57 AM
    Moderator
  • Hi,

    Thanks for providing the code.  However, it didn't add any new row.  It did set the original bottom value to 20.  But somehow, I can't get it to add the new row; or the new rows are not persisted into the database.

    Appreciate your help very much,

    Marilyn

    Tuesday, April 20, 2010 2:25 PM
  • Hi Marilyn,

     

    Have you run this section?   It will add all the newly split rows into the original table.  

    ==============================================================================

                // Add all the new rows into the table

                foreach (var row in insertedRows)

                {

                    table.Rows.Add(row);

                }

    ==============================================================================

    However, please note that if the pkfield is the primary key of the table, splitting the rows will generate duplicate keys, right?    It will cause some problems when we updating the database with the DataTable we get.  

     

    Please feel free to let me know if you have any questions.  

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by deskcheck1 Wednesday, April 21, 2010 2:27 PM
    Wednesday, April 21, 2010 2:17 AM
    Moderator
  • Hi,

    I just realized that not only does "table.Rows.Add(row)" adds the new rows into the table, the table also contains all the original data.  So, I just delete all records from the original table and replaced it with the new table.

    Thanks so much for your help.

    Marilyn

    Wednesday, April 21, 2010 2:27 PM
  • It's my pleasure!  :-)

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 22, 2010 8:17 AM
    Moderator