?
Solved

date exception - vb.net

Posted on 2014-08-05
9
Medium Priority
?
327 Views
Last Modified: 2014-08-05
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
0
Comment
Question by:allanlorriman
  • 6
  • 2
9 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 40240838
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
 

Author Comment

by:allanlorriman
ID: 40240874
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
 

Author Comment

by:allanlorriman
ID: 40240878
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:allanlorriman
ID: 40240881
  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
 

Author Closing Comment

by:allanlorriman
ID: 40240951
Thanks CodeCruiser

For my other query I will open a new question...
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40240953
why is your property (Public Property DateOfBirth() As String) declared as a string?
0
 

Author Comment

by:allanlorriman
ID: 40240958
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40240964
Create another readonly property that would return a DateOfBirth in datetime format to pass to your methods
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question