none
ListObject: inserted row in Excel disappears RRS feed

  • Question


  • Hi

    I did the walkthrogh "Complex Data Binding in an Application-Level Project" http://msdn.microsoft.com/en-us/library/cc668212.aspx

    When I debug with F5 all data will be loaded in Excel. When I insert a new row in Excel (typing the data into a cell, clicking TAB), after the last data cell in the row, the whole row disappears. What's wrong. Do I have to set any additional properties? Has somebody a similar problem? What can I do?

    Many Thanks!
    Bernhard

    Thursday, March 1, 2012 7:26 PM

All replies

  • Hi again,

    seems to be the same problem as here:
    http://social.msdn.microsoft.com/Forums/eu/vsto/thread/d737d24c-587c-4b45-bf1c-9aec63faf467

    This behavior occurs if there are any constraints and the current row does not fulfill these constraints.

    I hook into the Events BeforeAddDataBoundRow and ErrorAddDataBoundRow. In the Event BeforeAddDataBoundRow I can do the statement e.Cancel = true
    but the row with the incorrect data disappears even so.

    What I want:
    Show the user a message with a hint what's wrong and then give him a second chance to correct his data. How can I achieve that? (The row with the incorrect data should not be deleted automatically!)

    There is a good example on msdn:
    How to: Validate Data When a New Row is Added to a ListObject Control

    Any ideas?

    Many Thanks
    Bernhard


    • Edited by blauber1 Saturday, March 3, 2012 8:55 AM added good example from msdn
    Friday, March 2, 2012 3:40 PM
  • Hi Bernhard,

    Thanks for posting in the MSDN Forum.

    "but the row with the incorrect data disappears even so." What's mean of it? Does the incorrect row disappear? I tried the BeforeAddDataBoundRow event, it works fine if you append the data at the end of the ListObject(no use tab to navigate). And if you use tab to navigate form the ListObject to new cell it will left  a blank cell after error row disappear. However I don't think this row will trouble you more.

    Maybe this snippet will help you.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Xml.Linq;
    using Microsoft.Office.Tools.Excel;
    using Microsoft.VisualStudio.Tools.Applications.Runtime;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    
    namespace ExcelWorkbook1
    {
        public partial class Sheet1
        {
            public DataTable dt = null;
    
            private void Sheet1_Startup(object sender, System.EventArgs e)
            {
                dt = new DataTable("test");
                dt.Columns.Add("Value", typeof(int));
                dt.Rows.Add(1);
                dt.Rows.Add(2);
                dt.Rows.Add(-1);
                list1.AutoSetDataBoundColumnHeaders = true;
                list1.SetDataBinding(dt);
                list1.BeforeAddDataBoundRow += new BeforeAddDataBoundRowEventHandler(list1_BeforeAddDataBoundRow);
                DataRow dr = dt.NewRow();
                dr[0] = -5;
            }
    
            void list1_BeforeAddDataBoundRow(object sender, BeforeAddDataBoundRowEventArgs e)
            {
                DataRow dr = ((DataRowView)e.Item).Row;
                if (dr[0] != null)
                {
                    int i = -1;
                    try
                    {
                        i = (int)dr[0];
                    }
                    catch
                    {
                        e.Cancel = true;
                        return;
                    }
                    if (i < 0)
                    {
                        MessageBox.Show("Error");
                        e.Cancel = true;
                        return;
                    }
                }
                MessageBox.Show("dddfd");
                e.Cancel = true;
                V();
            }
    
            private void V()
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr[0] is System.DBNull)
                        dt.Rows.Remove(dr);
                }
            }
    
            private void Sheet1_Shutdown(object sender, System.EventArgs e)
            {
            }
    
            #region VSTO Designer generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(this.Sheet1_Startup);
                this.Shutdown += new System.EventHandler(this.Sheet1_Shutdown);
    
            }
    
            #endregion
    
    
        }
    }

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, March 6, 2012 6:02 AM
    Moderator
  • Hi Tom

    many thanks for your example. I tried it - but the behavior is still the same:

    When I insert a new row in Excel and then insert e.g. the value -40 the EventHandler list1_BeforeAddDataBoundRow is fired, then the error message is shown - but after the Cancel-instruction the row is deleted. And it doesn't matter if I press tab, return or click away with the mouse.

    Do you have the same behaviour? The installed version of Excel is 14.0.6112.5000, 32 bit.

    Thanks & Regards
    Bernhard

    Tuesday, March 6, 2012 4:47 PM
  • Hi again

    so I ask me: is this behavior a bug? Or did I something wrong? Or is this behaviour "as designed"?

    Another idea: I know it is possible with Excel to edit your TFS Tasks. And I have read this was not implemented with ListObject? Why not? Does somebody know with which obect the TFS team has implemented that? Is this Code available? Are there some "Best Practices"?

    Thanks & Regards
    Bernhard

    Thursday, March 8, 2012 10:31 AM
  • Hi blauber1,

    Sorry for later response. I would recommend you delete row from the datatable instead of ListObject.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, March 15, 2012 7:34 AM
    Moderator
  • Hi Tom

    I think you misunderstood me:

    I have done the example with your code (see above). When I insert a new row in Excel and then insert e.g. the value -40 the EventHandler list1_BeforeAddDataBoundRow is fired, then the error message is shown - but after the Cancel-instruction the row is deleted automatically by the ListObject. And it doesn't matter if I press tab, return or click away with the mouse in the Excel sheet.

    What we want is:

    1. User adds a new row in Excel within the ListObject

    2. The data then is validated in the BeforeAddDataBoundRow

    3a. If the data is OK --> Row will be added

    3b. If the data is not OK --> We want to set Cancel = true and show a Messagebox to the User with the information which data is wrong.
    Then we want following: The row should not be deleted automatically and the user should have a chance to correct the data.

    So the question is: How to suppress the automatically deleting by the ListObject after setting Cancel = true in the BeforeAddDataBoundRowa event handler.

    Is it possible for you to reproduce this with your example code from March, 6.th?

    Many thanks in advance!
    Wish you a good day too

    Bernhard

    Thursday, March 15, 2012 8:09 AM