Solved

populate dropdownlist from another dropdownlist where each list has AppendDataBoundItems="true"

Posted on 2015-02-23
23
140 Views
Last Modified: 2015-03-10
hi Experts,

I have 2 ddls each bound to a sqldatasource; on the first ddl I have set autopostback to true and I'm using the selected index change event to capture the selected value in a session variable which then filters the data in the second ddl.

The Two Bound DDLs:
 
<table width="100%" cellspacing="0" cellpadding="8">
                                    <tr valign="middle" align="left" style="background-color: aliceblue;">
                                    <td>Client No:</td>
                                <td><asp:DropDownList ID="DDLClientNo" DataSourceID="ClientNo" runat="server" OnSelectedIndexChanged="DDLClientNo_SelectedIndexChanged" 
                                    AutoPostBack="true" DataTextField="client_ref" DataValueField="client_ref" AppendDataBoundItems="true">
                                    <asp:ListItem Text="n/a" Value="0" />
                                    </asp:DropDownList>

                                    <asp:SqlDataSource ID="ClientNo" ConnectionString="<%$ connectionStrings:xxxxxxxxx %>"
        SelectCommand="Select DISTINCT client_ref from matter order by client_ref asc" runat="server"></asp:SqlDataSource>
                                </td>
                                 <td>Matter Name:</td>
                                        <td><asp:DropDownList ID="DDLMatterName" DataSourceID="MatterName" runat="server" 
                                            DataTextField="matter_suffix" DataValueField="matter_suffix" AppendDataBoundItems="true" >
                                            <asp:ListItem Text="n/a" Value="0" />
                                            </asp:DropDownList>
                                            <asp:SqlDataSource ID="MatterName" ConnectionString="<%$ connectionStrings:xxxxxxx %>"
        SelectCommand="Select DISTINCT matter_suffix from matter where client_ref=@client_ref order by matter_suffix" runat="server">
                                                <SelectParameters>
                                                    <asp:SessionParameter Name="client_ref" SessionField="client_ref" />
                                                </SelectParameters>
                                            </asp:SqlDataSource>
                                        </td></tr></table>

Open in new window

The Code Behind:
 protected void DDLClientNo_SelectedIndexChanged(object sender, EventArgs e)
    {
        DropDownList ddList = sender as DropDownList;
        Session["client_ref"] = ddList.SelectedValue.ToString();
    }

Open in new window

As indicated in the code above I want to include an 'n/a' option in each ddl, and if the user selects that value in the first ddl the 'n/a' value should be the only option in the second ddl.

However I now have a situation where the user is potentially going to select a value which doesn't exist in the sqldatasource and in fact whatever value they select from the first ddl, the data in the second ddl no longer filters, is there a way round this?

The alternative would be to add a checkbox to my form ahead of the ddls so they only show if relevant, I'm just curious to see if this can work without...

many thanks in adv.
0
Comment
Question by:forsters
  • 12
  • 11
23 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40628379
I don't understand your question.  Does the DropDownList function correctly, until a value is selected that does not exist in the data source?
0
 

Author Comment

by:forsters
ID: 40630324
It functions correctly until I append the two n/a values; without those it works, with them it doesn't matter what I select in the first ddl, the second one will display all the data, it no longer filters...
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40630703
Do the two N/A values have the same "value" attribute?
0
 

Author Comment

by:forsters
ID: 40630735
I've set both to 0
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40630747
The values need to be unique.
0
 

Author Comment

by:forsters
ID: 40632721
It appears to make no difference.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40633261
Is this how you are filtering?

                                      <asp:SqlDataSource ID="MatterName" ConnectionString="<%$ connectionStrings:xxxxxxx %>"
        SelectCommand="Select DISTINCT matter_suffix from matter where client_ref=@client_ref order by matter_suffix" runat="server">
                                                <SelectParameters>
                                                    <asp:SessionParameter Name="client_ref" SessionField="client_ref" />
                                                </SelectParameters>
                                            </asp:SqlDataSource>
 

Open in new window

0
 

Author Comment

by:forsters
ID: 40633307
yes - I have actually removed the 'DISTINCT' from that statement as it's not necessary.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40633465
I see where you set the Session parameter here:

Session["client_ref"] = ddList.SelectedValue.ToString();

Open in new window


Is this value what you need it to be to filter correctly?
0
 

Author Comment

by:forsters
ID: 40633498
yes that's correct, so that is picking up the selected value from the first ddl and using it to filter the data of the second, it's a one to many relationship in the database table, so each client_ref has a number of matter_suffix.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40633532
If it is the value that you expect, then what is the impact of this statement?

