Is it possible to show empty label value if the value of a column used for date or time is null?

Question

Is it possible to show empty label value if the value of a column used for date or time is null?

Donald Symmons 3,066

I have column for Name, Date and another one for Time. If the values of date and Time columns are null or empty, how do I get to still show the label value in null data value?

Is this Possible? please how can I do that?. Here is what I mean in the table.

IdtypeEventNameDatedTimedLocation1EventArts Exhibition13-Mar-2303:00PM2 king Avenue2Visit permitVisitor8 plane str. From the table above, the type, Event has value in Dated and Timed columns, while Visit permit does not have any value in the Date and the timed columns.

My Code to display from database onto the label controls

string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand
                        {
                            CommandText = "SELECT * FROM TicketTable WHERE EventName = '" + Session["index"] + "'",
                            Connection = con
                        };
                        SqlDataAdapter sda = new SqlDataAdapter();
                        DataSet ds = new DataSet();
                        sda.SelectCommand = cmd;
                        sda.Fill(ds, "detail");
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            Title.Text = ds.Tables[0].Rows[0]["EventName"].ToString();
                            datelbl.Text = Convert.ToDateTime(ds.Tables[0].Rows[0]["Dated"]).ToLocalTime().ToString("MMM d, yyyy");
                            timelbl.Text = ds.Tables[0].Rows[0]["Timed"].ToString();
                            venuelbl.Text = ds.Tables[0].Rows[0]["Location"].ToString();
                        }
                        else
                        {
                            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record not found');", true);
                        }
                    }

Donald Symmons 3,066

Because the Dated and Timed columns are null, I tried to use this

 datelbl.Text = ds.Tables[0].Rows[0]["Dated"] != null ? Convert.ToDateTime(ds.Tables[0].Rows[0]["Dated"]).ToString("MMM d, yyyy") : "";
                            timelbl.Text = ds.Tables[0].Rows[0]["Timed"] != null ? ds.Tables[0].Rows[0]["Timed"].ToString() : "";

But I still get the error:

String was not recognized as a valid DateTime.

Accepted answer

3 additional answers

Your answer

Donald Symmons 3,066 Reputation points

2023-03-13T14:08:48.06+00:00

Because the Dated and Timed columns are null, I tried to use this

datelbl.Text = ds.Tables[0].Rows[0]["Dated"] != null ? Convert.ToDateTime(ds.Tables[0].Rows[0]["Dated"]).ToString("MMM d, yyyy") : ""; timelbl.Text = ds.Tables[0].Rows[0]["Timed"] != null ? ds.Tables[0].Rows[0]["Timed"].ToString() : "";

But I still get the error:

String was not recognized as a valid DateTime.

Answer 1

Lan Huang-MSFT 30,191 Microsoft External Staff

Hi @Donald Symmons,

You can use the following code:

datelbl.Text = ds.Tables[0].Rows[0]["Dated"] == DBNull.Value ? (string)null : Convert.ToDateTime(ds.Tables[0].Rows[0]["Dated"]).ToLocalTime().ToString("MMM d, yyyy");

User's image

Best regards,
Lan Huang

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Lan Huang-MSFT 30,191 Reputation points Microsoft External Staff

2023-03-14T07:25:18.1566667+00:00

Hi @Donald Symmons,

Then I set the Textboxes text mode to date and time respectively.

In .Net with textmode=date only supports the YYYY-MM-DD format.

You either remove Textmode=date, or change the format. :("yyyy-MM-dd")
Donald Symmons 3,066 Reputation points

2023-03-14T07:33:55.4866667+00:00

If I remove the textmode=date, how will I be able to select date or time in the textbox?

Donald Symmons 3,066

The thing is, I am trying to create a situation where user can create a ticket or permit as the case may be, for an event or visitor or residents of an area. An attendee, visitor or resident will require to provide any of tickets or permits before being allowed entry into an event or into the estate where he or she lives or visits.

Now in creating an event user will be required to insert name of event, event date and time, and location or venue. but in the case of resident or visitor's permit, date and time is not really required (especially for resident permit).

