Handling null values with bound RadioButtonList

Sometimes when you're binding a RadioButtonList and there's a null value in one of the records, you can end up getting an exception thrown. The problem here is that it tries to select a ListItem that has the same value as the record retrieved from the database, in this case, null.

There are plenty of workarounds for this issue, including changing your query to don't retrieve nulls, or using a COALESCE instead, for example. In this post, I'll show a simple trick to handle this.


Binding to nothing

As I said, the big secret is to have a ListItem with the same value retrieved from the query. If your retrieved value is NULL, your ObjectDataSource will convert it to an empty string automatically, so you'd only need to have a ListItem with an empty string marked to be its value, like the following:

<asp:RadioButtonList runat="server" ID="radioButtonList1" SelectedValue='<%# Bind("NullableColumn") %>'>
    <asp:ListItem Text="MyEmptyStringOption" Value=""/>
    <asp:ListItem Text="MyOption1" Value="1" />
    <asp:ListItem Text="MyOption2" Value="2"/>
    <asp:ListItem Text="MyOption3" Value="3"/>                    
</asp:RadioButtonList>

By doing that, when bound, the selected option will appear as the EmptyStringOption, so we have already prevented that Exception from happening. But what would we write to the empty string option text? "None"? It makes no sense having an option that says None on the screen, right?

So let's hide it. That way, we'll always have at least one option selected, even if the return value is null, but the user will only see the options with an actual value, making it look like the RadioButtonList has no selected option at all.

If you check ListItem's attributes, you'll see that it does not have a Visible attribute, so we're going to hide it through CSS. Even if it had a Visible attribute, if we'd set it to "False", the ListItem wouldn't even be rendered, causing the exception to return to happen, as we wouldn't have an option with an empty string value.


To hide it, simply add the style attribute to it, like this:

<asp:ListItem Text="MyEmptyStringOption" Value="" style="display: none;" />

But now you'll see something else. Even if not instantly, the compiler might show you a warning message, telling you that element ListItem does not have a 'style' attribute even though it works perfectly fine. If that warning message annoys you enough to make you want to get rid of it like it does to me, remove the style attribute from the ListItem, and add it programmatically on the Page's Load event, like this:

protected void Page_Load(object sender, EventArgs e)
{
    // First find the ListItem in question, and then add the style attribute to it
    MyRadioButtonList.Items.FindByValue("").Attributes.Add("style", "display: none");
}

This will have the same effect, and it will not show you that warning...

GridView with ObjectDataSource and Pagination

The title is self explanatory: I'm going to develop a GridView bound to an ObjectDataSource with pagination directly at the DataSource, not just on the Grid.


The difference of paginating directly on the data source and paginating at the Grid level, is that in the first case you'll only have the records that are shown in the grid loaded to memory. This means that for each page selected, the data source will get the next N results to be displayed. When you do that directly on the grid, every row will be loaded on the first fetch the data source will perform, and the grid will handle paginating and mantaining those records in memory for that.


First of all, let's create our table:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY (1,1) PRIMARY KEY,
    FirstName VARCHAR(60) NOT NULL,
    LastName VARCHAR(60) NOT NULL,
    Age INT NOT NULL,
    Phone VARCHAR(8) NULL
)

Insert some data in it, about 15 Rows or so...


If you mark that the ObjectDataSource allows Paging (and you will), you have to implement a select method which takes two special integer parameters (startRowIndex and maximumRows) and a count method that returns an integer of how many records we have in our table. It's obvious enough that we need the count method to calculate how many pages we'll have, and the two special parameters to limit our result...

You could also change startRowIndex and maximumRows to whatever you'd like to with the properties StartRowIndexParameterName and MaximumRowsParameterName. However, if you change their names your select method must be changed too. You'll learn exactly why we need those parameters with the code below.


Now implement our Data Class, notice that I've created a constant called ConnStr just for demonstration purposes, you should get your Connection String from wherever you think is better.

[System.ComponentModel.DataObject(true)]
public class CustomerData
{

    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select, true)]
    public static DataTable GetCustomers(int maximumRows, int startRowIndex)
    {
        string strCommand = "SELECT * FROM ";
        strCommand += "(SELECT *, ROW_NUMBER() OVER (ORDER BY FirstName) as RowNum FROM Customers) as Sub ";
        strCommand += "WHERE RowNum BETWEEN @StartRow AND @MaximumRows";

        using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(strCommand, new SqlConnection(CustomerData.ConnStr)))
        {
            using (DataTable dtRet = new DataTable())
            {
                try
                {
                    startRowIndex++;

                    sqlAdapter.SelectCommand.Parameters.Add("@StartRow", SqlDbType.Int).Value = startRowIndex;
                    sqlAdapter.SelectCommand.Parameters.Add("@MaximumRows", SqlDbType.Int).Value = (maximumRows + startRowIndex);

                    sqlAdapter.Fill(dtRet);

                    return dtRet;
                }
                catch (Exception)
                {
                    throw;
                }                   
            }                
        }
    }


    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select, false)]
    public static int GetCustomersCount()
    {
        using (SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM [Customers]", new SqlConnection(CustomerData.ConnStr)))
        {
            try
            {
                sqlCommand.Connection.Open();
                return (int)sqlCommand.ExecuteScalar();                    
            }
            catch (Exception)
            {                    
                throw;
            }
        }
    }
}

Take a better look at our SELECT command:

SELECT
    *
FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY FirstName) AS RowNum 
    FROM 
        Customers
) AS Sub
WHERE
    RowNum BETWEEN @StartRow AND @MaximumRows

Since our DataSource passes us "0" on StartRowIndex for our first page, we have to add 1 to it, because our column RowNum starts at 1. We then limit the records to start on StartRowIndex + 1 and end on MaximumRows + our StartRowIndex (already increased by one).

We can see that when we add our parameters to the SqlCommand object:

startRowIndex++; // Increment our start row number by one

sqlAdapter.SelectCommand.Parameters.Add("@StartRow", SqlDbType.Int).Value = startRowIndex;

sqlAdapter.SelectCommand.Parameters.Add("@MaximumRows", SqlDbType.Int).Value = (maximumRows + startRowIndex); // MaximumRow plus StartRowIndex is the number of our last row for the actual page

Ok, so after that we start designing our page's objects.

Heres our ObjectDataSource, with no big secrets:

<asp:ObjectDataSource 
    ID="dsCustomers" 
    runat="server" 
    TypeName="CustomerData" 
    SelectMethod="GetCustomers" 
    SelectCountMethod="GetCustomersCount" 
    EnablePaging="true"
/>

And this is our GridView, marked to auto generate our columns, just for demonstration purposes:

<asp:GridView
    ID="gridCustomers" 
    AllowPaging="True" 
    runat="server" 
    PageSize="5" 
    DataSourceID="dsCustomers"
    AutoGenerateColumns="true"
/>  

If you've done everything right, you should be good to go with an ugly ass GridView, showing a maximum of 5 rows on each page, ordered by our Customers' first name, like the images below:

Go on and customize the columns, add Insert / Edit / Delete functionality, and the grid's skin / theme.