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();
        }
    }

Thorsten Dittmar · Accepted Answer · 2014-02-17 15:04:50Z

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:

Instead of ISNULL(ThumbnailUrl, '') AS ThumbnailUrl try ISNULL(ThumbnailUrl, '-') AS ThumbnailUrl and check whether you see the - now.
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.
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));

answered Feb 17, 2014 at 15:04

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dejsa Cocan Over a year ago

In the designer, the AutoGenerateColumns property is set to false. I updated my post to include the code for the gridview...the column for ThumbnailUrl is there...Not sure what you mean by "try to add the columns to the grid in the designer." Sorry! By "does not display," I meant that the column does not appear in the gridview at all, even if the ThumbnailUrl field isn't null...sometimes it may be null, other times, it has a value. I still want the column to show in the gridview in the browser regardless, though.

Thorsten Dittmar Over a year ago

Well, it says in your code: Visible='<%# !string.IsNullOrEmpty((string)Eval("ThumbnailUrl")) %>'. Maybe that's the problem? You explicitly set the field to "invisible" if it has no value. I'm not an ASP.NET expert, but I guess this affects the entire template.

Dejsa Cocan Over a year ago

I just tried removing that code from the visible property and set it to true but that still did not work...I also tried the first of the three suggestion you made and that did not work either, unfortunately :(

Thorsten Dittmar Over a year ago

What was the solution?

Dejsa Cocan Over a year ago

A checkbox that was meant to show/hide the thumbnails column was comment out for some reason, so I uncommented it. Don't know why it was commented out--wasn't something I did. I didn't notice it for a while because of this.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related