I then created a dropdown list for Event Type(Visitor, Resident, Event, Others), then textboxes for Event name, date, time and location or venue. When creating ticket or permit, user will select from the dropdown list the type of permit he or she is creating and will add the name, date, time and venue and submits. For the resident part, user did not insert date or time in the textbox because creating permit for an estate resident does not need to set any date or time for it, so those columns will be empty in the database table for ticket type "Resident" and for "Visitor".

Here is how my table schema looks

[Id]            INT            IDENTITY (1, 1) NOT NULL,
    [Name]          NVARCHAR (MAX) NULL,
    [CreatedDate]   DATETIME       NULL,
    [ticketname]      NVARCHAR (MAX) NULL,
    [TicketType]      NVARCHAR (MAX) NULL,
    [Dated]         DATE           NULL,
    [timed]         DATETIME       NULL,
    [venue]         NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)

This is how I insert those data into the table

 protected void Proceedbtn_Click(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(addresstxt.Text))
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("INSERT INTO TicketTable (Name, CreatedDate, ticketname, TicketType, Dated, timed, venue) VALUES(@Name, @CreatedDate, @ticketname, @TicketType, @Dated, @timed, @venue)"))
                        {
                            using (SqlDataAdapter sda = new SqlDataAdapter())
                            {
                                cmd.CommandType = CommandType.Text;
                                cmd.Parameters.AddWithValue("@Name", named.Text.Trim());
                                cmd.Parameters.AddWithValue("@CreatedDate", DateTime.UtcNow.ToString());
                                cmd.Parameters.AddWithValue("@ticketname", eventtxt.Text.ToUpper() + "" + alllabel.Text.ToUpper());
                                cmd.Parameters.AddWithValue("@TicketType", purpose.SelectedItem.Value);
                                cmd.Parameters.AddWithValue("@Dated", DatedEvent.Text.Trim());
                                cmd.Parameters.AddWithValue("@timed", TimeEvent.Text.Trim());
                                cmd.Parameters.AddWithValue("@venue", addresstxt.Text.Trim());
                                cmd.Connection = con;
                                con.Open();
                                cmd.ExecuteNonQuery();
                            }
                            con.Close();
                            Div1.Visible = true;
                            dvMessage.Visible = false;
                            lblMessage.Visible = false;
                            lblsuccess.Visible = true;
                            lblsuccess.ForeColor = System.Drawing.Color.Green;
                            lblsuccess.Text = "Permit Successfully Created";
                            Response.Redirect(Request.Url.AbsoluteUri);
                        }
                    }
                }
                else
                {
                    dvMessage.Visible = true;
                    lblMessage.Visible = true;
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = "Required field is empty";
                    lblsuccess.Visible = false;
                    Div1.Visible = false;
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

This is what I finally did. I changed the datatype of the two columns (Dated and timed) to datetime. And used the below code. It works, but when I don’t insert date or time in the two columns, it automatically inserts a default date and time in the two columns and when I checked the page, it displayed default date and time like (Jan 1, 1900) and (1:00 AM).

Now what I had to also do is hide these default date and time based on the value of the EventName column. Because for some EventNames, I did not insert any date or time data values.

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                Details();
                HideDateTimeLabel();
            }
        }
        public void Details()
        {
            try
            {
                if (Session["index"] != null)
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("SELECT * FROM TicketTable WHERE ticketname = @ticketname", con))
                        {
                            cmd.Parameters.AddWithValue("@ticketname", Session["index"]);
                            con.Open();
                            SqlDataReader dr = cmd.ExecuteReader();
                            if (dr.Read())
                            {
                                orgName.Text = dr["Name"].ToString();
                                title.Text = dr["ticketname"].ToString();
                                datelbl.Text = dr["Dated"] == DBNull.Value ? (string)null : Convert.ToDateTime(dr["Dated"]).ToLocalTime().ToString("MMM d, yyyy");
                                timelbl.Text = dr["Timed"] == DBNull.Value ? (string)null : Convert.ToDateTime(dr["timed"]).ToLocalTime().ToString("hh:mm tt");
                                venuelbl.Text = dr["venue"].ToString();
                            }
                            else
                            {
                                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record not found');", true);
                            }
                            con.Close();
                        }
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Timed Out');", true);
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }
        public void HideDateTimeLabel()
        {
            try
            {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("SELECT * FROM PassTable WHERE ticketname = @ticketname", con))
                        {
                            cmd.Parameters.AddWithValue("@ticketname", Session["index"]);
                            con.Open();
                            SqlDataReader dr = cmd.ExecuteReader();
                            if (dr.Read())
                            {
                                string eventname = dr["TicketType"].ToString().Trim().ToLower();
                                if (eventname == "VISITOR")
                                {
                                    datelbl.Visible = false;
                                    timelbl.Visible = false;
                                }
                                else if (eventname == "RESIDENT")
                                {
                                    datelbl.Visible = false;
                                    timelbl.Visible = false;
                                }
                                else if (eventname == "OTHERS")
                                {
                                    datelbl.Visible = false;
                                    timelbl.Visible = false;
                                }
                            }
                            con.Close();
                        }
                    }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

