DataBinding issues with a NULL or archived value

When binding web form controls to a data source you can often come unstuck when NULL values get returned from the database and you get dreaded run time NullReferenceExceptions. Although the examples below use Telerik controls, the equivalent ASP.NET controls can also be addressed in a similar manner

Binding a NULLable boolean field to a checkbox

When binding a boolean value to a grid, the add form will fail if it is NULLable due to there being no '3rd' option on a checkbox to handle the NULL value. To get around this we must set a default value manually when calling the InitInsert command as follows

Protected Sub rgd_ItemCommand(sender As Object, e As Telerik.Web.UI.GridCommandEventArgs) Handles rgd.ItemCommand
    If (e.CommandName = RadGrid.InitInsertCommandName) Then
        e.Canceled = True
        Dim newValues As System.Collections.Specialized.ListDictionary = New System.Collections.Specialized.ListDictionary()
        newValues("BoolField") = False
        e.Item.OwnerTableView.InsertItem(newValues)
    End If
End Sub
 

---

 
Binding a NULLable lookup table to Dropdown 

If you have a lookup table bound to a DropDownList/RadComboBox in a FormView/FormTemplate, by default it cannot handle NULL values if you create a line item for that NULL value. However by specifying a particular unused numeric value (such as 0 in this context will rarely by used for a table ID)

<telerik:RadComboBox ID="rcbForeignKeyID" runat="server" DataSourceID="sqlForeignKey" DataTextField="Name"
 DataValueField="ID" AppendDataBoundItems="true" SelectedValue='<%#Bind("ForeignKeyID")%>'>
    <Items>
        <telerik:RadComboBoxItem Text="No value..." Value="0" />
    </Items>
</telerik:RadComboBox>

On the Form's LinqDataSource we can then handle the Inserting & Updating events to set that 0 value to nothing which equates to DB NULL

Protected Sub lds_Inserting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceInsertEventArgs) Handles ldsForm.Inserting
    Dim rec As TableType = e.NewObject
    If rec IsNot Nothing Then
        If rec.ForeignKeyID = 0 Then rec.ForeignKeyID = Nothing
    End If
End Sub

Protected Sub lds_Updating(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceUpdateEventArgs) Handles ldsForm.Updating
    Dim rec As TableType = e.NewObject
    If rec IsNot Nothing Then
        If rec.ForeignKeyID = 0 Then rec.ForeignKeyID = Nothing
    End If
End Sub

---

Binding a value no longer in the DataSource to a GridDropDownColumn

When using a dropdown in the edit form of a RadGrid you often encounter the scenario where the grid row value no longer exists in the dropdown data source due to the record being archived or some such. In order to work around this, we can manually add the existing record to the top of the dropdown so the SelectedValue binding won't break.

The aspx code should look something like this for the column specification
        <telerik:GridTemplateColumn UniqueName="ID" HeaderText="Header" AllowFiltering="false" DataType="System.Int32">
          <ItemTemplate>
            <asp:Label ID="lblHeader" runat="server" Text='<%#Eval("Name")%>' />
          </ItemTemplate>
          <EditItemTemplate>
            <telerik:RadComboBox ID="rcb" runat="server" Skin="Sunset" SelectedValue='<%#Bind("ID")%>'
                            OnDataBinding="rcb_DataBinding" AppendDataBoundItems="true"
              DataSourceID="sqlList" DataTextField="Name" DataValueField="ID" />
          </EditItemTemplate>
        </telerik:GridTemplateColumn>


The first bit of code required is an event handler for the RadComboBox / DropDownList control to add in the extra list item with the current value as follows
    'Add in option for existing record in dropdown
    Protected Sub rcb_DataBinding(sender As Object, e As EventArgs)
        Dim rcb As RadComboBox = DirectCast(sender, RadComboBox)
        Dim rci As New RadComboBoxItem("-- Existing Record --", rcb.SelectedValue)
        rcb.Items.Insert(0, rci)
    End Sub

The next bit of code is required to populate the text label of this new record with the actual value as follows
    'Set the label of the existing
    Protected Sub grd_ItemDataBound(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridItemEventArgs) Handles grd.ItemDataBound
        If (TypeOf e.Item Is GridEditFormItem And e.Item.IsInEditMode) Then
            Dim editItem As GridEditFormItem = CType(e.Item, GridEditFormItem)
            Dim strName As String = editItem.ParentItem.DataItem("Name")
            Dim rcb As RadComboBox = DirectCast(e.Item.FindControl("rcb"), RadComboBox)
            If rcb IsNot Nothing Then rcb.Items(0).Text = strName
        End If
    End Sub

Customising the LinqDataSource

Binding a data grid to a data source quickly is a sinch with a LinqDataSource and it can save a lot of time. However sometimes it doesn't quite cut it and you wished you could use all of its benefits but just change one little aspect. Well actually with a bit of tweaking you can do exactly that. Here are two cases that I've frequently used while building some quick prototypes.

Altering the fields that are sent to a LinqDataSource

The data that is sent or received for a LinqDataSource action may need to be altered for a number of reasons, such as:

1. Altering the data sent in an update if the information is incorrect (e.g. a blank ("") field updating to a numerical field)
2. Filling in extra information for an insert (e.g. a predetermined foreign key)

The code is implemented in the handler for the LinqDataSource action, this will either be Inserting, Updating, or Deleting as the event will need to happen prior to the action.  In the code, first create a variable of the table type, then select the specific field and change it as needed.

Altering an Insert Example:

In this case, a foreign key needs to be automatically filled in.  The foreign key is taken from the query string parameter of the URL.

Protected Sub LinqDataSource_Inserting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceInsertEventArgs) Handles LinqDataSource.Inserting

    Dim rec As WEB_Table = e.NewObject

    Dim field As String = Request.QueryString("Field")

    If rec IsNot Nothing Then

        rec.Field = field

    End If

