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
allanlorrimanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
You can try and apply same date format on both sides

so change
EndTime=GetDate()

to

Convert(DateTime, EndTime, 103)=Convert(DateTime, GetDate(), 103)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
allanlorrimanAuthor Commented:
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

0
allanlorrimanAuthor Commented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

allanlorrimanAuthor Commented:
  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

0
allanlorrimanAuthor Commented:
Thanks CodeCruiser

For my other query I will open a new question...
0
Éric MoreauSenior .Net ConsultantCommented:
why is your property (Public Property DateOfBirth() As String) declared as a string?
0
allanlorrimanAuthor Commented:
0
allanlorrimanAuthor Commented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
Create another readonly property that would return a DateOfBirth in datetime format to pass to your methods
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.