Update DB through Gridview selected DDL from DataSource

Question

I have one gridview which is connected through SQL Data source from one table and I have enabled the EDIT and delete columns of gridview. When I'm Editing the value of row, a column is having DDL in Edit mode instead of textbox connected through another SQL datasource.

Once the page loads, after clicking the Edit Row of the Gridview, i'm selecting another value from dropdown and after clicking update it giving me error, can not insert null values into database.

<%@ Page Title="" Language="C#" MasterPageFile="~/Master.Master" AutoEventWireup="true" CodeBehind="CreateRoster.aspx.cs" Inherits="E_Scheduler.CreateRoster" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
    <link href="CSS/Addroster.css" rel="stylesheet" />
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <div class="maindiv">




        <asp:GridView ID="GridView1" runat="server" ShowFooter="True" AutoGenerateColumns="False" BackColor="#

    CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellPadding="4" CellSpacing="2" DataKeyNames="id" DataSourceID="SqlDataSource1" ForeColor="Black">
                <Columns>
                    <asp:CommandField ShowEditButton="True" ShowDeleteButton="True"></asp:CommandField>
                    <asp:TemplateField HeaderText="ID" InsertVisible="False" SortExpression="id">
                        <EditItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("id") %>'></asp:Label>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("id") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Name" SortExpression="username">
                        <EditItemTemplate>
                           <%-- <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("username") %>'></asp:TextBox>--%>
                            <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="username" DataValueField="username"></asp:DropDownList>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label2" runat="server" Text='<%# Bind("username") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Date" SortExpression="r_date">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("r_date") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label3" runat="server" Text='<%# Bind("r_date") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="From" SortExpression="from_time">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("from_time") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label4" runat="server" Text='<%# Bind("from_time") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="To" SortExpression="to_time">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("to_time") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label5" runat="server" Text='<%# Bind("to_time") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                <FooterStyle BackColor="#CCCCCC" />
                <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
                <RowStyle BackColor="White" />
                <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                <SortedAscendingCellStyle BackColor="#F1F1F1" />
                <SortedAscendingHeaderStyle BackColor="#808080" />
                <SortedDescendingCellStyle BackColor="#CAC9C9" />
                <SortedDescendingHeaderStyle BackColor="#383838" />
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DBCS %>" DeleteCommand="DELETE FROM [roster] WHERE [id] = @id" InsertCommand="INSERT INTO [roster] ([username], [r_date], [from_time], [to_time]) VALUES (@username, @r_date, @from_time, @to_time)" SelectCommand="SELECT [id], [username], [r_date], [from_time], [to_time] FROM [roster]" UpdateCommand="UPDATE [roster] SET [username] = @username, [r_date] = @r_date, [from_time] = @from_time, [to_time] = @to_time WHERE [id] = @id">
                <DeleteParameters>
                    <asp:Parameter Name="id" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="username" Type="String" />
                    <asp:Parameter DbType="Date" Name="r_date" />
                    <asp:Parameter Name="from_time" Type="String" />
                    <asp:Parameter Name="to_time" Type="String" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="username" Type="String" />
                    <asp:Parameter DbType="Date" Name="r_date" />
                    <asp:Parameter Name="from_time" Type="String" />
                    <asp:Parameter Name="to_time" Type="String" />
                    <asp:Parameter Name="id" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>

            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString='<%$ ConnectionStrings:DBCS %>' SelectCommand="SELECT [username] FROM [users]"></asp:SqlDataSource>


        </div>
    </asp:Content>

R.C R.C · Accepted Answer · 2020-02-16T11:58:57

You need to send BACK the SelectedValue of your dropdownlist:

Use this: SelectedValue='<%# Bind("username")%>' as:

<EditItemTemplate>                           
      <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" 
           DataTextField="username" DataValueField="username" 
           SelectedValue='<%# Bind("username")%>'>
      </asp:DropDownList>
</EditItemTemplate>

Update DB through Gridview selected DDL from DataSource

1 Answers