Link to home
Create AccountLog in
Avatar of CharlieDev
CharlieDevFlag for United Kingdom of Great Britain and Northern Ireland

asked on

c# asp.net- add a new row to a gridview

Hi
I am having troubles adding a new row to a gridview. I have created the new row with the help of burakiewicz from experts-exchange but I am having troubles binding it to the dataset. I have an error that the reader is not open. I build the dataset using data from the database (DateFilter)and then on rendering each row I run code (SumRows) to add an extra row at the bottom but when it goes back to the code to build the dataset (DateFilter) it has an error and cant bind the data.
Can anyone suggest why it wont add this row?
Thanks
public void DateFilter(object sender, EventArgs e)
    {
        string projectID = project.SelectedValue;
 
        string dateDayTo = toDayDate.SelectedItem.ToString();
        string dateMonthTo = toMonthDate.SelectedItem.ToString();
        string dateYearTo = toYearDate.SelectedItem.ToString();
        string dateDayFrom = fromDayDate.SelectedItem.ToString();
        string dateMonthFrom = fromMonthDate.SelectedItem.ToString();
        string dateYearFrom = fromYearDate.SelectedItem.ToString();
        SqlCommand commandviewprojects = null;
        string To = "01/01/2070";
        string From = "01/01/2000";
 
       
        if (!String.IsNullOrEmpty(toDayDate.SelectedValue) && !String.IsNullOrEmpty(toMonthDate.SelectedValue))
        {
             To = dateDayTo + "/" + dateMonthTo + "/" + dateYearTo;
            
        }
        if (!String.IsNullOrEmpty(fromDayDate.SelectedValue) && !String.IsNullOrEmpty(fromMonthDate.SelectedValue))
        {
            From = dateDayFrom + "/" + dateMonthFrom + "/" + dateYearFrom;
        }
       
        
        DBConnect.OpenConnection();
 
            DateTime dateTo = Convert.ToDateTime(To);
            DateTime dateFrom = Convert.ToDateTime(From);
 
            commandviewprojects = new SqlCommand("exec usp_GetFilteredProjectView @projectID, @dateTo, @dateFrom", DBConnect.conn);
 
            commandviewprojects.Parameters.Add("projectID", SqlDbType.UniqueIdentifier);
            commandviewprojects.Parameters["projectID"].Value = new Guid(projectID);
            commandviewprojects.Parameters.Add("dateTo", SqlDbType.DateTime);
            commandviewprojects.Parameters["dateTo"].Value = dateTo;
            commandviewprojects.Parameters.Add("dateFrom", SqlDbType.DateTime);
            commandviewprojects.Parameters["dateFrom"].Value = dateFrom;
 
            SqlDataReader readerClient = commandClientView.ExecuteReader();
 
        if (commandviewprojects != null)
        {
            gvwProject.DataSource = commandviewprojects.ExecuteReader();
 
            gvwProject.DataBind();
 
            DBConnect.CloseConnection();
        } 
      
    }
    public int MinutesForProject(object sender, EventArgs e)
    {
        Guid selectedProjectID = new Guid(project.SelectedValue);
        DBConnect.OpenConnection();
        SqlCommand commandGetMins = new SqlCommand("exec usp_GetAllocatedMinsForProject @projectID", DBConnect.conn);
 
        commandGetMins.Parameters.Add("projectID", SqlDbType.UniqueIdentifier);
        commandGetMins.Parameters["projectID"].Value = selectedProjectID;
 
        return (int) Convert.ToInt32(commandGetMins.ExecuteScalar());
        DBConnect.CloseConnection();
 
    }
 
    float total;
   
 
    public void sumRows(Object src, GridViewRowEventArgs e)
    {
        
 
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            TableCellCollection cells = e.Row.Cells;
            total += Single.Parse(float.Parse(cells[cells.Count - 1].Text.Split(' ')[0]).ToString());
            cells[cells.Count - 1].Text = string.Format("{0:n2}",
            float.Parse(cells[cells.Count - 1].Text));
        } 
       
    }
 
    public void setFooter(Object src, GridViewRowEventArgs e)
    {
        string timeRemain = Convert.ToString(MinutesForProject(src, e));
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            TableCellCollection cells = e.Row.Cells;
            cells[0].Text = "";
            cells[1].ColumnSpan =1;
            cells[1].Text = "Total";
            cells[2].Text = string.Format("{0:n2}", total);
            cells[2].BackColor = System.Drawing.Color.Orange;
            
        }
 
        GridViewRow gvr = e.Row;
 
        if (gvr.RowType == DataControlRowType.Footer)
       {
            GridViewRow row = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal);
            row.CssClass = "subtotalFooter";
 
            TableCell[] cells = CreateCells(gvr);
            //Build all the cell text 0 is an example  
            cells[0].Text = timeRemain;
            
            //Add the two Columns
            row.Cells.AddRange(cells);
            //get a reference to the table that holds this row
            Table tbl = (gvr.Parent as Table);
            //Add the row at the end of the list, but before the footer.
            tbl.Rows.AddAt(gvwProject.Rows.Count + 1, row);      
        }
 
 
    }
    private TableCell[] CreateCells(GridViewRow gvr)
    {
        TableCell[] cells = new TableCell[gvr.Cells.Count];
        for (Int32 j = 0; j < gvr.Cells.Count; j++)
        {
            cells[j] = new TableCell();
            cells[j].HorizontalAlign = gvr.Cells[j].HorizontalAlign;
            cells[j].CssClass = gvr.Cells[j].CssClass;
        }
        return cells;
    }
}

