C# showing only the values from sql table with null value

Question

I am connecting to an SQL server. I have a column which has date and time and another which has the user name. Once i connect to the database it shows everything except the null values in the column of date and time. I need to show only the column of data and time which holds null values and the user with the null value. How do i Display the null values but only the null values?

Below is my code:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sql_connection
{
class Program
{
    static void Main(string[]args)
{

        string conn=null;
        SqlConnection connection;
        conn=("Data Source=Database\\SQL2012;Initial Catalog=Table;User ID=user;Password=example");

         connection=new SqlConnection(conn);
         try{

             connection.Open();
             Console.Writeline("Connection Open!");
             SqlCommand cmd= new SqlCommand("SELECT tabledateandtime,tableuser FROM [dbo].[tb_Showingnull]");
             cmd.Connection = connection;
             SqlDataReader reader = cmd.ExecuteReader();
             while(reader.Read())
              {

                   Console.WriteLine("{1} , {0}", reader["tabledateandtime"].ToString(),  reader["tableuser"].ToString()));

               }

               connection.Close();
                }

                catch(Exception ex)
               {
                   Console.WriteLine(ex.Message);
                }

                }
            }
      }

If I understand corrctly all you need to do is change SqlCommand cmd= new SqlCommand("SELECT tabledateandtime,tableuser FROM [dbo].[tb_Showingnull]"); to SqlCommand cmd= new SqlCommand("SELECT tabledateandtime,tableuser FROM [dbo].[tb_Showingnull] WHERE tabledateandtime IS NULL OR tableuser IS NULL"); — Jurgen Camilleri
– Jurgen Camilleri, Commented May 6, 2015 at 9:56
@JurgenCamilleri but tableuser will never be null just it will show the user connected to the null value — bobby
– bobby, Commented May 6, 2015 at 10:04

Donal · Accepted Answer · 2015-05-06 10:15:41Z

1

Based on OP's last comment, I have updated the code below to display NULL if the date is NULL otherwise show the date.

while(reader.Read())
{
    Console.WriteLine("{1}, {0}", 
       reader["tableuser"].ToString(),  
       reader["tabledateandtime"] == DBNull.Value ? "NULL" : reader["tabledateandtime"].ToString());
}

If you just want to return only records that have a null date, then just update your SQL to this:

SqlCommand cmd = new SqlCommand("SELECT tabledateandtime, tableuser" +
    " FROM [dbo].[tb_Showingnull] WHERE tabledateandtime IS NULL OR tableuser IS NULL");

edited May 6, 2015 at 10:15

answered May 6, 2015 at 9:57

Donal

33.2k10 gold badges69 silver badges75 bronze badges

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

5 Comments

danish Over a year ago

Why not just filter out null values from database itself?

Donal Over a year ago

@danish ok, I have added that option.

bobby Over a year ago

@Donal i dont think you understood exactly what i have to do. i have a list with date and time but some are null. I need to display in the command run on one side the user and on the other side the value saying null.

bobby Over a year ago

I need to do the second one but when I am doing it as you are telling me it tells me "incorrect syntax near '.' "

Donal Over a year ago

@bobby the syntax is correct, I have checked - can you try copy and paste.

Jurgen Camilleri · Accepted Answer · 2015-05-06 10:08:45Z

0

I think I finally understood what you want - check this out:

while(reader.Read())
{
    Console.WriteLine("{1} , {0}", reader["tabledateandtime"] == DBNull.Value ? "NULL" : reader["tabledateandtime"].ToString(),  reader["tableuser"].ToString()));
}

answered May 6, 2015 at 10:08

Jurgen Camilleri

3,58922 silver badges46 bronze badges

4 Comments

bobby Over a year ago

okay this worked can you show me how i can show another table like tabledateandtime and i need to display its null values also. In the same program.

Jurgen Camilleri Over a year ago

I would need the table name and column names for the null columns

bobby Over a year ago

i have 3 tables 1. TableIn 2. TableOut 3.TableUser. Now i need to show the null values of tablein and table out and near them the user with this null value.

Jurgen Camilleri Over a year ago

This seems more complex than I thought. Please create another question and include more details such as the tables' structure and which columns you need to check for null.

Collectives™ on Stack Overflow

C# showing only the values from sql table with null value

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related