GridView + FindControl = Certain Death!?

Why hello there!

I'm currently working on a section of a project that utilizes a GridView - I have used this because each page could display anywhere from 1 to a few dozen records at a time.

Basically, what I'm trying to get this part of my page to do is only display a Hyperlink if the record being viewed has employees. So, I have a simple query run to count all employees with a ManagerID equal to that of the current record's ID. If the Count returns with more than 0, a value is assigned to the hyperlink.

The problem is, I have to pull the current record's ID from a hiddenfield control on the page - which normally wouldn't be a problem, but since I'm viewing multiple records at once in my GridView, the standard GridView1.FindControl("Blah") doesn't work.

I've looked around a bit on the internet and have tried many things, and so far a foreach statement has seemed to work the best. However, my code seems to skip the Foreach statement, giving it the default value...

Well, here's my Code:

    protected String Forward()
    {
        String returnstring = "";

        foreach (GridViewRow row in ListingsGrid.Rows)
        {

            string ID = ((HiddenField)ListingsGrid.FindControl("IDLabel")).Value;

            string DatabaseConnectionStringLocate = WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

            string sql = "SELECT COUNT(ID) FROM Employee WHERE ManagerID = '" + ID + "'";
            SqlConnection con = new SqlConnection(DatabaseConnectionStringLocate);
            SqlCommand cmd = new SqlCommand(sql, con);

            con.Open();

            int EmployeeCount = (int)cmd.ExecuteScalar();

            con.Close();

            if (EmployeeCount > 0)
            {
                returnstring = "Employees";
            }
            else
            {
                returnstring = EmployeeCount.ToString();
            }
        }

        return (returnstring);
    }

Code (markup):

Does anyone know how to get the Foreach statement working properly? If not that, is there a better way to accomplish this? Any help would be greatly appreciated.

Thanks.

 

I don't think I was all too much help on the last thing I tried to help you with, but until you tell me to stop I'll try again. ;-)

In the .NET IDE, if you click on the grid so that you can see the properties, you can click on the little lightning bolt icon above the properties to get the events. There is an event called 'RowDataBound'. If you double click that event in the little properties window it will create the even handler and hook up the event to the grid for you. That event will fire for every row that is generated on your grid. There is a parameter in that event that it creates called GridViewRowEventArgs with a variable name 'e'. Use the e object to get the row and then the cells. You can get access to each row as they fire one at a time, and each cell in each row by iterating through the cells array.

Hope that helps and makes it a little easier than trying to loop through the rows manually.

 

!

This is exactly what I needed! I saw and tried all sorts of stuff with the GridViewRowEventArgs, but couldn't figure out exactly how to do it!

Anyway, I replaced some stuff with e.Row, but I'm still getting the error that the "Object reference not set to an instance of an object" highlighting my lines with FindControl. Here's my revised code, in case I screwed somethin' up.

    protected void ManagerCalculate(object sender, GridViewRowEventArgs e)
    {
            // This just sets a managable string to the value of the ID.  I get an error here where "Object reference not set to an instance of an object." 
            string ManagerID = ((HiddenField)e.Row.FindControl("IDLabel")).Value;

            // This just sets up the ConnectionString
            string DatabaseConnectionStringLocate = WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

            //Here's my SQL, it's all working correctly
            string sql = "SELECT COUNT(ID) FROM Employee WHERE ManagerID = " + ManagerID;
            SqlConnection con = new SqlConnection(DatabaseConnectionStringLocate);
            SqlCommand cmd = new SqlCommand(sql, con);

            con.Open();

            // I get the same error here as I did up at the string ManagerID.
            ((HiddenField)e.Row.FindControl("EmployeeCount")).Value = (String)cmd.ExecuteScalar();

            con.Close();

            // And for a third time I get the same error on this line.
            String EmployeeCount = ((HiddenField)e.Row.FindControl("EmployeeCount")).Value;
            
            // This is just in there so I can make sure it works properly.
            Response.Write(EmployeeCount);
    }

Code (markup):

Thanks!

 

What you might want to do is take a little different approach.

Can you just set the managerID as a regular column in your gridView instead of putting it in a hidden field? Then you can see it and get everything to work just by referencing the value of that cell in the row when the event fires.

