C# - Set null to empty string on SQL SELECT?

Question

I would like to replace any null values for a field with an empty string when I select items from the database. Currently, what I have works fine in SQL Management Studio, but when I place it in my C# code's SELECT statement, the column I want to be empty if the value is null does not display. I do not see any error messages displaying.

I am still learning SQL, so I am not sure how to fix the query so that all 3 columns are displayed in the browser in the gridview regardless of whether the ThumbnailUrl field is null or not.

Any help is appreciated! Thanks!

Gridview code in the designer:

 <cb:SortedGridView ID="SearchResultsGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductId" GridLines="None" SkinID="PagedList" Width="100%" Visible="false" >

                                <Columns>
                                    <asp:TemplateField HeaderText="Thumbnail">
                                        <ItemStyle HorizontalAlign="Center" />
                                        <ItemTemplate>
                                            <asp:HyperLink ID="NodeImageLink" runat="server" NavigateUrl='<%# UrlGenerator.GetBrowseUrl((int)Eval("ProductId"), CatalogNodeType.Product, (string)Eval("Name")) %>'> <asp:Image ID="NodeImage" runat="server" ImageUrl='<%# Eval("ThumbnailUrl") %>' Visible='<%# !string.IsNullOrEmpty((string)Eval("ThumbnailUrl")) %>' AlternateText='<%# Eval("Name") %>' /> </asp:HyperLink>
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="SKU" SortExpression="Sku">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemTemplate>
                                            <asp:HyperLink ID="ProductSku" runat="server" Text='<%#Eval("Sku")%>' SkinID="FieldHeader" /><br />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Item" SortExpression="Name" > 
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemTemplate>
                                            <asp:HyperLink ID="ProductName" runat="server" Text='<%#Eval("Name")%>' SkinID="FieldHeader" /><br />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Assigned">
                                        <ItemStyle Width="50px" HorizontalAlign="Center" />
                                        <ItemTemplate>
                                            <asp:ImageButton ID="AttachButton" runat="server" CommandArgument='<%#Container.DataItemIndex%>' AlternateText="Add" ToolTip="Add" SkinID="AddIcon" OnClientClick="this.visible=false" OnClick="AttachButton_Click" Visible='<%#!IsProductLinked((int)Eval("ProductId"))%>' />
                                            <asp:ImageButton ID="RemoveButton" runat="server" CommandArgument='<%#Container.DataItemIndex%>' AlternateText="Remove" ToolTip="Remove" SkinID="DeleteIcon" OnClientClick="return confirm('Are you sure you want to remove this item from this option?')" OnClick="RemoveButton_Click" Visible='<%#IsProductLinked((int)Eval("ProductId"))%>' />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                </Columns>
                                <EmptyDataTemplate>
                                    There are no products that match the search text.
                                </EmptyDataTemplate>
                            </cb:SortedGridView>

This is the code:

 DataTable dt = new DataTable();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = cn;
    cmd.CommandType = CommandType.Text;
    if (SearchName.Text != "")
    {
        try
        {
            cmd.CommandText = "SELECT ProductID, ISNULL(ThumbnailUrl, '') AS ThumbnailUrl, Name, Sku FROM ac_Products WHERE Name LIKE '%' + @Name + '%'";

            cmd.Parameters.AddWithValue("@Name", SearchName.Text);

            cn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            SearchResultsGrid.DataSource = dt;
            SearchResultsGrid.DataBind();
            SearchResultsGrid.Columns[0].Visible = ShowImages.Checked;

        }
        catch (Exception x)
        {
            Logger.Error(ErrLoc + " PopulateSearchGrid(): ", x);
            errorLbl2.Text = "PopulateSearchGrid() Name Search Error: " + x.Message.ToString();
        }
        finally
        {
            cn.Close();
        }
    }

Answer 1

I'm not quite sure what you mean by "does not display". An empty value actually doesn't display anything. I doubt that this is an SQL problem, as your statement looks fine (apart from what I write at the bottom about parameterizing your query). I guess it is a binding problem. You can try one of the following:

1. Instead of ISNULL(ThumbnailUrl, '') AS ThumbnailUrl try ISNULL(ThumbnailUrl, '-') AS ThumbnailUrl and check whether you see the - now.
2. If this is a Windows Forms application you can try to add the columns to the grid in the designer and set the AutoGenerateColumns property to false. Please note that you can set this property only in code, not in the designer.
3. If this is WPF, check whether you actually have a column definition for the required field. Maybe you have a typo on the binding configuration? The binding names are case sensitive! If you return ThumbnailUrl from the database, the binding ThumbNailUrl will not work.

As a side note: Thumbs up for trying to use parameterized queries, but it don't think you're doing it right in this case. It should read like this:

cmd.CommandText = "SELECT ProductID, ISNULL(ThumbnailUrl, '') AS ThumbnailUrl, Name, Sku FROM ac_Products WHERE Name LIKE @Name";
cmd.Parameters.AddWithValue("@Name", String.Format("%{0}%", SearchName.Text));