Error when using a DDL for the <EditItemTemplate> of a LIstView control

Question

User-830563764 posted

In a VS2010/ASP.NET4 webpage, I have had a ListView control for editing and adding data.  Inside the <EditItemTemplate>, I added a DDL:
<td><asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource7"
DataTextField="Unit" DataValueField="UnitID"
SelectedValue='<%# Bind("UnitID") %>' AutoPostBack="True"></asp:DropDownList></td>

While editing a data record, the DDL did show up for the Unit field.  I changed the Unit and clicked Update button, then an error popped up:
Cannot insert the value NULL into column 'UnitID', table 'Employee'.  

What's wrong? 

Answer 1

User-830563764 posted

The DDL in the <EditItemTemplate> of the GrdiView worked fine in my other webpage.  I can edit and update the UnitID column.  With the GridView control, we can not add the new data.  That is the reason why I tried using ListView control. 

Answer 2

User-691209617 posted

The error is pretty self explanatory. You are trying to update a value in a database field that does not accept empty values, but you are sending an empty one. The problem probably lies in the code that gets the value from the ListView and sends it to the database. Update your question with that code snippet

Answer 3

User-830563764 posted

Reviewed the GridView and ListView codes and found something different between these two controls.

The GridView used TemplateField, instead of BoundField, for the DDL.  
<asp:TemplateField HeaderText="UnitID" SortExpression="UnitID">
  <EditItemTemplate><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
  DataSourceID="SqlDataSource5"  DataTextField="Unit" DataValueField="UnitID"
  SelectedValue='<%# Bind("UnitID") %>'>  </asp:DropDownList>
   </EditItemTemplate>;
and an ItemTemplate:
   <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("UnitID") %>'></asp:Label>
   </ItemTemplate> </asp:TemplateField>

I just added the DDL to the EditItemTemplate of the ListView:
 <EditItemTemplate> <td><asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource7"
  DataTextField="Unit" DataValueField="UnitID" SelectedValue='<%# Bind("UnitID") %>' AutoPostBack="True"></asp:DropDownList></td>


The ItemTemplate of the ListView showed:
< ItenTemplate> <asp:Label ID="UnitIDLabel" runat="server" Text='<%# Eval("UnitID") %>' />

Apparently, my ListView is missing some codes for the Text='<%# Bind("UnitID") %>.  Where should I add this ItemTemplate to the ListView? 

Answer 4

User-1716253493 posted

Seem like the ddl selectedvalue is not passed in update command

First check update command, you should set UnitID value from UnitID parameter

UPDATE yourtable SET UnitID=@UnitID ...

You should have UnitID parameter in UpdateParameters collection

        <UpdateParameters>






            <asp:Parameter Name="UnitID" Type="Int32" />
            <asp:Parameter Name="Id" Type="Int32" />
        </UpdateParameters>

Then in ddl

SelectedValue='<%# Bind("UnitID") %>'

Answer 5

User-830563764 posted
Not quite understand the codes you suggested. Codes in SqlDataSource? Or ListView source codes?
I think I already have had theses codes for updating in SqlDataSource and ListView source codes. Will
review the codes again tomorrow. How about the codes for <ItemTemplate>?







<asp:Parameter Name="UnitID" />
<asp:Parameter Name="Id" Type="Int32" />
</UpdateParameters>

Answer 6

User-1716253493 posted

I guess the updatecommand and updateparameters code for sqldatasource that you use as ListView DataSource

Can you post the lisview codes and sqldatasource codes the listview datasource?

I'm not sure about your update button, update Button or button with commandname="update" is in edit mode or inside EditItemTemplate

It's related to sqldatasource UpdateParameters and UpdateCommand

Next, when you want post a question to this site, for a better view, post the codes by clicking insert code icon between HTML & Help icon

Answer 7

User-691209617 posted

You need to post the snippet that sends the data to the database.

Answer 8

User-830563764 posted

A possible problem is: the record is UnitID, DDL is to show the Unit, and the selected is UnitID.  
I have another webpage with FormView.  The DDL editing/inserting FormView worked fine, but the DDL DataTextField and DataValueField are the same. 

Answer 9

User-830563764 posted

