CODEWITHSUNDEEP
sql-servervb.netdatetime
Gary Yee
Gary Yee

Reputation: 13

Check date value is null - specified cast in not valid

I have problem with my code which i spent a day but unable to figure out a workaround. The error code is specified cast in not valid

This is very high chances is due to my data type value for checking the date value whether is null. because when I removed the IF ELSE statement the code is working fine which I able to update the DB.

Scenario is that I have clock in and clock out time for visitor, so first the visitor clock in and then the information will insert to DB without the clock out time (apparently the clock out time will be NULL in DB), and later when visitor clock out I need to validate whether the clock out time is NULL for the same visitor.

Below is my codes:

Private Sub btn_clockout_Click(sender As Object, e As EventArgs) Handles btn_clockout.Click
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Dim clockouttimestatus As DateTime? = Nothing

    Try
        con.ConnectionString = "Data Source=LAPTOP-HP;Initial Catalog=COMPANY;Integrated Security=True"
        cmd.Connection = con
        con.Open()
        cmd.CommandText = "select v_clockouttime from visitor where v_id = @txt_visitorid"
        cmd.Parameters.Add("@txt_visitorid", SqlDbType.Int).Value = txt_visitorid.Text
        clockouttimestatus = cmd.ExecuteScalar()

        If IsDBNull(clockouttimestatus) Then

            cmd.CommandText = "update visitor set v_clockouttime = @lbl_displaydate where v_id = @txt_visitorid"

            cmd.Parameters.Add("@lbl_displaydate", SqlDbType.DateTime).Value = lbl_displaydate.Text
            cmd.Parameters.Add("@txt_visitorid", SqlDbType.Int).Value = txt_visitorid.Text
            cmd.ExecuteNonQuery()

            MessageBox.Show("Visitor ID is successful clockout")
        Else

            MessageBox.Show("Visitor ID is failed to clockout")

        End If

    Catch ex As Exception
        MessageBox.Show("Error while updating record on table..." & ex.Message, "Update Records")
    Finally
        con.Close()
    End Try

Upvotes: 1

Views: 273

Answers (2)

Fabio
Fabio

Reputation: 32443

I think specified cast in not valid error will be thrown when you trying assign result of ExecuteScalar() method to variable of Nullable(Of DateTime).

Dim clockouttimestatus As DateTime? = Nothing
clockouttimestatus = cmd.ExecuteScalar()

This exception will be throw only when returned value is DbNull

Add checking for DbNull before assigning a value

'connection and command staff
Dim databaseValue = cmd.ExecuteScalar()
clockouttimestatus = If(IsDbNull(databaseValue) = True, Nothing, databaseValue)

If clockouttimestatus.HasValue = False Then
    'Use your update block
End If

Upvotes: 0

Saragis
Saragis

Reputation: 1792

cmd.Parameters.Add("@lbl_displaydate", SqlDbType.DateTime).Value = lbl_displaydate.Text

You are assigning a text value to a datetime parameter. If .NET can not implicitly convert this to a datetime, the statement will fail. You should take care of the cast in your code, for example like this:

Dim parsedDate as DateTime 
Dim isValidDate as boolean = DateTime.TryParse(lbl_displaydate.Text, parsedDate)

If isValidDate Then
   cmd.Parameters.Add("@lbl_displaydate", SqlDbType.DateTime).Value = parsedDate
End If

Upvotes: 2

Related Questions