Once you get it working, there is a similar event called initialize or gridInit (something with initialize in it) that fires once when the grid is databound. In this event is a good place to format your grid and set the column with the managerID to be hidden. You can still use it server side when loading and postbacks, but it just won't show to the client.

 

Alright, so I threw the line "<asp:BoundField DataField="ID" Visible="false" />" in between my <Columns> tags and above my TemplateField. I then changed my backend up a little bit:

    protected void ManagerCalculate(object sender, GridViewRowEventArgs e)
    {
            // This just sets a managable string to the value of the ID.  I get an error here where "Object reference not set to an instance of an object." 
            string ID = ((String)DataBinder.Eval(e.Row.DataItem, "ID")).ToString();

            // This just sets up the ConnectionString
            string DatabaseConnectionStringLocate = WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

            //Here's my SQL, it's all working correctly
            string sql = "SELECT COUNT(ID) FROM Employee WHERE ManagerID = '" + ID + "'";
            SqlConnection con = new SqlConnection(DatabaseConnectionStringLocate);
            SqlCommand cmd = new SqlCommand(sql, con);

            con.Open();

            // I get the same error here as I did up at the string ManagerID.
            String EmployeeCount = (String)cmd.ExecuteScalar();

            con.Close();

            // And for a third time I get the same error on this line.
            //String EmployeeCount = ((HiddenField)e.Row.FindControl("EmployeeCount")).Value;
            
            // This is just in there so I can make sure it works properly.
            Response.Write(EmployeeCount);
    }

Code (markup):

Problem is, I'm still getting the same error on the line with my String ID. >_O

 

Can you change this:

string ID = ((String)DataBinder.Eval(e.Row.DataItem, "ID")).ToString();

to this:

string ID = e.Row.Cells[0].Text; //Change 0 to the appropriate index of the column

??

 

Aha! It worked! <3

Now I have a different error farther down the page. =D My page now highlights this line:

        String EmployeeCount = (String)cmd.ExecuteScalar();

Code (markup):

And says "Conversion failed when converting the varchar value '&nbsp;' to data type int."

I figured that it probably has something to do with the query, so I removed the apostrophies and changed my query to:

        string sql = "SELECT COUNT(ID) FROM Employee WHERE ManagerID = " + ID;

Code (markup):

And now it highlights the same line with ExecuteScalar but gives me the error "Incorrect syntax near '&'. "

Any ideas on what could have caused that? >>

 

What does the ID variable look like when you step through the code and mouse over it when it is being added to the sql query string. Does it have additional text in it along with the ID value?

 

When I mouseover my ID variable within the query sting a tooltip pops up that says "(local variable) String ID".

Within my database, ID is set up as an integer, with values starting at 1 and going up to 28.

I tried changing out the Cells[0] with Cells[1], thinking that it may be trying to pull the wrong value, but it ended up giving me the same error.

 

Place a break on that line and run the app from code. It will stop at that point and you can then mouse over the variable to get the current value while it is running.

Or, when it stops you can use the "immediate" window on the bottom, type in the variable name and press enter and it will show the value. I'm guessign that the value that it is setting ID as is not the ID, or it has some other text with it.

 

Alright, I don't know how to do what you said, but I knew what you were getting at. I've been trying to Response.Write(ID) to see what was coming out and it wouldn't show anything, so you inspired me to Response.Write(sql) - and I know realize why I didn't see anything when it was writing ID.

It gave me exactly this:

SELECT COUNT(ID) FROM Employee WHERE ManagerID = ' 'SELECT COUNT(ID) FROM Employee WHERE ManagerID = ''SELECT COUNT(ID) FROM Employee WHERE ManagerID = ' ' 

Code (markup):

I'm not sure why it would write that 3 times, as there's only one record to be shown on this page. Ideally, my sql query should be SELECT COUNT(ID) FROM Employee WHERE ManagerID = '[Whatever the record's ID is]'... So it seems like it's returning the blank space, &nbsp for my ID value.

