Show NULL and Empty String in GridView

Question

User951722283 posted

Hello, I'm looking for a way to display differently NULL ans Empty data from a Custom SQL Query 

I DON'T WANT TO USE ISNULL() IN THE SQL! 

I don't know if I use the right event it "almost" work except NULL and Empty String gave the same result

Here's my code

Protected Sub fillGrid(ByRef grid As GridView, ByVal query As String, Optional server As String = "")

     ' For testing...
     query = "SELECT NULL AS testNull, '' AS testEmpty"

     Using MyCon As SqlConnection = New SqlConnection(vSQL.selectBase(server))

          da = New SqlDataAdapter(query, MyCon)
          ds = New Data.DataSet
          da.Fill(ds)
          grid.DataSource = ds

          grid.DataBind()
     End Using
End Sub

    Private Sub gridSQL_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles gridSQL.RowDataBound
        If e.Row.RowType <> DataControlCellType.Header Then
            For c As Integer = 0 To e.Row.Cells.Count - 1
                Select Case LCase(e.Row.Cells(c).Text) ' CAN'T FIND WHAT ELSE TO TEST
                    Case "null" ' I KNOW 
                        e.Row.Cells(c).Text = "<i>NULL</i>"
                    Case "&nbsp;", "" ' THIS WORKS BUT FIRES ON NULL AND EMPTY (e.Row.Cells(c).text = "&nbsp;") 
                        e.Row.Cells(c).Text = "<i>EMPTY</i>"
                End Select
            Next
        End If
    End Sub

My result is obvious now :

testNull	testEmpty
EMPTY	EMPTY

Buy I'm looking for :

testNull	testEmpty
NULL	EMPTY

Please HELP! :)

Thanks!!!

Answer 1

User283571144 posted

Hi strauby,

I don't know if I use the right event it "almost" work except NULL and Empty String gave the same result

According to your description, I suggest you could loop each record in the datatable.

If the record value is dbnull.value, you set the value to "Null".

If the record value is String.IsNullOrWhiteSpace, you set the value to "Empty".

More details, you could refer to follow codes:

Sql:

protected void Page_Load(object sender, EventArgs e)
        {
            string connString = ConfigurationManager.ConnectionStrings["UserConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connString);
            string query = "select * from [dbo].[UserInfo2]";
   
            SqlDataAdapter ada = new SqlDataAdapter(query, conn);
            DataTable ta = new DataTable();
            ada.Fill(ta);

            foreach (DataRow row in ta.Rows)
            {
                for (int i = 0; i < ta.Columns.Count; i++)
                {
                    if (row[i] == DBNull.Value)
                    {
                        row[i] = "Null";
                    }
                    if (String.IsNullOrWhiteSpace(row[i].ToString()))
                    {
                        row[i] = "Empty";
                    }
                }
            }
            GridView1.DataSource = ta;
            GridView1.DataBind();
        }

Result:

Best Regards,

Brando

Answer 2

User951722283 posted

Thanks! Well I had to improvise in VB when row[i] is DBNull it of type Int32 so can't assign a string value... Not everything is the same...

Here's the code :

    Dim nullInt32 As Int32 = -2147483647 ' Had to create a value not(well rarely) to happen in real life ...


    Protected Sub fillGrid(ByRef grid As GridView, ByVal query As String, Optional server As String = "")
        Try

            Using MyCon As SqlConnection = New SqlConnection(vSQL.selectBase(server))

                da = New SqlDataAdapter(query, MyCon)
                dt = New Data.DataTable
                da.Fill(dt)

                ' I test for DBNull here and use RowDataBound for Display
                For Each row As Data.DataRow In dt.Rows
                    For i As Integer = 0 To dt.Columns.Count - 1
                        If row(i) Is DBNull.Value Then
                            row(i) = nullInt32
                            'row(i) = "Null" This don't work cause row(i) is of Type.Int32 :S
                        End If
                    Next
                Next

                grid.DataSource = dt
                grid.DataBind()
            End Using

        Catch ex As Exception
            vWeb.alert(ex.ToString)
        End Try

    End Sub

I used it whit :

    Private Sub gridSQL_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles gridSQL.RowDataBound
        If e.Row.RowType <> DataControlCellType.Header Then
            For c As Integer = 0 To e.Row.Cells.Count - 1
                Select Case LCase(e.Row.Cells(c).Text)
                    Case nullInt32.ToString
                        e.Row.Cells(c).Text = "<i class=""label label-warning"">NULL</i>"
                    Case "&nbsp;", ""
                        e.Row.Cells(c).Text = "<i class=""label label-info"">EMPTY</i>"
                End Select
            Next
        End If
    End Sub

I know it's not perfect but it works. If anyone had a better solution please share ! :)

EDIT: After testing this only work for String and Int32 field have to code a bit more for other Type (Date, Boolean, etc...) 

Answer 3

User283571144 posted

Hi strauby,

Thanks! Well I had to improvise in VB when row[i] is DBNull it of type Int32 so can't assign a string value... Not everything is the same...

I'm sorry, I forget your codes is VB.I will write a VB.NET demo.

Besides, could you please post some example about your database and value?

For example"

ID  Name Age

1    aaa    18

2   bbb    12

You say your column type is int?

Null value is DBNull.Value.

How you define empty value in the database? 0 ?

Best Regards,

Brando 

Answer 4

User951722283 posted

Don't worry I use both language.

Yes it seem to be the default of a NULL Field as in my test Query "SELECT NULL as testNull, '' AS testEmpty"

The testNull is of Type Int32 don't ask why!! Same thing in C# I tryed!

Anyway my test was for String and Int32 mainly so my modification work fine "kind of" just have to add a Try like this:

...
                            Try
                                row(i) = nullInt32
                            Catch ex As Exception
                                'for other type than String and Int32 just ignore for now
                            End Try
...