"they select from the first ddl, the data in the second ddl no longer filters"

That would suggest that the value in the WHERE clause is incorrect.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:forsters
ID: 40633606
Well no, the problem only occurs once I append the two additional values (which of course are not within the database) so I am not surprised that at this point the filtering breaks but it has nothing to do with the value in the where clause it is because I am appending onto that an additional value 0. I know this. I don't know if there is a workaround or whether it is simpler to approach differently.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40633617
I still don't understand the issue.  If the WHERE clause is correct, and the proper values are returned from the database, are you saying that the DropDownList does not show the correct items?
0
 

Author Comment

by:forsters
ID: 40633663
The code below filters as expected - that works.

The code I posted in the original question which includes the appended list items does not.    

<td>Client No:</td>
                                    <td>
                                        <asp:DropDownList ID="DDLClientNo" DataSourceID="ClientNo" runat="server" OnSelectedIndexChanged="DDLClientNo_SelectedIndexChanged"
                                            AutoPostBack="true" DataTextField="client_ref" DataValueField="client_ref">
                                        </asp:DropDownList>

                                        <asp:SqlDataSource ID="ClientNo" ConnectionString="<%$ connectionStrings:xxxxxx%>"
                                            SelectCommand="Select DISTINCT client_ref from matter order by client_ref asc" runat="server"></asp:SqlDataSource>
                                    </td>
                                    <td>Matter No:</td>
                                    <td>
                                        <asp:DropDownList ID="DDLMatterNo" DataSourceID="MatterNo" runat="server" AutoPostBack="true"
                                            DataTextField="matter_suffix" DataValueField="matter_suffix" OnSelectedIndexChanged="DDLMatterNo_SelectedIndexChanged">
                                        </asp:DropDownList>
                                        <asp:SqlDataSource ID="MatterNo" ConnectionString="<%$ connectionStrings:xxxxxx %>"
                                            SelectCommand="Select matter_suffix from matter where client_ref=@client_ref order by matter_suffix" runat="server">
                                            <SelectParameters>
                                                <asp:SessionParameter Name="client_ref" SessionField="client_ref" />
                                            </SelectParameters>
                                        </asp:SqlDataSource>
                                    </td>

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40633700
What is the difference when adding the N/A items?

      <asp:ListItem Text="n/a" Value="0" />

Do the data-bound items have value > 0?
0
 

Author Comment

by:forsters
ID: 40633716
Yes the Databound items are all values >0, the second ddl appears to return a lot more data that without the n/a values, but because I am unfamiliar with the data itself it's quite hard to see what exactly is going on. but whereas prior to the n/a values the second ddl might filter down to just one record - the same selection with the n/a values appended will return 30+ items so it's fairly clear that it isn't filtering as it was.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40633982
"the same selection with the n/a values appended will return 30+ items so it's fairly clear that it isn't filtering as it was."
What is the value for this selection here?

Session["client_ref"] = ddList.SelectedValue.ToString();

Open in new window

0
 

Author Comment

by:forsters
ID: 40634953
that depends what the user selects from the first dropdown
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 40636136
Something has to explain why there would be a difference when you add the N/A item, but I haven't seen anything yet, so I can't suggest a different approach, yet...
0
 

Assisted Solution

by:forsters
forsters earned 0 total points
ID: 40639203
I did a bit of googling on this and it seems this might be a generic problem with trying to filter ddls with appended data - the last few comments in the link below suggest a possible workaround which I will try but it otherwise looks like a non-starter :

http://weblogs.asp.net/scottgu/436804

thanks for your help, I will update if I get the workaround to play nicely..
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40639694
You might try to add the items in the SqlDataSource.Selecting event handler, and not using AppendDataboundItems.

Example:

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        SqlDataSource1.SelectParameters["OwnerID"].DefaultValue = Membership.GetUser(true).ProviderUserKey.ToString();
    }

Open in new window

0
 

Assisted Solution

by:forsters
forsters earned 0 total points
ID: 40647492
I have ended up using the union but it doesn't resolve anything, just a bit neater than appending the data. I will make do with that and a check box, it's messy but will have to do.
Thanks for the input.
0
 

Author Closing Comment

by:forsters
ID: 40655800
Essentially the question is unresolved but it seems that what I want to do is not easily done, I have ended up using a work-around
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

12 Experts available now in Live!

Get 1:1 Help Now