Bound DropDownList with AppendDataBoundItems not returning Null

Question

I have this:

<asp:DropDownList ID="HomeCountry" runat="server" 
    DataSourceID="CountryListEntityDS" 
    DataTextField="CountryNameEn" 
    DataValueField="CountryCode" 
    AppendDataBoundItems="True"                          
    SelectedValue="<%# Bind('HomeCountry') %>">                                              
    <asp:ListItem Text="--Select One--" Value="" />
</asp:DropDownList>

and the datasource is like this:

<asp:EntityDataSource ID="CountryListEntityDS" runat="server" 
        ConnectionString="name=MainDataModelEntityCont" 
        DefaultContainerName="MainDataModelEntityCont" 
        EnableFlattening="False" 
        EntitySetName="Countries">
</asp:EntityDataSource>

I get the drop down with the countries and the --- Select One --- as the first item correctly. Everything work correctly EXCEPT when I change a record that had a country to the --- select one --- (so not having a country) and press save.

Then it throws:

[SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Account_CountryList".

I have reproduced this error manually in SQL Server Management Studio, it occurs if I try to change a record's Country field from a valid value to a "". The same error is thrown. But when I put Null it works fine. The DB constraint is basically saying you have to have either a valid value or Null.

So it seems to me, the C# code somehow is not resulting in a Null being sent to DB, rather a blank string.

Anyone help greatly appreciated. Many thanks in advance.

there is a problem with your sql query. look into the query and the table fields, specially the foreign key — polin, Apr 28 '13 at 08:40

score 0 · Answer 1 · answered Apr 28 '13 at 09:09

0

You can simply have a default value for country(e.g US). or you can have a value like 0 in DB for countries that is not seleceted by users so when the value of dropdown is "" you can send 0 to DB for updating table.

Best Regards.

answered Apr 28 '13 at 09:09

Soleiman Sharifi

83
1
8

user2328625 · Answer 2 · 2013-05-01T16:56:37.847

The data type on the SQL table makes the difference.

(TextBox has similar behaviour.)

It seems the rule is, when the server control is bound to a database field that can accept a blank string (ie: "") it returns that to db, on save. But when it is bound to a type that cannot, it returns Null.

Hence for my problem above, it was solved when I changed from char(2) to int for the country code. The Null did not violate the foreign key constraint, while "" did.

Bound DropDownList with AppendDataBoundItems not returning Null

2 Answers2