Conversion from string "" to type 'Date' is not valid

[airhalynn101]

I have this line of code which gets the activation date of an agent and computes the difference between date.now to get the date difference:

Dim dt As New DataTable
        dt = ExecQuery("select agtid,Activation from tblagentinfo")
        If dt.Rows.Count > 0 Then
            Dim activation As String

            For i As Integer = 0 To dt.Rows.Count - 1
                Dim a As Date
                Dim b As Date

                a = dt.Rows(i)(1).ToString
                b = Date.Now
                activation = DateDiff(DateInterval.Day, a, b)

                If activation >= 365 Then
                    ExecQuery("Update tblagentinfo set AgentStat = 'Inactive' where agtid = '" & dt.Rows(i)(0).ToString & "'")

                End If
            Next
            dt = ExecQuery("select AgentStat from tblagentinfo where AgentStat = 'Inactive'")
            MsgBox(dt.Rows.Count.ToString + " Inactive Agents to Follow up")
        End If

When I try to run it, it always gives me this error message:

Conversion from string "" to type 'Date' is not valid.

and highlights this part:

a = dt.Rows(i)(1).ToString

I have already tried using DateParseExact and CDate but it still returns that error message. What's wrong with the code? Why the error message?

[PerplexedB]

You define a as a date in line 7, you should not be surprised that it cannot accept a .tostring expression no?

[airhalynn101]

I know, that's why I tried to use DateParseExact and CDate to convert string to date, but it still gives me the error message (I don't know if I'm doing it right).

To be honest, I was not the one who wrote that code. It was previously developed by another programmer, and when he left, this project was passed on to me, so I cannot ask him directly about the issue. But I'm really trying my best here. :)

[PerplexedB]

Try leaving out the .tostring, and then

b=date.now
a= trycast(dt.Rows(i)(1),date)
if a is nothing then
    activation = b
else
    activation = DateDiff(DateInterval.Day, a, b)
endif

should be close to what you need.

Let us know if this helps and if we can be of any further support;

[airhalynn101]

I tried the code you gave me but it returns two error messages:
'TryCast' operand must be reference type, but 'Date' is a value type. and
'Is' operator does not accept operands of type 'Date'. Operands must be reference or nullable types. :(

[PerplexedB]

Right, right, right. Sooooo sorry.

In the following code if the value you get from the table is a valid date, activation will compute the difference in days between that date and today, otherwise today will be assumed and activation will be set to 0

dim a as date
if datetime.tryparse(dt.Rows(i)(1),a) then
    activation = DateDiff(DateInterval.Day, a, date.now)     
else
    activation= 0
endif

Let us know if this helps and if we can be of any further support;

[airhalynn101]

It still returns that error message. I suspect now that this is not about the conversion, but the data being converted.

I want to ask about some things:

In

dt = ExecQuery("select agtid,Activation from tblagentinfo")

If dt.Rows.Count > 0 Then
            Dim activation As String

            For i As Integer = 0 To dt.Rows.Count - 1
                Dim a As Date
                Dim b As Date

                a = dt.Rows(i)(1).ToString()
                b = Date.Now

                ...

does dt.Rows(i)(1) refer to the data returned by the query? If so, then does i refer to the row and 1 refer to the column? Because I want to check if that cell is empty or what, because the error message suggests so (Conversion from string ""..., meaning the string being converted is empty, am I right?). Thanks for always helping me.

[PerplexedB]

Come to think of it that tryparse is not the solution here. I should have known. Well I'm sorry.

dt(i)(1) refers to the second column in the table (remember all this is 0 based), so in your example it wil return the Activation column.

I guess that's a date type, but it might be null. If you error is ""Conversion from type 'DBNull' to type 'String' is not valid." then try this

if IsDbNull(dt.Rows(i)(1)) = false then
    ' will crash if dt.Rows(i)(1) is not a date.
    ' it might be a date saved as a string.   I suggest we deal with
    ' that he next time around
    dim a as date = dt.Rows(i)(1)
    activation = DateDiff(DateInterval.Day, a, date.now)     
else
    activation= 0
endif

There remains one little problem. Your "friend" dim'ed activation as a String. That looks a bit wild, considering Datediff returns a long.

You're welcome and let us know if there is anything else that we can help you with.

Good luck.

[airhalynn101]

It works great (though I still don't know why that dt.Rows(i)(1) returns a null value, of course it should already be up to me to figure that out, with the help of your suggestions)! It's like a Try-Catch thing, but this one is better (I tried using Try-Catch but the notification message that should appear at form load does not show up, but when I tried your solution, it shows up again now).

Thank you very, very much. :)

[airhalynn101]

You're right. I checked the 'Activation' column from the database (why didn't I check it before?) and it is indeed a date saved as string (data type is varchar(50) ). How does this affect the codes by the way?

[PerplexedB]

If the Activation date is stored as a string then unless I'm very wrong dim a as date = dt.Rows(i)(1) should crash.`

Dates saved as Strings pose there own challenges, especially if you have a localisation problem, i.e. you are not an American coding in America on pc's with Regional set up English US (mm/dd/yy).

activation = 0  
if IsDbNull(dt.Rows(i)(1)) = false then
    dim a as date
    if datetime.tryparse(dt.Rows(i)(1),a) then
        activation = DateDiff(DateInterval.Day, a, date.now)    
    endif
endif

I'm still a bit worried about the fact that activation (the variable) is defined as a string, and not as a long.

Voilà. See if that helps and let us know if there is anything else we can help you with.

Good luck.

[airhalynn101]

It works fine now. Thank you very much!