Lan Huang-MSFT 30,191 Reputation points Microsoft External Staff

2023-03-14T07:51:05.43+00:00

Hi @Donald Symmons,

Is your problem solved? Your code already differs from the one provided in your question. Can you tell me your new needs again, so that we can help you better, instead of working hard based on the problem at the beginning.

Donald Symmons 3,066

Okay. Thank you @Lan Huang-MSFT ,

I will give you the whole idea that I want to create. Here are the details of what I want. I hope it will be clearly understood, and I can get help on this.

The thing is, I am creating a situation where user can create a ticket or permit/gate pass for an event or for resident of an estate or for visitors. There are options to choose from to select the type of permit or gate pass to be created. This may be Event, Visitor, Estate Permit or Other, as the case may be. For an event, the user will create the gate pass by selecting the type; if user selects Event, then Event name, Date, time and Venue/Location will be required. If user selects Visitor or Estate Permit, then the Date, Time will be optional. So, I created a dropdown to select the type of ticket/permit, textboxes for Name, Date, Time and Venue/Location/Address. When user selects Visit or Estate permit from the dropdown, the Date and Time textboxes are hidden, and the required textboxes for Visit r Estate Permit details are seen only.

After the user has successfully created this ticket or permit, it will be stored in the database, so that when the user is registering anybody for the event, or for the visit or giving permit to an estate resident, the information regarding that particular permit is displayed for onward insertion when the data of the visitor, resident or event attendee are inserted i.e., when the user is inserting the data of the resident of an estate, or inserting the data of the visitor who plans to visit, or inserting that of an event attendee, the event type, event name, Date, time and venue will also be inserted from the labels they are displayed in.

Here is my table schema

[Id]            INT            IDENTITY (1, 1) NOT NULL,
    [Name]          NVARCHAR (MAX) NULL,
    [CreatedDate]   DATETIME       NULL,
    [ticketname]      NVARCHAR (MAX) NULL,
    [TicktType]      NVARCHAR (MAX) NULL,
    [Dated]         DATE           NULL,
    [timed]         DATETIME       NULL,
    [venue]         NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)

Here is how I insert the date into table:

protected void Proceedbtn_Click(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(addresstxt.Text))
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("INSERT INTO PassTable (Name, CreatedDate, ticketname, TicketType, Dated, timed, venue) VALUES(@Name, @CreatedDate, @ticketname, @TicketType, @Dated, @timed, @venue)"))
                        {
                            using (SqlDataAdapter sda = new SqlDataAdapter())
                            {
                                cmd.CommandType = CommandType.Text;
                                cmd.Parameters.AddWithValue("@Name", named.Text.Trim());
                                cmd.Parameters.AddWithValue("@CreatedDate", DateTime.UtcNow.ToString());
                                cmd.Parameters.AddWithValue("@ticketname", eventtxt.Text.ToUpper() + "" + alllabel.Text.ToUpper());
                                cmd.Parameters.AddWithValue("@TicketType", purpose.SelectedItem.Value);
                                cmd.Parameters.AddWithValue("@Dated", DatedEvent.Text.Trim());
                                cmd.Parameters.AddWithValue("@timed", TimeEvent.Text.Trim());
                                cmd.Parameters.AddWithValue("@venue", addresstxt.Text.Trim());
                                cmd.Connection = con;
                                con.Open();
                                cmd.ExecuteNonQuery();
                            }
                            con.Close();
                            Div1.Visible = true;
                            dvMessage.Visible = false;
                            lblMessage.Visible = false;
                            lblsuccess.Visible = true;
                            lblsuccess.ForeColor = System.Drawing.Color.Green;
                            lblsuccess.Text = "Permit Successfully Created";
                            Response.Redirect(Request.Url.AbsoluteUri);
                        }
                    }
                }
                else
                {
                    dvMessage.Visible = true;
                    lblMessage.Visible = true;
                    lblMessage.ForeColor = System.Drawing.Color.Red;
                    lblMessage.Text = "Required field is empty";
                    lblsuccess.Visible = false;
                    Div1.Visible = false;
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

For the Date and time, which is where I am having issue. Here is the HTML for the two

<div class="form-group">
                                    <div class="row" style="margin-top: 3%;">
                                        <div class="col-sm-7">
                                            <div class="form-group">
                                                <label style="margin: 0 auto; padding: 5px; color: #626364; font-weight: 400; font-size: 8pt;">Date</label>
                                                <div class="input-group">
                                                     <asp:TextBox ID="DatedEvent" runat="server" TextMode="Date" CssClass="form-control" Font-Size="9pt"></asp:TextBox>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-sm-5">
                                            <div class="form-group">
                                                <label runat="server" style="margin: 0 auto; padding: 5px; color: #626364; font-weight: 400; font-size: 8pt;">Time</label>
                                                <div class="input-group">
                                                     <asp:TextBox ID="TimeEvent" runat="server" TextMode="Time" CssClass="form-control" Font-Size="9pt"></asp:TextBox>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                </div>

Then to display the information of the ticket or gate permit from database on the webpage where the data of the visitor, resident or attendee will be inserted, I have this

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                Details();
                HideDateTimeLabel();
            }
        }
        public void Details()
        {
            try
            {
                if (Session["index"] != null)
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("SELECT * FROM TicketTable WHERE ticketname = @ticketname", con))
                        {
                            cmd.Parameters.AddWithValue("@ticketname", Session["index"]);
                            con.Open();
                            SqlDataReader dr = cmd.ExecuteReader();
                            if (dr.Read())
                            {
                                orgName.Text = dr["Name"].ToString();
                                title.Text = dr["ticketname"].ToString();
                                datelbl.Text = dr["Dated"] == DBNull.Value ? (string)null : Convert.ToDateTime(dr["Dated"]).ToLocalTime().ToString("MMM d, yyyy");
                                timelbl.Text = dr["timed"] == DBNull.Value ? (string)null : Convert.ToDateTime(dr["timed"]).ToLocalTime().ToString("hh:mm tt");
                                venuelbl.Text = dr["venue"].ToString();
                            }
                            else
                            {
                                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record not found');", true);
                            }
                            con.Close();
                        }
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Timed Out');", true);
                }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }
        public void HideDateTimeLabel()
        {
            try
            {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("SELECT * FROM TicketTable WHERE ticketname = @ticketname", con))
                        {
                            cmd.Parameters.AddWithValue("@ticketname", Session["index"]);
                            con.Open();
                            SqlDataReader dr = cmd.ExecuteReader();
                            if (dr.Read())
                            {
                                string eventname = dr["TicketType"].ToString().Trim().ToLower();
                                if (eventname == "VISITOR")
                                {
                                    datelbl.Visible = false;
                                    timelbl.Visible = false;
                                }
                                else if (eventname == "RESIDENT")
                                {
                                    datelbl.Visible = false;
                                    timelbl.Visible = false;
                                }
                                else if (eventname == "OTHERS")
                                {
                                    datelbl.Visible = false;
                                    timelbl.Visible = false;
                                }
                            }
                            con.Close();
                        }
                    }
            }
            catch (SqlException ex)
            {
                string msg = "Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }
        }

In the nearest above code, I tried to hide the default date and time which the database automatically inserted, based on the value of the Ticket/Permit Type column. Because for some Ticket/Permit, the date or time data values will be empty.