I manually added the <asp:DropDownList> into the <EditItemTemplate>

 

  <asp:ListView ID="ListView2"

             runat="server"

             DataKeyNames="EmployeeID"

             DataSourceID="SqlDataSource3"

             InsertItemPosition="LastItem">

      <AlternatingItemTemplate>

                 <tr style="">

                     <td>

                         <asp:Button ID="EditButton"

                             runat="server" CommandName="Edit"

                             Text="Edit" />

                     </td>

                     <td>

                         <asp:Label ID="EmployeeIDLabel"

                             runat="server"

                             Text='<%# Eval("EmployeeID") %>' />

                     </td>

                     <td>

                         <asp:Label ID="OOLIDLabel"

                             runat="server"

                             Text='<%# Eval("OOLID") %>' />

                     </td>

                     ....

                 </tr>

    </AlternatingItemTemplate>

    <EditItemTemplate>

                 <tr style="">

                     <td>

                         <asp:Button ID="UpdateButton"

                             runat="server" CommandName="Update"

                             Text="Update" />

                         <asp:Button ID="CancelButton"

                             runat="server" CommandName="Cancel"

                             Text="Cancel" />

                     </td>

                     <td>

                         <asp:Label ID="EmployeeIDLabel1"

                             runat="server"

                             Text='<%# Eval("EmployeeID") %>' />

                     </td>

                     <td>

                         <asp:TextBox ID="OOLIDTextBox"

                             runat="server"

                             Text='<%# Bind("OOLID") %>' />

                     </td>

                    

                     <td>

              <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource7"

                     DataTextField="Unit" DataValueField="UnitID" SelectedValue='<%# Bind("UnitID") %>' AutoPostBack="True">

                     </asp:DropDownList>

                     </td>

                     <!--<td>

                         <asp:TextBox ID="UnitIDTextBox"

                             runat="server"

                             Text='<%# Bind("UnitID") %>' />

                    </td>-->

                    

      </EditItemTemplate>

             <EmptyDataTemplate>

                 <table runat="server" style="">

                     <tr>

                         <td>

                             No data was returned.</td>

                     </tr>

                 </table>

             </EmptyDataTemplate>

             <InsertItemTemplate>

                 <tr style="">

                   

                 </tr>

             </InsertItemTemplate>

             <ItemTemplate>

                 <tr style="">

                     <td>

                         <asp:Button ID="EditButton"

                             runat="server" CommandName="Edit"

                             Text="Edit" />

                     </td>

                     <td>

                         <asp:Label ID="EmployeeIDLabel"

                             runat="server"

                             Text='<%# Eval("EmployeeID") %>' />

                     </td>

                     <td>

                         <asp:Label ID="OOLIDLabel"

                             runat="server"

                             Text='<%# Eval("OOLID") %>' />

                     </td>

                     ...

                 </tr>

             </ItemTemplate>

             <LayoutTemplate>

                 ...

             </LayoutTemplate>

             <SelectedItemTemplate>

                 <tr style="">

                     <td>

                         <asp:Button ID="EditButton"

                             runat="server" CommandName="Edit"

                             Text="Edit" />

                     </td>

                     <td>

                         <asp:Label ID="EmployeeIDLabel"

                             runat="server"

                             Text='<%# Eval("EmployeeID") %>' />

                     </td>

                     <td>

                         <asp:Label ID="OOLIDLabel"

                             runat="server"

                             Text='<%# Eval("OOLID") %>' />

                     </td>

                     ...

             </SelectedItemTemplate>

         </asp:ListView>

         <asp:SqlDataSource ID="SqlDataSource3"

             runat="server"

             ConnectionString="<%$ ConnectionStrings:EmployeeTestConnectionString %>"

             DeleteCommand="DELETE FROM [Employee] WHERE [EmployeeID] = @EmployeeID"

             InsertCommand="INSERT INTO [Employee] ([OOLID], ) VALUES (@OOLID, @LASTNAME, "

             SelectCommand="SELECT * FROM [Employee] WHERE ([EmployeeID] = @EmployeeID)"

             UpdateCommand="UPDATE [Employee] SET [OOLID] = @OOLID, [.... WHERE [EmployeeID] = @EmployeeID">

             <DeleteParameters>

                 <asp:Parameter Name="EmployeeID"

                     Type="Int32" />

             </DeleteParameters>

             <InsertParameters>

                 <asp:Parameter Name="OOLID"

                     Type="Int32" />

                 ....

             </InsertParameters>

             <SelectParameters>

                 <asp:ControlParameter ControlID="GridView1"

                     Name="EmployeeID"

                     PropertyName="SelectedValue"

                     Type="Int32" />

             </SelectParameters>

             <UpdateParameters>

                 <asp:Parameter Name="OOLID"

                     Type="Int32" />

                 <asp:Parameter Name="LASTNAME"

                     Type="String" />

                 ....

             </UpdateParameters>

         </asp:SqlDataSource>

         <asp:SqlDataSource ID="SqlDataSource7"

             runat="server"

             ConnectionString="<%$ ConnectionStrings:EmployeeTestConnectionString %>"

             SelectCommand="SELECT [UnitID], [Unit] FROM [Unit] order by [Unit] ASC">

         </asp:SqlDataSource>

