Solved

Populating Checkbox List Values from a Database

Posted on 2010-11-14
68
1,470 Views
Last Modified: 2012-05-10
I have built a checkbox List on my page using a datasource which builds the items from a data table.  There is a separate data table which holds the values of the checked list items.

For example, each partner can select multiple services they offer. Each service selection is stored in a map data table with the pertner ID and the service ID.  

I am trying to call up these partnerID/serviceID pairs to re-populate the checkbox list boxes when our partners log in to update their profiles, but I keep getting an error "Object reference not set to an instance of an object."  on this line of code from the first snipped below:

ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

I'm not exactly sure what this error means and why I am getting it?  

Also, should I put a try/catch in place in case there are no records returned (i.e. the partner has not indicated any services they offer)?
// Build the Services Offered command

                SqlCommand cmdSO = new SqlCommand("sp_ServicesOffered_MAP", conn);

                cmdSO.CommandType = CommandType.StoredProcedure;

                cmdSO.Parameters.AddWithValue("@ID", partnerid);



                SqlDataReader SOreader = cmdSO.ExecuteReader();

                CheckBoxList chkbx = (CheckBoxList)Page.FindControl("cblServicesOffered");



                while (SOreader.Read()) 

                {     

                    ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                    if (currentCheckBox != null)

                    {

                        currentCheckBox.Selected = true;

                    }

                

                }

Open in new window

<p>

            <asp:Label ID="lblServicesOffered" runat="server" 

                AssociatedControlID="cblServicesOffered" Text="Services Offered" 

                CssClass="black12Bold"></asp:Label>

            <asp:CheckBoxList ID="cblServicesOffered" runat="server" BorderStyle="Groove" 

                RepeatColumns="3" TabIndex="60" DataSourceID="dsServicesOffered" DataTextField="LIST_ITEM_NAME" 

                DataValueField="LIST_ID">

            </asp:CheckBoxList>

        </p>

Open in new window

0
Comment
Question by:vcbertini
  • 37
  • 31
68 Comments
 
LVL 21

Expert Comment

by:Alfred1
ID: 34133242
Have you tried to debug that line 11 to determine the value of SOreader["AGENCY_SERVICES_INTERIM_ID"]?  It looks like it is NULL and then you are trying to convert it into string using ToString.  You will definitely get Object reference not set to an instance of an object." error.

Yes.  you could try putting a try catch in there if you want so that you can isolate the error.
0
 

Author Comment

by:vcbertini
ID: 34133296
I'm getting "Enumeration yielded no results", which is weird because I tested the stored procedure and it yielded 3 results.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34133363
Is "AGENCY_SERVICES_INTERIM_ID" a resulting column in your stored procedure?  Can you provide a snapshot of your SP?  It might have something to do with your SP.

Also, since it passed while (SOreader.Read()), it means it is reading the results.  Have you tried to investigate the value/s of the first Read using your debugger?
0
 

Author Comment

by:vcbertini
ID: 34133383
I'm still new at using the debugger, but I think it said there was a [0] and [1] result, which I'm assuming is the index of results...

Stored procedure is below. Pretty simple.
PROCEDURE dbo.sp_ServicesOffered_MAP

	@ID int = 0

AS

	SELECT  ENTITY_ID, AGENCY_SERVICES_INTERIM_ID

	FROM    SERVICES_OFFERED_MAP

	WHERE   (ENTITY_ID = @ID)

Open in new window

0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34133413
OK.  SP is straightforward.  Can you try putting a breakpoint in line 11, run your debugger and then in your immediate window do the following one at time to inspect the values:

? SOreader[0]    
? SOreader[1]
? SOreader["AGENCY_SERVICES_INTERIM_ID"]
? SOreader["ENTITY_ID"]

Can you provide the results if possible?
0
 

Author Comment

by:vcbertini
ID: 34133451
The data is there. I managed to find the AGENCY_SERVICES_INTERIM_ID = 10014 and THe ENTITY_ID = 7

