Handling null values on reader

Question

0.00/5 (No votes)

See more:

Hi,

I am trying to read a csv file on to my datagrid view but it always throws me an error "Object cannot be cast from DBnull to other types". I have done breakpoint on my code this happen when the reader tries to load the datetime format to the datagridview. Already work diffrent procedures that may handled null values but seems the error does not go away. I'll appreciate if you could suggest any other way i could handle the null values

With dt1
           .Columns.Add("Employee Number", Type.GetType("System.String"))
           .Columns.Add("Time In", Type.GetType("System.DateTime"))
           .Columns.Add("Time Out", Type.GetType("System.DateTime"))
           .Columns.Add("Shift Starttime", Type.GetType("System.DateTime"))
           .Columns.Add("ShiftEndTime", Type.GetType("System.DateTime"))


       End With

Dim csvreader As New StreamReader(txtboxcsv.Text, System.Text.Encoding.Default)
        Dim sline As String = ""

        Do
            sline = csvreader.ReadLine
            If sline Is Nothing Then Exit Do
            Dim columns() As String = sline.Split(",")
            Dim row As DataRow = dt1.NewRow
            Try

                row("Employee Number") = columns(0)
                If columns(2) = "IN" Then
                    row("Time In") = columns(1)
                Else
                    row("Time out") = columns(1)
                End If
                Dim d As String
                d = Convert.ToDateTime(columns(1)).ToString("dd-MMM-yyyy")
                Dim shift_start As String
                shift_start = Convert.ToDateTime(row("Time In")).AddHours(1).ToString("MM-dd-yyyy hh tt")
                Dim shift_end As DateTime
                shift_end = Convert.ToDateTime(row("Time In")).AddHours(9).ToString("MM-dd-yyyy hh tt")
                row("Shift Starttime") = shift_start
                row("ShiftEndTime") = shift_end
                
                dt1.Rows.Add(row)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        Loop
        csvreader.Close()

        DataGridView1.DataSource = dt1
        Me.Text = dt1.Rows.Count & "rows"
    End Sub

What I have tried:

'Dim n0_value As String = "00/00/0000"
                If IsDBNull(row("Time In")) = False Then
                   row("Shift Starttime") = shift_start OrElse
                    row("Shift Starttime") = n0_Value
                  Convert.IsDBNull(row("Shift Starttime"))
                ElseIf If IsDBNull(row("Time out")) = False Then
                    row("Shift EndTime") = shift_end OrElse
                     row("Shift EndTime") = n0_Value
                    Convert.IsDBNull(row("Shift EndTime"))
                   
                End If

Posted 19-Aug-19 16:43pm

sophia kylie taylor

Updated 20-Aug-19 6:32am

Add a Solution

Comments

[no name] 19-Aug-19 23:26pm

I guess you could try a NULL-ABLE date time. At some point, you have to deal with them though (nulls that is).

sophia kylie taylor 19-Aug-19 23:50pm

Is there anyway I could make the date time null-able?

3 solutions

Solution 1

First off, get rid of Convert - if you are processing data that may or may nor contain what you want, use TryParse instead:

VB

Dim d As String = ""
Dim dt As DateTime

If DateTime.TryParse(columns(1), dt) Then
    d = dt.ToString("dd-MMM-yyyy")
End If

That way, d will either be an empty string, or the date in the format you wanted.
Do the same for your other fields, and you don't need nullable DateTimes.

Posted 19-Aug-19 19:14pm

OriginalGriff

Solution 2

I'd suggest to use: DBNull.Value Field (System) | Microsoft Docs[^]

VB.NET

If DBNull.Value.Equals(row("Time In")) Then 'if null then...
    'your logic here
End If

Posted 19-Aug-19 20:29pm

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2019-08-20T06:32:00

I use a full class full of extension methods for dealing with NULL values, which greatly simplifies retrieving potentially null values from database calls.

You would need to trans-code this small sample into VB.Net, but you should be able to do that rather easily.This line will always give me a valid DateTime.

C#

DateTime GpsTime = row["GpsTime"].FixDate();

This is how that extension method is defined. Again this is C# and would need to be transcoded into VB.

C#

namespace System {
	public static partial class PublicExtensions {
		public static DateTime FixDate(this object NullableDate, string DefaultDateString = "01/01/0001") {
			DateTime ReturnValue;
			if (NullableDate != null) {
				try { ReturnValue = DateTime.Parse(NullableDate.FixString(DefaultDateString)); }
				catch (Exception) { if (!DateTime.TryParse(DefaultDateString, out ReturnValue)) { ReturnValue = new DateTime(0); } }
			}
			else { if (!DateTime.TryParse(DefaultDateString, out ReturnValue)) { ReturnValue = new DateTime(0); } }
			return ReturnValue;
		}
	}
}

Reference:
Extension Methods (Visual Basic) | Microsoft Docs[^]