SQLDataSource inside GridView

Question

for the past days i've been trying to find a solution for my problem but so far i couldn't. I Have a Gridview that in edit mode, one of the fields have a DropDownList of people that is populated by a Select that uses two other fields of that row (State and City). I tried everything i found on the web about it and i couldn't make it work. Can you help me please? Code below

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"  
        DataKeyNames="id,Municipio,UF" DataSourceID="SqlDataSource5">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:CommandField ShowEditButton="True" />
            ...stuff...
            <asp:TemplateField HeaderText="UF" SortExpression="UF">
                <EditItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("UF") %>'>
                    </asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("UF") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Municipio" SortExpression="Municipio">
                <EditItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Eval("Municipio") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("Municipio") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="People" SortExpression="People">
                <ItemTemplate>
                    <asp:Label ID="LbPeople" runat="server" Text='<%# Bind("Cultura") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:DropDownList ID="DdlPeople" runat="server" AutoPostBack="true" 
                        DataSourceID="SqlDataSourcePeopleEdit" DataTextField="People" 
                        DataValueField="People">
                    </asp:DropDownList>
                    <asp:HiddenField ID="Hidden1" Value='<%# Bind("Municipio") %>' runat="server"/>
                    <asp:HiddenField ID="Hidden2" Value='<%# Bind("UF") %>' runat="server"/>
                    <asp:SqlDataSource ID="SqlDataSourcePeopleEdit" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:dbdb %>" 
                        ProviderName="<%$ ConnectionStrings:somar.ProviderName %>"
                        SelectCommand="SELECT People FROM Table_People WHERE (Cidade = @Municipio) AND (UF = @UF)"
                        CancelSelectOnNullParameter="false">
                        <SelectParameters>
                            <asp:ControlParameter ControlID="Hidden1" Name="Municipio" PropertyName="Value" Type="String" />
                            <asp:ControlParameter ControlID="Hidden2" Name="UF"        PropertyName="Value" Type="String" />
                        </SelectParameters>
                    </asp:SqlDataSource>
                </EditItemTemplate>
            </asp:TemplateField>
       ...stuff...     
    </asp:GridView>

What am i doing wrong?

UPDATE when i click edit, the dropdownlist does not load values. And if i set the property CancelSelectOnNullParameter="false" on SqlDataSource it shows 'Must declare scalar variable @xxx' I have inspected the hiddenfields and they have values loaded, so the problem is the controlparameter taht does not get the value from it

You forgot to mention what exactly is not working. Exception? DDL ends up empty? What's the actual problem? — Andrei, Apr 19 '17 at 13:01
Sorry for that. The Ddl does not have values, if i set the property CancelSelectOnNullParameter="false" on SqlDataSource it shows 'Must declare scalar variable @xxx' — Gabriel Marques, Apr 19 '17 at 13:03
You're declaring `DataKeyNames` with 3 fields (`DataKeyNames="id,Municipio,UF"`), which `ControlParameter` only supplies 2 fields with parameters (omitting `id` as other required parameter). Try removing unnecessary column field names from `DataKeyNames` property. — Tetsuya Yamamoto, Apr 20 '17 at 02:51

score 0 · Answer 1 · answered Jul 02 '18 at 23:16

What you should do is have the hidden fields and the sqldatasource before the dropdownlist. The issue you are experiencing is due to when the data is loaded.

<asp:GridView ID="GridView1" runat="server" AllowSorting="True"  
    DataKeyNames="id,Municipio,UF" DataSourceID="SqlDataSource5">
    <AlternatingRowStyle BackColor="White" />
    <Columns>
        <asp:CommandField ShowEditButton="True" />
        ...stuff...
        <asp:TemplateField HeaderText="UF" SortExpression="UF">
            <EditItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Eval("UF") %>'>
                </asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Bind("UF") %>'>
                </asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Municipio" SortExpression="Municipio">
            <EditItemTemplate>
                <asp:Label ID="Label2" runat="server" Text='<%# Eval("Municipio") %>'></asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="Label2" runat="server" Text='<%# Bind("Municipio") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="People" SortExpression="People">
            <ItemTemplate>
                <asp:Label ID="LbPeople" runat="server" Text='<%# Bind("Cultura") %>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:HiddenField ID="Hidden1" Value='<%# Bind("Municipio") %>' runat="server"/>
                <asp:HiddenField ID="Hidden2" Value='<%# Bind("UF") %>' runat="server"/>
                <asp:SqlDataSource ID="SqlDataSourcePeopleEdit" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:dbdb %>" 
                    ProviderName="<%$ ConnectionStrings:somar.ProviderName %>"
                    SelectCommand="SELECT People FROM Table_People WHERE (Cidade = @Municipio) AND (UF = @UF)"
                    CancelSelectOnNullParameter="false">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="Hidden1" Name="Municipio" PropertyName="Value" Type="String" />
                        <asp:ControlParameter ControlID="Hidden2" Name="UF"        PropertyName="Value" Type="String" />
                    </SelectParameters>
                </asp:SqlDataSource>

                <asp:DropDownList ID="DdlPeople" runat="server" AutoPostBack="true" 
                    DataSourceID="SqlDataSourcePeopleEdit" DataTextField="People" 
                    DataValueField="People">
                </asp:DropDownList>
            </EditItemTemplate>
        </asp:TemplateField>
   ...stuff...     
</asp:GridView>

Hello. the time i wrote this question i was in a job of another company and i can't test this solution anymore. Sorry — Gabriel Marques, Jul 18 '18 at 14:31
OK I see. Hopefully if someone else experience a similar problem they will confirm that this worked for them. I had a similar problem and I found that this resolved the problem for me, which is why I decided to share it here. — Winnifred, Jul 19 '18 at 15:40

SQLDataSource inside GridView

1 Answers1