hasrows was true

I confirmed this is one of the row results from the stored procedure (there should be three returned records total).  The "7" should find a "7" in the LIST_ID of the Checkbox Value.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34133515
OK.  With the three returned records, was there any empty data at all?  You might have a result set that have empty data on both columns.

As I recall this error occurs when empty result sets are returned.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34133536
Oh by the way, are you also returning any NULL results on both columns from your stored proc?  This will also cause problems as well.
0
 

Author Comment

by:vcbertini
ID: 34136351
When I executed the stored procedure passing the same parameter that the code is passing, I received no null result sets. When I checked the reader, I only found that one result set that I mentioned above (probably because that was the first iteration of the where {}, but it choked on that iteration (that included no nulls) so that is what is so puzzling.

As a curiousity I checked the table that is being queried for null values anywhere and there are none in there.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34136807
OK.  Correct me if I am wrong,  there is only one row returned from the SP?  How many rows were returned, what I understand is three, right?

This is a bit of a longshot but can you try putting an ORDER BY AGENCY_SERVICES_INTERIM_ID in your stored procedure and try again?  At least when you run your stored proc in SSMS to verify, you will have the same sequence as the result set.   And then, verify each result if you can go forward in your data reader.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34137061
OK.  I think I know what your problem is.  I believe you are retrieving multiple result sets from your simple stored procedure.  By the way, which SQL Server version are you using?  Try inserting a SET NOCOUNT ON in your procedure and try again.  I hope this helps this time.


PROCEDURE dbo.sp_ServicesOffered_MAP

	@ID int = 0

AS

        SET NOCOUNT ON

        GO



	SELECT  ENTITY_ID, AGENCY_SERVICES_INTERIM_ID

	FROM    SERVICES_OFFERED_MAP

	WHERE   (ENTITY_ID = @ID)



        GO

Open in new window

0
 

Author Comment

by:vcbertini
ID: 34137251
I am running SQL Server Express that comes packaged with Visual Studio Express 2010.  I added the NO COUNT, but the ALTER PROCEDURE did not allow the GO command, so left that one out.

Ran it again and got the same error.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34140583
OK.  I just quickly copied the construct from a query analyzer.  Inside the stored proc should have only one GO.  Sorry.  :-)
Try either SET NOCOUNT ON without the GO or SET NOCOUNT ON;
PROCEDURE dbo.sp_ServicesOffered_MAP

	@ID int = 0

AS

        SET NOCOUNT ON;



	SELECT  ENTITY_ID, AGENCY_SERVICES_INTERIM_ID

	FROM    SERVICES_OFFERED_MAP

	WHERE   (ENTITY_ID = @ID)



        GO

Open in new window

0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34140614
Oh just to confirm, it should be "SET NOCOUNT ON;"  (with the semi colon).  Check the template below for SQL Server 2005/2008

http://www.mssqltips.com/tip.asp?tip=1226
0
 

Author Comment

by:vcbertini
ID: 34141871
Still no luck. There should be 3 records returned and I am only getting a [0] value of (10014, 7) and a [1] value of (10014,9) the third record is not being returned. Same error "Object reference not set to an instance of an object."

It errors on the "ListItem" line below.

while (SOreader.Read())
                {    
                    ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());
                    if (currentCheckBox != null)
                    {
                        currentCheckBox.Selected = true;
                    }
               
                }

I appreciate your continued efforts to help me with this.
0
 

Author Comment

by:vcbertini
ID: 34141882
Does it matter that both fields in the table are data type "int" values and in the reader, this ID is .ToString()?
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34141921
It should not matter but you can try removing ToString and check the results.

Also, what should be the third record?  You mention you can get this using Query Analyzer, right?  It should be (10014,<number>), right?  Would the third record for SOreader["AGENCY_SERVICES_INTERIM_ID"] be empty (string.empty or "") or NULL?   Because like I said "Object reference not set to an instance of an object." will occur if you try to convert a NULL to string.  The error means there is no reference to an instance and therefore it is likely to be NULL.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34141927
0
 