Lan Huang-MSFT 30,191 Reputation points Microsoft External Staff

2023-03-14T08:23:45.54+00:00

Hi @Donald Symmons,

I think it should be the comment before my comment above. You described the logic of your development, so what problems do you encounter in the development process now?
Donald Symmons 3,066 Reputation points

2023-03-14T08:48:32.73+00:00

When I tried to navigate to the page where the data of the person who I will want to give permit to, I got that error that stated:

String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: String was not recognized as a valid DateTime.

But that was when I had NvarChar(MAX) as the data type for both the Dated and Timed columns. When I changed the Dated and Timed columns to datetime, it worked but I had the labels for the date and time display the default date and time which is in the database table. So I later tried to hide the date and time labels, but it did not work
Michael Taylor 60,326 Reputation points

2023-03-14T14:03:20.53+00:00

The code I posted handles null/empty values from the DB. If you tried that code and are still having an issue then please post the updated code, Also provide an example of the data you're having an issue with.
Donald Symmons 3,066 Reputation points

2023-03-14T18:10:54.8233333+00:00

@Michael Taylor I already posted an updated code, when you check in one of the comments above you'll see
Michael Taylor 60,326 Reputation points

2023-03-14T19:51:46.8033333+00:00

Your code that you posted isn't using the simple code I posted that resolved the issue you described. Since you aren't going to use the code I posted then I'm going to drop from this conversation and let you continue with the other poster who is trying to help you. But the code I posted works correctly for the scenario you described so if you want to try that in your version at some point go ahead.
Donald Symmons 3,066 Reputation points

2023-03-15T05:55:10.1466667+00:00

Okay @MichaelTaylor-3340 ,

Thank you for your contribution. I really appreciate. I will see if others can give me an answer using the updated code I posted.

Thank you.
Lan Huang-MSFT 30,191 Reputation points Microsoft External Staff

2023-03-15T06:29:32.56+00:00

Hi @Donald Symmons,

So I later tried to hide the date and time labels, but it did not work

Your code is judged when it is uppercase, but what you get is lowercase TicketType.

You need to change the code below.

string eventname = dr["TicketType"].ToString().Trim().ToUpper();
Donald Symmons 3,066 Reputation points

2023-03-15T08:59:23.5533333+00:00

Thank you @Lan Huang-MSFT ,

Your answers have really been helpful. I'm very very grateful for the answers you provided to me all along.

And to everybody who contributed their answers as well; I thank you deeply. Those answers have given me ideas on the different ways to do this, and has also broaden mind. Again, I thank you all.

Answer 2

Database NULLs are represented as DbNull in ADO.NET. If you expect columns to be nullable you have to handle that case.

I'm not clear on what you want to display when they are null but I suspect you at least don't want it to crash.

if (!ds.Tables[0].Rows[0].IsNull("Dated"))
   datelbl.Text =  Convert.ToDateTime(ds.Tables[0].Rows[0]["Dated"]).ToLocalTime().ToString("MMM d, yyyy");
else
   datelb1.Text = "None";
if (!ds.Tables[0].Rows[0].IsNull("Timed"))
   timelbl.Text = ds.Tables[0].Rows[0]["Timed"].ToString();

I would also encourage you to use the strongly typed helper methods of DataRowExtension to simplify your code.

sda.Fill(ds, "detail");
var row = ds.Tables.OfType<DataTable>().FirstOrDefault().Rows.AsEnumerable();
if (row != null)
{
   Title.Text = row.Field<string>("EventName");
   datelbl.Text = !row.IsNull("Date") ? row.Field<DateTime>("Dated").ToLocalTime().ToString("MMM d, yyyy") : "None";
   timelbl.Text = !row.IsNull("Timed") ? row.Field<string>("Timed") : ""; 
   venuelbl.Text = row.Field<string>("Location");
}
else
{
   ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record not found');", true);
}

I'm guessing on your datatable's types though.

Answer 3

Sreeju Nair 12,666

You can check whether the column contains null value by