Open in new window

Avatar of CharlieDev
CharlieDev
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

This is my new error after going through step y step in debugging mode: There is already an open DataReader associated with this Command which must be closed first.
SOLUTION
Avatar of Allamz
Allamz
Flag of Egypt image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thanks, I now get error showing on my page!!!

So its catching it, but how do I stop the error from occuring in the first place?
In the   gvwProject.DataSource = commandviewprojects.ExecuteReader();
the DataSource is NULL

SO it doesnt seem to like binding the extra row(set footer code) to the dataset as it worked fine before I added that to it
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi Imperdonato, Thanks for your post. I really dont think its to do with the database connection as the code worked fine until i added:

public void setFooter(Object src, GridViewRowEventArgs e)
    {
        GridViewRow gvr = e.Row;

        if (gvr.RowType == DataControlRowType.Footer)
       {
            GridViewRow row = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal);
            row.CssClass = "subtotalFooter";

            TableCell[] cells = CreateCells(gvr);
            //Build all the cell text 0 is an example  
            cells[0].Text = timeRemain;
           
            //Add the two Columns
            row.Cells.AddRange(cells);
            //get a reference to the table that holds this row
            Table tbl = (gvr.Parent as Table);
            //Add the row at the end of the list, but before the footer.
            tbl.Rows.AddAt(gvwProject.Rows.Count + 1, row);      
        }
 private TableCell[] CreateCells(GridViewRow gvr)
    {
        TableCell[] cells = new TableCell[gvr.Cells.Count];
        for (Int32 j = 0; j < gvr.Cells.Count; j++)
        {
            cells[j] = new TableCell();
            cells[j].HorizontalAlign = gvr.Cells[j].HorizontalAlign;
            cells[j].CssClass = gvr.Cells[j].CssClass;
        }
        return cells;
    }


so the error is in the above code somewhere, any ideas?
Thanks
Yeah, but still the error that you specify talks about connection being open/ closed, right?
I still don't have the full code, so can't really comment on it, but my say would be you are doing something wrong with the opening and closing of your connection object.
Can you try implementing once the thing I've suggested above? If that doesn't work, give the full code that will explain the definition and other needed things for the same.
I have an error that DBConnect doesnt have a definiton for State!
the code in the DBConnect should do that check anyhow

if it doesnt can you say what i put in the state bit ?
Thanks very much
public class DBConnect
{
 
    public static SqlConnection conn = null;
 
    public static void OpenConnection()
    {
        DBConnect.CloseConnection();
        if (conn == null)
        {
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["std"].ConnectionString);
        }
 
        conn.Open();
    }
 
    public static void CloseConnection()
    {
        if (conn != null)
            conn.Close();
    }

Open in new window

Instead of using the custom class, why don't you use the SQl connection class instead?

Something like:
    public static SqlConnection DBConnect = null;

I no longer have a database connection error. I have started again anyhow as I cant get it to work.
Thanks