Author Comment

by:vcbertini
ID: 34141935
The third record is (10014, 1)
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34141968
By the way, are you still getting the "Enumeration yielded no results" or just the "Object reference not set to an instance of an object."?

Can you see this third record in your reader once it is passed?  You can actually put a condition in the breakpoint to stop when SOreader["AGENCY_SERVICES_INTERIM_ID"] is equal to 1.

Is there at any time SOreader["AGENCY_SERVICES_INTERIM_ID"] equal to NULL?  How did you get the third record (10014,1)?  Is through the debugger or query analyzer?
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34141977
Oh and another thing, in your code snippet, I don't see a SOreader.Close(); at the end.  Do you have that in your code?
0
 

Author Comment

by:vcbertini
ID: 34141985
Okay, here's what I get...

I set a breakpoint at the ListItem line. When the code breaks there, and I check the values of the reader, it has two entries [0] and [1] and I can view the results (see paired values above). If I step into the routine, that's when it errors out with the "Object reference...". When I check the reader at that point, where I viewed the results before now says "Enumeration yielded no results..."

I plugged in a different ID, just to see if maybe there was weird data in the table - this one had ten results (from the query analyzer, sp-execution), but returned [0] through [8], then when I stepped into the routine (again), errored out as above and the results gave me the same "Enumeration..." error and the previous results were gone.

Hope that helps?
0
 

Author Comment

by:vcbertini
ID: 34141997
No, I don't have a close reader... I hate to ask if that's important, or just makes things run more efficiently?
0
 

Author Comment

by:vcbertini
ID: 34142011
Here are the results from the query analyzer (with the new variable)

Running [dbo].[ServicesOffered_MAP] ( @ID = 15541 ).

