asked on

date exception - vb.net

Hello,
I am using the following statement on one of the page and I get an error sometimes if the user is outside the GB format
Dim Query As String = "UPDATE IQResults SET IsCompleted='True', EndTime=GetDate() Where resultid =(SELECT max(resultid) FROM IQResults where CustomerID='" & CustomerID & "')"

Dim MyCommand As SqlCommand = New SqlCommand(Query, MySqlConnection)
MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()
MyCommand.Connection.Close()
pnlTestComplete.Visible = True

Exception
>>InvalidCastException: Conversion from string "26/03/1991" to type 'Date' is not valid.

Can someone please advice how can I fix this?
Thanks

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

allanlorriman

ASKER

thanks Codecruiser, I have updated the code I still get the same error, this must be because I am fetching customers dob and passiing to the to the function to check date and perform certain actions. ..

>> System.InvalidCastException: Conversion from string "19/07/1964" to type 'Date' is not valid.

please can you advice whats wrong with this code?

  ds = GetCustomerContactDetails(CustomerID)
        If ds.Tables(0).Rows.Count > 0 Then
                  ContactDetails.DateOfBirth = ds.Tables(0).Rows(0).Item("CustDOB")
        End If
 
 GetResult = IsPass(CustomerID, TestID, GiftCodeID, ContactDetails.DateOfBirth)

  Public Shared Function IsPass(ByVal CustomerID As Integer, ByVal TestID As Integer, ByVal ID As String, ByVal DateOfBirth As Date) As Integer
        Dim dbconn As SqlConnection
        Dim dbcomm As SqlCommand
        Dim dbread As SqlDataReader
        Dim SQL As String
        Dim IQScore, VarF As Decimal
        Dim VarAge As Integer

        dbconn = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        dbconn.Open()

        SQL = "SELECT Count(*) As Total FROM Answers WHERE TestID = '" & TestID & "'" & _
              " AND CustomerID = '" & CustomerID & "' AND Correct = '1'"

        dbcomm = New SqlCommand(SQL, dbconn)
        dbread = dbcomm.ExecuteReader()

        If dbread.HasRows Then
            dbread.Read()
            'If dbread("Total") > 24 Then
            '    Return 1
            'End If

            IQScore = Int(dbread("Total").ToString()) 'get the total correct answers

            If IQScore > 35 Then
                IQScore = "140+"
            ElseIf IQScore > 29 Then
                IQScore = CLng(131 + ((IQScore - 30) * 1.6))
            ElseIf IQScore > 24 Then
                IQScore = CLng(120 + ((IQScore - 25) * 2.5))
            ElseIf IQScore > 19 Then
                IQScore = CLng(110 + ((IQScore - 20) * 2.25))
            ElseIf IQScore > 13 Then
                IQScore = CLng(90 + ((IQScore - 14) * 1.8))
            ElseIf IQScore > 11 Then
                IQScore = CLng(80 + ((IQScore - 12) * 9))
            ElseIf IQScore > 8 Then
                IQScore = CLng(70 + ((IQScore - 9) * 4.5))
            ElseIf IQScore > 3 Then
                IQScore = CLng(50 + ((IQScore - 4) * 4.75))
            Else
                IQScore = "50"
            End If

            VarAge = WebFunctions.Age(DateOfBirth) 'certain age groups.

            If VarAge = 13 Then
                VarF = 1.25

            ElseIf VarAge = 14 Then
                VarF = 1.2
            ElseIf VarAge = 15 Then
                VarF = 1.15
            ElseIf VarAge = 16 Then
                VarF = 1.1
            ElseIf VarAge = 17 Then
                VarF = 1.05
            Else
                VarAge = 18
                VarF = 1
            End If

            If IQScore = "140+" Then
                IQScore = "140+"
            Else
                IQScore = (IQScore * VarF)
            End If

            ' IQScore = (IQScore * VarF)   'until here

            Registration.StoreTest(ID, IQScore)
        End If
        dbread.Close()
        dbconn.Close()
        Return 0
    End Function

Open in new window

allanlorriman

ASKER

where as contactdetails.dateofbirth is

Public Property DateOfBirth() As String
Get
Return Me._DateOfBirth
End Get
Set(ByVal value As String)
Me._DateOfBirth = value
End Set
End Property

allanlorriman

ASKER

  Public Shared Function Age(ByVal Birthdate As System.DateTime) As Long
        Try
            Dim CurrentDate As System.DateTime = System.DateTime.Today
            Select Case Month(Birthdate)
                Case Is < Month(System.DateTime.Today)
                    Age = DateDiff("YYYY", Birthdate, Now())
                Case Is = Month(CurrentDate)
                    Select Case Day(Birthdate)
                        Case Is < Day(CurrentDate)
                            Age = DateDiff("YYYY", Birthdate, Now())
                        Case Is = Day(CurrentDate)
                            Age = DateDiff("YYYY", Birthdate, Now())
                        Case Is > Day(CurrentDate)
                            Age = DateDiff("YYYY", Birthdate, Now()) - 1
                    End Select
                Case Is > Month(CurrentDate)
                    Age = DateDiff("YYYY", Birthdate, Now()) - 1
                Case Else
                    Age = 0
            End Select
        Catch ex As System.Exception
            'Error handling code does here
        End Try
    End Function

Open in new window

allanlorriman

ASKER

Thanks CodeCruiser

For my other query I will open a new question...

Éric Moreau

why is your property (Public Property DateOfBirth() As String) declared as a string?

allanlorriman

ASKER

https://www.experts-exchange.com/questions/28490774/date-exception-vb-net.html

please check here

allanlorriman

ASKER

Hi Eric
thanks for looking into this for me, it was one of the old property which was being used elsewhere as well, I thought I could reuse in the code...
this is causing a problem now, can you advice a solution please? do not want to change the property as its being in reference on other places...
thank you

Éric Moreau

Create another readonly property that would return a DateOfBirth in datetime format to pass to your methods