End Sub


---

Using a Stored Procedure or more complicated query with a LinqDataSource

Sometimes you want to use all the handiness of the automated Insert, Update and Delete functions but use a select command that is too complicated to be handled by the basic features provided by the LinqDataSource control. In order to do this we just override the Select query in the Selecting event. Here is an example of how to inject a stored procedure as the select data source.

1. Create stored procedure for your SELECT that includes any other business logic you want in it
2. Add the stored procedure to the data context and if you want to turn off optimistic concurrency checks, set Update Check to Never for all properties in that table (can highlight them all to change all at once)
3. In code behind change Selecting event of LinqDataSource to set the e.Result = DC.StoredProcedure(Params).ToList()

Protected Sub lds_Selecting(sender As Object, e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles lds.Selecting
    Dim res = db.usp_StoredProcList(Param1, e.WhereParameters("ParentID")).ToList()
    e.Result = res
End Sub

4. Remove any Where clause from LinqDataSource (and any WhereParameters if you don't need them)
5. Use WhereParameter to get ParentID field into Selecting event for Detail Level tables - accessed via e.WhereParameters("ParentID")

Finding orphaned records in SQL

In general relationships should always be created between a table's Primary Key and the associated Foreign Keys in other tables. However sometimes those relationships might not already exist in the database and it must be added later on. This can sometimes fail due to the constraint being unenforceable on the existing data (e.g. because certain database records have already been deleted and the IDs persist in the Foreign Key's table).

To solve this problem you can write one of the following queries to discover the records at fault:

SELECT * FROM FK_table f LEFT OUTER JOIN PK_table p ON p.ID = f.ID
WHERE p.ID IS NULL

SELECT * FROM FK_table f
WHERE NOT EXISTS (SELECT p.ID FROM PK_table p WHERE f.ID = p.ID)

SELECT * FROM FK_table f
WHERE f.ID NOT IN (SELECT p.ID FROM PK_table p)

This final T-SQL method is actually very useful as the equivalent DELETE syntax also works. Therefore to then delete these faulty records you can run this query:

DELETE FROM FK_table
WHERE ID NOT IN (SELECT ID FROM PK_table)

It should also be noted that sometimes Constraints can get dropped automatically when making changes to a table. So any table changes should be followed by a check to make sure all Relationships are still in place.

Testing for Javascript undefined correctly

I believe there are a number of incorrect answers to this topic. Contrary to common belief, "undefined" is NOT a keyword in JavaScript and can in fact have a value assigned to it.

// DO NOT USE the following bad examples, they are degenerate!

var undefined = false;  // Shockingly, this is completely legal!

 

var myVal;

if (myVar === undefined) {

    alert("You have been misled. Run away!");

}

if (myVar == undefined) {

    alert("You have been misled. Run away!");

}

 

Additionally, myVar === undefined will raise an error in the situation where myVar is undeclared.

The most robust way to perform this test is:

if (typeof myVar === "undefined")

 

This will always return the correct result, and even handles the situation where myVar is not declared.

Delayed Facebook Plugin Loading

When doing some complex javascript, using the standard Facebook plugins can really slow down and interfere with your script. To control when it loads and runs you can load it in manually like so (fadeTo function is an example only of organising timing):

$('.myelement').fadeTo(800, 1, function () {
    PluginSetup();
});

function PluginSetup() {
    $.ajaxSetup({ cache: true });
    $('body').append('<div id="fb-root"></div>');
    $.getScript('//connect.facebook.net/en_US/sdk.js', function () {
        FB.init({
            version: 'v2.4'
        });
        FB.XFBML.parse();
    });
}

FB.XFBML.parse() will then run through your HTML looking for plugins (you can specify a selector too) that it needs to activate.

Modern Browser Targeting

We all know that in general we should not put browser specific CSS in, just use CSS that works everywhere. Sometimes though it is unavoidable for little intricacies.

To target IE9 or less use conditional commenting to attach an external CSS file with appropriate overrides:

<!--[if lte IE 9]>
  <link href="/bundles/ie9.css" type="text/css" rel="stylesheet" />
<![endif]-->

To target IE10 and IE11:

@media all and (-ms-high-contrast: none), (-ms-high-contrast: active) { /* IE10+ CSS here */ }

To target Edge Browser:

@supports (-ms-accelerator:true) { /* MS Edge CSS here */ }

To target Firefox:

@-moz-document url-prefix() { /* Firefox CSS here */ }

Handling a LINQ ChangeConflict Exception

Multi-user applications using LINQ-to-SQL will commonly come across a ChangeConflictException at run time when a user tried to update a record which if not handled properly can cause a poor user experience as they can lose their changes. This is because it implements the optimistic concurrency approach by default.

There are a couple of ways of handling the ChangeConflictException which occurs when two processes are trying to update the same record in the database at about the same time (i.e. the record has changed in the database since you initially requested it). One way is to just add UpdateCheck = Never on each field in the DataContext which means last change wins - this is a bit crude though and you may actually want to do something more elegant.

Here is one way to handle it in code:

try {
    _db.SubmitChanges(ConflictMode.ContinueOnConflict);
} catch (ChangeConflictException e) {
    _db.ChangeConflicts.ResolveAll(RefreshMode.KeepCurrentValues);
    _db.SubmitChanges(ConflictMode.ContinueOnConflict);
}

Options for ConflictMode are ContinueOnConflict or FailOnFirstConflict
Options for RefreshMode are KeepChanges, KeepCurrentValues, OverwriteCurrentValues
You can then handle it how you want appropriate to the situation

More info: http://weblogs.asp.net/dixin/understanding-linq-to-sql-9-concurrent-conflict