Answer 10

User-1716253493 posted

The important part is SqlDataSource3 UpdateCommand and UpdateParameters, why you hide it in ... ?

If you can save UnitID using TextBox

<asp:TextBox ID="UnitIDTextBox" runat="server" Text='<%# Bind("UnitID") %>' />

The dropdown should work also

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource7" DataTextField="Unit"
     DataValueField="UnitID" SelectedValue='<%# Bind("UnitID") %>' AutoPostBack="True">
</asp:DropDownList>

To save the data in EditItemTemplate You need update button, i don't see the button

<asp:Button ID="UpdateButton" runat="server" CommandName="Update" Text="Save changes" />

 

Answer 11

User1724605321 posted

Hi wonjartran,

Base on your code , i create a demo using dropdownlist in EditItemTemplate of listview . Bind the dropdownlist with :

<asp:DropDownList ID="Dropdownlist2" runat="server" DataSourceID="SqlDataSource2"
                                    DataTextField="Unit" DataValueField="UnitID"
                                    SelectedValue='<%# Bind("UnitID") %>' AutoPostBack="True">
                                </asp:DropDownList>

After click update button , unitID update in the other table (here i use user table , you are using Employee table ) . code below is for your reference :

      <EditItemTemplate>
            <tr style="background-color:#008A8C;color: #FFFFFF;">
                <td>
                    <asp:Button ID="UpdateButton" runat="server" CommandName="Update" Text="Update" />
                    <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Cancel" />
                </td>
                <td>
                    <asp:Label ID="IdLabel1" runat="server" Text='<%# Eval("Id") %>' />
                </td>
                <td>
                    <asp:TextBox ID="NameTextBox" runat="server" Text='<%# Bind("Name") %>' />
                </td>
                <td>
                    <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />
                </td>
                <td>
                   <asp:DropDownList ID="Dropdownlist2" runat="server" DataSourceID="SqlDataSource2"
                                    DataTextField="Unit" DataValueField="UnitID"
                                    SelectedValue='<%# Bind("UnitID") %>' AutoPostBack="True">
                                </asp:DropDownList>
                </td>
            </tr>
        </EditItemTemplate>

And the sqldatasource :

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>" DeleteCommand="DELETE FROM [TB_User] WHERE [Id] = @Id" InsertCommand="INSERT INTO [TB_User] ([Name], [Address], [UnitID]) VALUES (@Name, @Address, @UnitID)" SelectCommand="SELECT [Id], [Name], [Address], [UnitID] FROM [TB_User]" UpdateCommand="UPDATE [TB_User] SET [Name] = @Name, [Address] = @Address, [UnitID] = @UnitID WHERE [Id] = @Id">
            <DeleteParameters>
                <asp:Parameter Name="Id" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="Address" Type="String" />
                <asp:Parameter Name="UnitID" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="Address" Type="String" />
                <asp:Parameter Name="UnitID" Type="String" />
                <asp:Parameter Name="Id" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
        <asp:sqldatasource ID="SqlDataSource2"  runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>" SelectCommand="SELECT [UnitID],[Unit] FROM [TB_Unit]"></asp:sqldatasource>

Best Regards,

Nan Yu

Answer 12

User-830563764 posted

Yes, I have had the UpdateButton>

<EditItemTemplate><tr style=""><td>

<asp:Button ID="UpdateButton" runat="server" CommandName="Update" Text="Update" />

<asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Cancel" />

</td>

"If you can save UnitID using TextBox"???    I tried adding both, and  both textbox and DDL were displayed. but it always showed old UnitID only after clicking Update.

One strange question: Every time I clicked Refresh Schema on my ListView/SqlDataSource, the <DropDownList> in <EditItemTemplate> codes were removed and <Texbox> codes were restored.

Answer 13

User-830563764 posted

I think I solved the problem.

While adding the <DropDownList> codes to the <EditItemTemplate>, I used <!--   --> to comment out the Texbox codes, <asp:TextBox ID="UnitIDTextBox" runat="server" Text='<%# Bind("UnitID") %>' />

No warning error popped up, but the <!--   --> code was the real trouble-maker!   I completely deleted the <Textbox> codes, and it worked.