ENTITY_ID                                                                                                                                                                                                                                                        AGENCY_SERVICES_INTERIM_ID                                                                                                                                                                                                                                      
15541     6
15541     7        
15541     8                                                                                            
15541     9                                                                                            
15541    10                                                                                          
15541    12                                                                                          
15541    1                                                                                            
15541    2                                                                                            
15541    4                                                                                            
15541    5
No rows affected.
(10 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[ServicesOffered_MAP].
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34142088
You need to close the reader after reading it.  You will have another problem if you don't close.  I recall I helped someone fix an issue and the problem was that the reader was not close at the end.

Also, as I recall, there is an issue with the debugger resetting after looking at one row in a data reader.  

Can you try completing the code by putting SOreader.Close(); at the end and then run your code without any debugger and check the result?

Can you also try to isolate ENTITY_ID = 15541 for testing purposes?  See the sample code below.  I also put DBNULL checking in there.

 
SqlCommand cmdSO = new SqlCommand("sp_ServicesOffered_MAP", conn);

                cmdSO.CommandType = CommandType.StoredProcedure;

                cmdSO.Parameters.AddWithValue("@ID", partnerid);



                SqlDataReader SOreader = cmdSO.ExecuteReader();

                CheckBoxList chkbx = (CheckBoxList)Page.FindControl("cblServicesOffered");



                while (SOreader.Read()) 

                {     

                    if(SOreader["ENTITY_ID"] == 15541)

                    {

                      if(SOreader["AGENCY_SERVICES_INTERIM_ID"] != DBNull.Value)

                      {

                        ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                        if (currentCheckBox != null)

                        {

                            currentCheckBox.Selected = true;

                        }

                      }

                    }

                }

                SOreader.Close();

Open in new window

0
 

Author Comment

by:vcbertini
ID: 34142118
Well, my page came up, but none of the checkboxes were checked.  Theoretically, ten of them should have been checked.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34142140
That is weird!

Ok. Try the following steps:

1.  With the existing code, check if code for currentCheckBox is being executed, put a breakpoint inside the DBNull value checking and step through it if it accessed it.  It is possible that the checkbox is not set to true because of something else.

2.  If it doesn't go inside,  Can you then remove the enclosing if for the ENTITY_ID and run it again?
while (SOreader.Read()) 

                {     

                      if(SOreader["AGENCY_SERVICES_INTERIM_ID"] != DBNull.Value)

                      {

                        ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                        if (currentCheckBox != null)

                        {

                            currentCheckBox.Selected = true;

                        }

                      }

                }

                SOreader.Close();

Open in new window

0
 

Author Comment

by:vcbertini
ID: 34142194
We made it inside the if statement and choked on the "ListItem" statement with the same "Object reference not set to an instance of an object."  The value of the reader (from the IF statement) had the same [0] through [8] values, missing [9].

When I tested the ENTITY_ID scenario, I got the following error: Invalid attempt to read when no data is present.

Is it possible that there is some parameter I didn't set in the actual CheckBoxList object?  Here is what I have...  

<asp:CheckBoxList ID="cblServicesOffered" runat="server" BorderStyle="Groove"
                RepeatColumns="3" TabIndex="60" DataSourceID="dsServicesOffered" DataTextField="LIST_ITEM_NAME"
                DataValueField="LIST_ID">
            </asp:CheckBoxList>
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34142227
OK.  Let's isolate each of the component in your code.  Can you try to test if you can output the values?  Something like this:  You can further enhance it if you want.  After that slowly put back the components one at a time.
string TestSTR = string.empty;



                while (SOreader.Read()) 

                {     

                    if(SOreader["ENTITY_ID"] == 15541)

                    {

                      if(SOreader["AGENCY_SERVICES_INTERIM_ID"] != DBNull.Value)

                      {

                        TestStr = TestStr + SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString();                        



                        //ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                        //if (currentCheckBox != null)

                        //{

                        //    currentCheckBox.Selected = true;

                        //}

                      }

                    }

                }

                SOreader.Close();

                Response.Write(TestStr);

Open in new window

0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34142234
Oh by the way, yes, there might be a possibility that there might be a missing item in your CheckBoxList object.
0
 

Author Comment

by:vcbertini
ID: 34142264
It is going through the While {} loop 9 times, each time going to the false value of if SOreader["ENTITY_ID" == "15541"), which means it does not == "15541"

If I take the "" off of the 15541, it says I can't check for == on an int.
0
 

Author Comment

by:vcbertini
ID: 34142275
I replaced == "15541" with != DBNull.Value and got this for the test string:  678910121245

or...  6, 7, 8, 9, 10, 1, 2, 4, 5  (so 12 is missing)
0
 

Author Comment

by:vcbertini
ID: 34142282
Ok... and I just replaced the test string with this:

TestStr = "|" + SOreader["ENTITY_ID"] + "/" + SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString() + "|";

and got this?? One result? Why would that be different just because I added some to the string?

| 15541 / 5 |
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34142323
You remove the other TestStr

TestStr = TestStr + "|" + SOreader["ENTITY_ID"] + "/" + SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString() + "|";
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 21

Expert Comment

by:Alfred1
ID: 34142329
Hey the test string above that is 6,7,8,9,10,12,1,2,4,5  right?  That is ten!

I should have added a comma in there.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34142332
It looks like you are getting the values from the database correctly.  The issue is with your checkboxlist.  just remember that it is a zero-index.  I have a feeling that it might have something to with the zero-index.......
0
 

Author Comment

by:vcbertini
ID: 34145929
No, the "12" was missing, so there were only nine being returned.
0
 

Author Comment

by:vcbertini
ID: 34145946
Oh, and when I removed the AGENCY_SERVICES from the test string, I got one result, and that was the 15541. Technically there should have been 10 versions of that output, correct? Not sure why it's only returning 1.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34146336
In your comment above -------->  I replaced == "15541" with != DBNull.Value and got this for the test string:  678910121245

That is 10.  I should have put a space or comma so that it would be clear.  Separating the number would be 6,7,8,9,10,12,1,2,4,5.

Also, what code construct are you pertaining to in the removal of AGENCY_SERVICES.  As I said before, it looks like you removed the TestStr in the right side of the equation.  You should run it as

TestStr = TestStr + "|" + SOreader["ENTITY_ID"] + "/" + SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString() + "|";

My objective with this exercise is to determine whether we are getting the values from the database before we plug-in the Checkboxlist.  If we can manage to get all the values, it means that there is something wrong with the Checkboxlist construct somehow.  Can you try running your code with the sample TestStr output above to see whether we are getting all the values from the datareader or not?
0
 

Author Comment

by:vcbertini
ID: 34146423
Ah, yes. One of these days I'll learn to read. Sorry. This stuff has me cross-eyed.

I did run the test string multiple ways. One was like you suggested:  TestStr = TestStr + "|" + SOreader["ENTITY_ID"] + "/" + SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString() + "|";  and I got one result, which was the | 15541 / 5 |
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34146589
You were planning to do something like in the code below, right?  And you are only getting one result?  That should not be case, it should be 10.  Anyway, just to confirm, with the original test code I provided above, can you run the process again, with the comma this time  --->   TestStr = TestStr + SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString() + ",";

string TestSTR = string.empty;



                while (SOreader.Read()) 

                {     

                    if(SOreader["ENTITY_ID"] == 15541)

                    {

                      if(SOreader["AGENCY_SERVICES_INTERIM_ID"] != DBNull.Value)

                      {

                         TestStr = TestStr + "|" + SOreader["ENTITY_ID"] + "/" + SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString() + "|";



                        //ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                        //if (currentCheckBox != null)

                        //{

                        //    currentCheckBox.Selected = true;

                        //}

                      }

                    }

                }

                SOreader.Close();

                Response.Write(TestStr);

Open in new window

0
 

Author Comment

by:vcbertini
ID: 34146728
Not sure if it makes a difference, but I had to modify what you gave above to what is below because I was getting an error on:

* TestSTR vs TestStr  (easy fix)
and
* if (SOreader["ENTITY_ID"] == 15541)   vs.  if (SOreader["ENTITY_ID"] == "15541")
Telling me you can't perform an '==' operator on an int. Is there another way to check this?

I got no result from the == "15541" (probably because now it's a string).  At the SOReader ENTITY IF statement, it skips right over the rest, thus it is never true in each iteration through the result set.

changing the "15541" to !DBNull.Value, I get the following:

|15541/6||15541/7||15541/8||15541/9||15541/10||15541/12||15541/1||15541/2||15541/4||15541/5|

Which appears to be ALL records. Woo!   This leads me to believe that the problem lies in the "int" portion of ENTITY_ID.
string TestStr = "";



            while (SOreader.Read())

            {

                if (SOreader["ENTITY_ID"] == "15541")

                {

                    if (SOreader["AGENCY_SERVICES_INTERIM_ID"] != DBNull.Value)

                    {

                        TestStr = TestStr + "|" + SOreader["ENTITY_ID"] + "/" + SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString() + "|";



                        //ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                        //if (currentCheckBox != null)

                        //{

                        //    currentCheckBox.Selected = true;

                        //}

                    }

                }

            }

            SOreader.Close();

            Response.Write(TestStr);

Open in new window

0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34146940
Yes.  Now I remember!  There is an issue with this one that it converts the int into an enum rather than a value and that is why you are getting that "Enumeration yielded no results"

I encountered this a long time ago.

Check the link below.  There is comment in the middle regarding this.

http://stackoverflow.com/questions/370165/datareader-returns-no-results-in-vs-yet-stored-procedure-returns-multiple-result

OK.  Can you now try plugging in the currentCheckBox code and test it out?
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34146958
Oh by the way, take note that C# is case-sensitive.
0
 

Author Comment

by:vcbertini
ID: 34147351
The page is coming up, but still nothing checked in the checkboxes.  I'm still not doing something right. Very confused right now, sorry. This project is over my head for a beginning C# programmer (my expertise is in classic ASP using VB).

Am I supposed to be retrieving a return value from the Stored Procedure? (commented out code where I tried that to no avail).

I also tried casting the
chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

to this:

chkbx.Items.FindByValue(int.Parse(SOreader["AGENCY_SERVICES_INTERIM_ID"]));

but it woluldn't even let me compile.  Something about missing overflow commands in the int.Parse...

Sorry. This is such a pain. Maybe I should consider a different occupation.
// Build the Services Offered command

                SqlCommand cmdSO = new SqlCommand("ServicesOffered_MAP", conn);

                cmdSO.CommandType = CommandType.StoredProcedure;



                //SqlParameter RetVal = cmdSO.Parameters.Add("RetVal", SqlDbType.Int);

                //RetVal.Direction = ParameterDirection.ReturnValue;



                SqlParameter IdIn = cmdSO.Parameters.Add("@ID", SqlDbType.Int, partnerid);

                IdIn.Direction = ParameterDirection.Input;



                SqlDataReader SOreader = cmdSO.ExecuteReader();

                CheckBoxList chkbx = (CheckBoxList)Page.FindControl("cblServicesOffered");



                while (SOreader.Read()) 

                {

                    ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                    if (currentCheckBox != null)

                    {

                        currentCheckBox.Selected = true;

                    }

                

                }

                SOreader.Close();

                conn.Close();

        }

Open in new window

0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34150906
No worries.  Your situation is fully understandable.  

I believe you have a try/catch in your code, obviously if there is an error at the start, it will not tick your checkbox.

By the way, can you put a breakpoint at your line 16 and determine the Items.Count and other relevant values available for chkbx during runtime?

Also, does chkbx.Items(0) exists?  How did you added your items in the chkbx?

I believe the issue now is with the checkboxlist.  We know that SOreader reads your rows properly.
0
 

Author Comment

by:vcbertini
ID: 34152022
Well, back to square 1. My reader is empty, so it's not reading my rows properly anymore. Somehow I broke it.   I've attached the code for my checkbox and how it's populated.
<asp:SqlDataSource ID="dsServicesOffered" runat="server" ConnectionString="" SelectCommand="sp_ServicesOffered" SelectCommandType="StoredProcedure">

                <SelectParameters>

                    <asp:Parameter DefaultValue="F" Name="IsInactive" Type="String" />

                </SelectParameters>

        </asp:SqlDataSource>

<p>

            <asp:Label ID="lblServicesOffered" runat="server" 

                AssociatedControlID="cblServicesOffered" Text="Services Offered" 

                CssClass="black12Bold"></asp:Label>

            <asp:CheckBoxList ID="cblServicesOffered" runat="server" BorderStyle="Groove" 

                RepeatColumns="3" TabIndex="60" DataSourceID="dsServicesOffered" DataTextField="LIST_ITEM_NAME" 

                DataValueField="LIST_ID">

            </asp:CheckBoxList>

        </p>

Open in new window

0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152104
I don't think it is square one, you just need to back track where it worked before.  :-)

Remember, we know that we get all data from data reader.

OK.  I can see that you bound it with dsServicesOffered.  How many items was populated in the checkboxlist?  Is the 10 records from our sample above included in this list?  
0
 

Author Comment

by:vcbertini
ID: 34152160
There are 21 entries in the check box list - those are showing up perfectly. The ten records that were being returned previously would have matched up with what was in the checkbox list. (I tried to embed a screenshot in this - not sure if it worked or not.

The only thing I changed was that I made the partnerID into an int, which it is supposed to be. screenshot.docx
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152227
OK.  Let us approach this differently.  Try the modified code below. This is not really efficient but it is straightforwaed and  I am just attacking your problem at a different angle.
/ Build the Services Offered command

                SqlCommand cmdSO = new SqlCommand("ServicesOffered_MAP", conn);

                cmdSO.CommandType = CommandType.StoredProcedure;



                //SqlParameter RetVal = cmdSO.Parameters.Add("RetVal", SqlDbType.Int);

                //RetVal.Direction = ParameterDirection.ReturnValue;



                SqlParameter IdIn = cmdSO.Parameters.Add("@ID", SqlDbType.Int, partnerid);

                IdIn.Direction = ParameterDirection.Input;



                SqlDataReader SOreader = cmdSO.ExecuteReader();

                CheckBoxList chkbx = (CheckBoxList)Page.FindControl("cblServicesOffered");



                while (SOreader.Read()) 

                {

                    foreach(ListItem currentCheckBox in chkbx.Items)

                    {

                        if(currentCheckBox.Value == SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString())

                        {

                           currentCheckBox.Selected = true;

                        }

                    }



                    //ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                    //if (currentCheckBox != null)

                    //{

                    //    currentCheckBox.Selected = true;

                    //}

                

                }

                SOreader.Close();

                conn.Close();

Open in new window

0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152238
Oh by the way, you can just use your original AddWithValue for your parameter.  It should not be a problem.  I believe the code below worked before, right?

SqlCommand cmdSO = new SqlCommand("sp_ServicesOffered_MAP", conn);
                cmdSO.CommandType = CommandType.StoredProcedure;
                cmdSO.Parameters.AddWithValue("@ID", partnerid);

                SqlDataReader SOreader = cmdSO.ExecuteReader();
                CheckBoxList chkbx = (CheckBoxList)Page.FindControl("cblServicesOffered");
0
 

Author Comment

by:vcbertini
ID: 34152264
The reader is empty, so it just completely bypasses everything in the While statement.  This has to be a problem with the parameter being pushed into the stored procedure.

I'm convinced the problem is here:

SqlParameter IdIn = cmdSO.Parameters.Add("@ID", SqlDbType.Int, partnerid);
                IdIn.Direction = ParameterDirection.Input;
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152278
Just change it to your original code.  I am providing your original plus my modification below:
SqlCommand cmdSO = new SqlCommand("sp_ServicesOffered_MAP", conn);

                cmdSO.CommandType = CommandType.StoredProcedure;

                cmdSO.Parameters.AddWithValue("@ID", partnerid);



                SqlDataReader SOreader = cmdSO.ExecuteReader();

                CheckBoxList chkbx = (CheckBoxList)Page.FindControl("cblServicesOffered");



                while (SOreader.Read()) 

                {     

                    foreach(ListItem currentCheckBox in chkbx.Items)

                    {

                        if(currentCheckBox.Value == SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString())

                        {

                           currentCheckBox.Selected = true;

                        }

                    }



                    //ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                    //if (currentCheckBox != null)

                    //{

                    //    currentCheckBox.Selected = true;

                    //}



                }

                SOreader.Close();

                conn.Close();

Open in new window

0
 

Author Comment

by:vcbertini
ID: 34152283
Ok, I put the stored procedure back to where it was and now the reader has rows again.

This line:  foreach(ListItem currentCheckBox in chkbx.Items)
produced this error:  Object reference not set to an instance of an object.  (specifically pointing to the foreach keyword)
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152289
I use AddWithValue in seven of my web application projects that use a datareader.  So it should be OK.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152296
Is chkbx.Items empty?!?

Can you put a breakpoint on that line and check the Items.Count?

Can you also step into each line one at a time as well?  

This is just weird.
0
 

Author Comment

by:vcbertini
ID: 34152313
it's telling me that chkbox.Items is null then won't let me step in further because of the error.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152318
OK.  I think this line

CheckBoxList chkbx = (CheckBoxList)Page.FindControl("cblServicesOffered");

is not working.  

Try using a FindControlRecursive to locate your checkboxlist
SqlCommand cmdSO = new SqlCommand("sp_ServicesOffered_MAP", conn);

                cmdSO.CommandType = CommandType.StoredProcedure;

                cmdSO.Parameters.AddWithValue("@ID", partnerid);



                SqlDataReader SOreader = cmdSO.ExecuteReader();

                //CheckBoxList chkbx = (CheckBoxList)Page.FindControl("cblServicesOffered");

                CheckBoxList chkbx = FindControlRecursive(Page,"cblServicesOffered");



                while (SOreader.Read()) 

                {     

                    foreach(ListItem currentCheckBox in chkbx.Items)

                    {

                        if(currentCheckBox.Value == SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString())

                        {

                           currentCheckBox.Selected = true;

                        }

                    }



                    //ListItem currentCheckBox = chkbx.Items.FindByValue(SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString());

                    //if (currentCheckBox != null)

                    //{

                    //    currentCheckBox.Selected = true;

                    //}



                }





public static Control FindControlRecursive(Control Root, string Id)



{



    if (Root.ID == Id)



        return Root;



 



    foreach (Control Ctl in Root.Controls)



    {



        Control FoundCtl = FindControlRecursive(Ctl, Id);



        if (FoundCtl != null)



            return FoundCtl;



    }



 



    return null;



}

Open in new window

0
 

Author Comment

by:vcbertini
ID: 34152339
Getting this error during compile:

Cannot implicitly convert type 'System.Web.UI.Control' to 'System.Web.UI.WebControls.CheckBoxList'. An explicit conversion exists (are you missing a cast?)

on this line:

CheckBoxList chkbx = FindControlRecursive(Page, "cblServicesOffered");

FindControlRecursive(Page, "cblServicesOffered"); is underlined in red.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152347
Oops sorry.  it should be

CheckBoxList chkbx = (CheckBoxList) FindControlRecursive(Page,"cblServicesOffered");

Oh by the way, have you send the FindControlRecursive function below my previous post.  I hope you added it in your code.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152349
Oh mistyped, have you seen the FindControlRecursive function?
0
 

Author Comment

by:vcbertini
ID: 34152370
Yes, got that function.  Ran the code and the checkboxes are empty. It is finding the control, however, but when I step through, it goes from the foreach to the closing bracket - never even hits the "if" statement. Skips it each time through.  Does this mean there are no items in my checkbox list?

If that is the case, then why are they showing on the page?
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152380
You need to inspect each value of currentCheckBox.Value.  

You might need to do the following

if(currentCheckBox.Value == SOreader["AGENCY_SERVICES_INTERIM_ID"])

or

if(currentCheckBox.Value.ToString() == SOreader["AGENCY_SERVICES_INTERIM_ID"].ToString())

0
 

Author Comment

by:vcbertini
ID: 34152389
It's never making it that far to the if statement. Just skips right over it from the opening bracket of the foreach statement to the end one.
0
 
LVL 21

Expert Comment

by:Alfred1
ID: 34152413
OK.  Once you hit the foreach line at breakpoint, did you step into the code?

Or have you inspected the chkbx.Items?  What is the Items.Count at this stage?  Can you please investigate the contents of chkbx.Items if there is any inside of it?

Is it still empty?  I think it is not but you have to give me more details that it is just skipping.
0
 

Author Comment

by:vcbertini
ID: 34152425
Yes, I am stepping through the code.

Items.Count = 0
0
 
LVL 21

Accepted Solution

by:
Alfred1 earned 500 total points
ID: 34152456
Items.Count = 0 means there are no items in your CheckBoxList.........

Is that right?!?

By the way, when you bind your checkboxlist, did you do the following:

cblServicesOffered.DataBind();

You usually do this in page load.

You need to verify the contents of cblServicesOffered.  Your problem lies in accessing the list inside this control.  It looks like the control exists but the items are not binded and that is why you are getting this problem.  If you manage to resolve this, then I believe your problem will be solved.  The checkboxlist access is the problem from the get go.
0
 

Author Closing Comment

by:vcbertini
ID: 34152479
That worked. Thank you SO much for your hard work, time and dedication to this problem. It is appreciated more than you know. I wish I could give you even more points for this. Kudos to you.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now