if(ds.Tables[0].Rows[0]["Dated"] != DBNull.Value)
{
    datelbl.Text = Convert.ToDateTime(ds.Tables[0].Rows[0]["Dated"]).ToLocalTime().ToString("MMM d, yyyy");
}
else
{
    datelbl.Text = "Date in empty";
}

However by taking a glance at your code, there are several improvement opportunity.

You are doing concatenation to build your query, this can lead to SQL Injection attacks. So shift to parameterized queries.
Try to use Entity Framework as it will be easier.
In the provided code, you are using SQLDataAdapter, but I could n't find any data binding code. SQLDataAdapter was used for doing bidirectional update between the dataset and your database. Read more https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter?view=dotnet-plat-ext-7.0 However, based on the code you are only reading data from the database, then it is better to use SQLDataReader
https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader?view=dotnet-plat-ext-7.0
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

In SQLDataReader, there is a method to check whether the value is null. https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.isdbnull?view=netcore-1.1

Hope this helps

Donald Symmons 3,066

It looks like the issue will be from the date format which I set.

datelbl.Text = Convert.ToDateTime(dr["Dated"]).ToLocalTime().ToString("MMM d, yyyy");

I set the format in the String: (“MMM d, yyyy”), and it seems it does not recognize Empty or Null values, because when I remove this (“MMM d, yyyy”), the code works. But I just wanted to set the format for the date and not have it display exactly as it is in the database table (2023-04-07). But empty value seems not to be recognized in the DateTime format (“MMM d, yyyy”)

The below code works, but the date format and time format are not what I need to achieve

if (Session["index"] != null)
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(connectionString))
                    {
                        using (SqlCommand cmd = new SqlCommand("SELECT * FROM TicketTable  WHERE EventName = @EventName", con))
                        {
                            cmd.Parameters.AddWithValue("@EventName", Session["index"]);
                            con.Open();
                            SqlDataReader dr = cmd.ExecuteReader();
                            if (dr.Read())
                            {
                                orgName.Text = dr["Name"].ToString();
                                title.Text = dr["EventName"].ToString();
                                if (dr["Dated"] != DBNull.Value)
                                {
                                    datelbl.Text = dr["Dated"].ToString();
                                }
                                else
                                {
                                    datelbl.Text = "";
                                }
                                    
                                if (dr["timed"] != DBNull.Value)
                                {
                                    timelbl.Text = dr["timed"].ToString();
                                }
                                venuelbl.Text = dr["venue"].ToString();
                            }
                            else
                            {
                                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Record not found');", true);
                            }
                            con.Close();
                        }
                    }
                }
                else
                {
                    ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Timed Out');", true);
                }

Michael Taylor 60,326 Reputation points

2023-03-14T02:31:19.6566667+00:00
use the IsNull function to check for DBNull. Please do not compare against DbNull as you're doing. That is considered the wrong approach. If the value isn't NULL in the DB then you should be good to use the ToString that accepts a format string. However if your DB is not well defined and allows an empty string in a datetime column then you would need to check for an empty string as well. But this is assuming that your database isn't actually storing the value as a date which would seem wrong to me.

var labelValue = ""; if (!dr.IsNull("Dated")) { //It is a string?? var value = dr.Field<string>("Dated"); if (DateTime.TryParse(value, out var dt)) lblValue = dt.ToString("MMM d, yyyy"); }; datelbl.Text = lblValue;

If the column is actually a datetime then you just need to check for DbNull which is what I posted originally. Once you have a datetime object then ToSTring will format properly.
Donald Symmons 3,066 Reputation points

2023-03-14T06:24:58.55+00:00

The Dated column data type is actually set as nvarchar(MAX); same with the timed column. Then I set the Textboxes text mode to date and time respectively.

Answer 4

Albert Kallal 5,586

Lots of answers here.

Might consider a "helper" converter.

So,

                datelbl.Text =  fDate(dt.Rows[0]["VisitDate"]);

and


        string fDate(object dt)
        {
            if (DBNull.Value.Equals(dt))
            {
                return "";
            }

         return Convert.ToDateTime(dt).ToLocalTime().ToString("MMM d, yyyy");
        }

Share via

Is it possible to show empty label value if the value of a column used for date or time is null?

3 additional answers

Your answer