DataTable not retaining added rows

Question

This webpage should flow as such:

Call is made to populateTeamMembers(), which first checks to see if the datatable has no rows (indicating this is the first time the function is called) in case it queries SQL to see who is an existing team member, load that into the datatable sqlTeamMembers

This function also checks if there are any rows in a second datatable, newTeamMembers, and if so it merges the contents of newTeamMembers into sqlTeamMembers. The first time the page loads, the newTeamMembers datatable should always be empty, so no merge is performed.

Finally, now that sqlTeamMembers should contain everybody we need, it binds it to a gridview for display on the web page.

Once it is displayed, there is a dropdownlist under the grid view, with a list of employees that may be added to the team. If the selected index is changed on the ddl, you get a postback and selectedIndexChanged() gets called.

The logic in selectedIndexChanged() is that it queries the selected individuals information into a datatable, warns the user if they are already assigned to a different team, and proceeds to add the employee to the newTeamMembers datatable.

Finally, the populateTeamMembers() is called again, this time there is data in the newTeamMembers datatable, and the merge is performed, before binding to the gridView again.

Due to the nature of the two datatables sqlTeamMembers and newTeamMembers, I have initialized them at the head of my class so they should be accessible to all functions.

It works so far as adding a single new line to sqlTeamMembers, but if I attempt to follow through with a second addition, it is wiping out the first addition. For some reason, newTeamMembers is not retaining the new rows, it only ever contains the most recent addition that selectedIndexChanged() creates.

How can I make newTeamMembers retain the new rows each time selectedIndexChanged() gets called?

public partial class Personnel_Admin_Teams : System.Web.UI.Page
{

SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["WebServerConnectionString"].ConnectionString);
SqlDataReader myreader = default(SqlDataReader);
SqlCommand cmd = new SqlCommand();


string sqlStr;
DataTable sqlTeamMembers = new DataTable();
DataTable newTeamMembers = new DataTable();
...



protected void populateTeamMembers(string TeamID)
{
    if (sqlTeamMembers.Rows.Count == 0)
    {
        /***  Read Team Members with Matching TeamID from Employees Table ***/
        cnn.Open();
        sqlStr = "SELECT [ID], [LastName]+', '+[FirstName] AS [Name], ISNULL([TeamRole], '') AS [TeamRole] FROM [Employees] WHERE [TeamID] = " + TeamID + ";";
        cmd = new SqlCommand(sqlStr, cnn);
        sqlTeamMembers.Load(cmd.ExecuteReader());
        cnn.Close();
    }



    if (newTeamMembers.Rows.Count > 0)
    {
        sqlTeamMembers.Merge(newTeamMembers);
    }

    gvTeamMembers.DataSource = sqlTeamMembers;
    gvTeamMembers.DataBind();


    try
    {
        for (int i = 0; i < gvTeamMembers.Rows.Count; i++)
        {

            DropDownList ddlTeamRole = gvTeamMembers.Rows[i].Cells[2].FindControl("ddlTeamRole") as DropDownList;
            string txtTeamRole = sqlTeamMembers.Rows[i][2].ToString();
            txtTeamRole = txtTeamRole.Replace("'", "''");

            ddlTeamRole.SelectedValue = txtTeamRole;
        }
    }

    catch (Exception ex)
    {
        ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "class", "alert(\"There was an error: " + ex + "\");", true);
    }
}



selectedIndexChanged()  
{    


        cnn.Open();
        sqlStr = "SELECT [ID], [LastName]+', '+[FirstName] AS [Name], ISNULL([TeamRole], '') AS [TeamRole], ISNULL([TeamID], '0') FROM [Employees] WHERE [ID] = " + ddlAllEmployees.SelectedValue + ";";
        cmd = new SqlCommand(sqlStr, cnn);
        DataTable addOneMember = new DataTable();
        addOneMember.Load(cmd.ExecuteReader());
        cnn.Close();


        int empTeamID = Convert.ToInt32(addOneMember.Rows[0][3]);

        /*** If DT has not been used yet, establish columns ***/
        if (newTeamMembers.Columns.Count == 0)
        {
            newTeamMembers.Columns.Add("ID", typeof(int));
            newTeamMembers.Columns.Add("Name", typeof(string));
            newTeamMembers.Columns.Add("TeamRole", typeof(string));
        }

        /*** Remove TeamID Column before merging data back to primary DataTable, perform merge ***/
        addOneMember.Columns.RemoveAt(3);
        newTeamMembers.Merge(addOneMember);



    if (empTeamID != 0)
        {
            sqlStr = "SELECT [TeamLocation]+' | '+[Department]+' | '+[Type]+' | '+[Team]+' | '+[Shift] AS [Team] FROM [Teams] WHERE [ID] =" + empTeamID + ";";
            cmd = new SqlCommand(sqlStr, cnn);
            cnn.Open();
            myreader = cmd.ExecuteReader();
            myreader.Read();

            string existingTeam = myreader[0].ToString();

            myreader.Close();
            cnn.Close();

            ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "class", "alert(\"Warning: User is currently assigned to another team.  Employee will be removed from " + existingTeam + " when you save this page.\");", true);

        }
    }

Answer 1

There are a few things you need to understand.

Web pages are stateless, which means on it's initial pass and subsequent PostBacks you are responsible for saving and restoring state via some persistence medium.

A lot of Server controls can do this for you, but you are opting to manage all of this via DataTables, so you need to implement data persistence. For Database query results, the SqlDataSource control uses the Application Cache. You should use that as well programmatically:

    public partial class Personnel_Admin_Teams : System.Web.UI.Page
    {
      DataTable sqlTeamMembers = null;
      DataTable newTeamMembers = null;

      protected void Page_Load( object sender, EventArgs e ) {
        if( !Page.IsPostBack ){
          if( Application.Cache["sqlTeamMembers"] == null ) 
            Application.Cache["sqlTeamMembers"] = new DataTable();

          if( Application.Cache["newTeamMembers"] == null ) 
            Application.Cache["newTeamMembers"] = new DataTable();
        }

        sqlTeamMembers = (DataTable) Application.Cache["sqlTeamMembers"];
        newTeamMembers = (DataTable) Application.Cache["newTeamMembers"];

        // NOTE: sqlTeamMembers will be null until you make that call to 
        //       populateTeamMembers()  so there is no hard and fast rule 
        //       about binding during PageLoad.  Bind every time you need 
        //       to reflect changes.
        gvTeamMembers.DataSource = sqlTeamMembers;
        gvTeamMembers.DataBind();
      }

    }

Also, just to verify, DataTables are not live, open connections to the database. Changes to DataTables are not automatically written back to your database. You have to make the appropriate INSERT/UPDATE SQL calls.

Answer 2

I think the problem is the placement of this line:

DataTable newTeamMembers = new DataTable();

Since it's at the beginning of the class, it's run each time the page is loaded, meaning everytime you load the page, including when page is a postback. Postback occurs, for example, when the user clicks a button to add a new team member. The page is then posted back to the server with the user entries included.

The behaviour you're looking for is to initialize a new newTeamMembers the first time the page loads, but not when page is a postback. To solve this, declare newTeamMembers at the top of the class, but only initialize it when the page is not a postback, by checking this in the page load event:

public partial class Personnel_Admin_Teams : System.Web.UI.Page
{
    DataTable newTeamMembers;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack) //page is loaded for the first time
        {
            newTeamMembers = new DataTable();
        }
    }
    //rest of the code
}