I'll give you the code I just used to Write the query string:

    protected void ManagerCalculate(object sender, GridViewRowEventArgs e)
    {
        // This just sets a managable string to the value of the ID.  I get an error here where "Object reference not set to an instance of an object." 
        String ID = e.Row.Cells[0].Text.ToString();

        // This just sets up the ConnectionString
        string DatabaseConnectionStringLocate = WebConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

        //Here's my SQL, it's all working correctly
        string sql = "SELECT COUNT(ID) FROM Employee WHERE ManagerID = '" + ID + "'";
        SqlConnection con = new SqlConnection(DatabaseConnectionStringLocate);
        SqlCommand cmd = new SqlCommand(sql, con);

        con.Open();

        // I get the same error here as I did up at the string ManagerID.
        //String EmployeeCount = (String)cmd.ExecuteScalar();

        con.Close();

        // And for a third time I get the same error on this line.
        //String EmployeeCount = ((HiddenField)e.Row.FindControl("EmployeeCount")).Value;
        
        Response.Write(sql);
    }

Code (markup):

And here's my GridView, 'cause I have a feeling it may have something to do with that.

            <asp:GridView ID="ListingsGrid" runat="Server" AutoGenerateColumns="False" AllowPaging="False" DataSourceID="SqlDataSource1" BorderWidth="0" OnRowDataBound="ManagerCalculate">
                <Columns>
                <asp:BoundField DataField="ID" Visible="false" />
                <asp:TemplateField>
                        <ItemTemplate>
                            <tr>
                                <td style="text-align:left; vertical-align:top;" width="175"><asp:Label ID="FNameLabel" runat="server" Text='<%# Bind("FName") %>'></asp:Label>
                                
                                <asp:HiddenField ID="IDLabel" runat="server" Value='<%# Bind("ID") %>' />
                                
                                <asp:Label ID="LNameLabel" runat="server" Text='<%# Bind("LName") %>'></asp:Label><br />

                                <i><asp:Label ID="TitleLabel" runat="server" Text='<%# Bind("Title") %>'></asp:Label></i><br />

                                <asp:Label ID="PhoneLabel" runat="server" Text='<%# Bind("Phone") %>'></asp:Label><br />
                             
                                <asp:HyperLink ID="HyperLink1" runat="server" Text='<%# Back(Container.DataItem) %>' NavigateUrl='<%# "employeegrid.aspx?t=" + Eval("MManagerID") + "&m=" + Eval("MManagerID")%>' Target="_self"></asp:HyperLink><br />
                                
                                <asp:HyperLink ID="HyperLink2" runat="server" Text="" NavigateUrl='<%# "employeegrid.aspx?t=" + Eval("ID") + "&m=" + Eval("ManagerID")%>' Target="_self"></asp:HyperLink><br />
                                
                                <asp:HyperLink Text="Information" runat="server" NavigateUrl='<%# "employee.aspx?t=" + Eval("ID")%>'></asp:HyperLink>
                                
                                <asp:HiddenField ID="EmployeeCount" runat="server" />
                                
                                </td>
                                
                                <td style="text-align:left; vertical-align:top; width:200px"><img alt="Picture" height="125" width="125" src="Pictures/<%# Eval("Picture") %>"/></td>
                                
                            </tr>

                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>  
            </asp:GridView>

Code (markup):

 

Something interesting I found... When I changed my Response.Write(sql) to Response.Write("cat" + ID + "dog"), it wrote this at the top of my page: cat dogcatdogcat dog

I had no idea why it has decided to make my ID's value dogcatdogcat. o_o So, with that, I tried changing it Response.Write("cat"), and it responded with catcatcat.

...But I still have no idea as to why it would do that.

 

Do you have 3 rows in your dataview? Is it happening for each time the event is fired... one time per row?

 

Alright, well, I tried some more tests out... When I try to load another page that should display 4 rows of data, it gives me 6 "cat"s; when I try with one that should display 2 rows of data, it gives me 4 "cat"s.

So somewhere, the event is firing 2 times more. Think it may be in a header and footer of somesort?

Granted, this isn't as much of a problem as the whole "not pulling the correct data" thing, but it'd still be a nice one to figure out.

 

AHA! I was correct in my assumption of the header/footer thing. =D

All I had to do was wrap my code in an if statement: if (e.Row.DataItem != null) { code }

Unfortunately the big problem isn't fixed yet, so I'll keep workin' on that one. ^_~

EDIT:

YES. I kept fiddlin' around and put this in as my string: String ID = ((HiddenField)e.Row.FindControl("IDLabel")).Value;

And guess what? It works! =D I have a feeling that it was trying to pull a value from the header, which would most likely return an empty/effed up value for the IDLabel control..

Regardless, it works now. For this I thank you a bamillion times!

<3

 

Glad to hear it.