dcsimg
Results 1 to 15 of 15

Thread: [RESOLVED] VS 2017 getting date error conversation why?

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,994

    Resolved [RESOLVED] VS 2017 getting date error conversation why?

    hey
    i am getting this error and i dont know why.
    what i am missing?
    datatype in sql server is datetime
    error is in red
    Code:
                    Dim Sql As String = "SELECT Format([DatePayRishum],'mm-yyyy') AS MonthGroup,PaymentMethod," &
                    " Sum(Payments.Amount) AS SumOfAmount" &
                    " FROM Payments " &
                    " GROUP BY Format([DatePayRishum],'mm-yyyy'), PaymentMethod" &
                    " ORDER BY PaymentMethod"
                    Using Cmd As New SqlCommand(Sql, Cn)
                        Cn.Open()
                        Using Reader As SqlDataReader = Cmd.ExecuteReader
                            While Reader.Read
                                If paymethod <> Reader.Item("PaymentMethod") Then
                                    paymethod = Reader.Item("PaymentMethod")
                                    PayTashlom = ChartControl.Content.Series.Add(Reader.Item("PaymentMethod"))
                                End If
                            PayTashlom.Points.Add(String.Format(CDate(Reader.Item("MonthGroup")), "mmmm-yyyy"), Reader.Item("SumOfAmount"))
                        End While
                            Cn.Close()
                        End Using
                    End Using
    Name:  ERROR.jpg
Views: 259
Size:  6.1 KB
    tnx for the helpers
    salsa

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,156

    Re: VS 2017 getting date error conversation why?

    Because "41-2018" isn't a valid date.

  3. #3

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,994

    Re: VS 2017 getting date error conversation why?

    Quote Originally Posted by wes4dbt View Post
    Because "41-2018" isn't a valid date.
    yeah i know
    why i am getting it?

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,156

    Re: VS 2017 getting date error conversation why?

    Why are you getting 41-2018? How would I know? But you are telling your SQL to format it like this Format([DatePayRishum],'mm-yyyy'). Why it is returning a 41, I have no idea. Look at your data.

  5. #5

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,994

    Re: VS 2017 getting date error conversation why?

    if i knew i wouldnt be asking here my friend

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,156

    Re: VS 2017 getting date error conversation why?

    Have you check the data in the database to make sure you have valid data.

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,156

    Re: VS 2017 getting date error conversation why?

    Ok, I've done a test and you can't use CDate on a month-year value. You need a valid date mm-dd-yyyy.

    Sorry I'm wrong, it's the Format(someDate,"mm-yyyy") that's returning the bad value.

    Try this,
    Code:
            Dim dt As Date = Date.Now
            Dim str As String = Format(dt, "mm-yyyy")
    
            MessageBox.Show(str)
    Last edited by wes4dbt; Jun 28th, 2019 at 02:27 PM.

  8. #8

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,994

    Re: VS 2017 getting date error conversation why?

    ill try that
    tnx

  9. #9

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,994

    Re: VS 2017 getting date error conversation why?

    wll you gave me a way point
    Code:
      PayTashlom.Points.Add(String.Format("{0:dd/mm/yyyy}", Reader.Item("MonthGroup"), "mmmm-yyyy"), Reader.Item("SumOfAmount"))
    gracias

  10. #10
    Frenzied Member
    Join Date
    Jul 2011
    Location
    UK
    Posts
    1,243

    Re: [RESOLVED] VS 2017 getting date error conversation why?

    Quote Originally Posted by salsa31 View Post
    what i am missing?

    The Shift key?

    m is for minutes
    M is for Months

  11. #11
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,156

    Re: [RESOLVED] VS 2017 getting date error conversation why?

    Inferrd, Good catch. Damn case sensitive things!!!

    salsa, You could also use the DatePart function in you SQL.

  12. #12

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,994

    Re: [RESOLVED] VS 2017 getting date error conversation why?

    Quote Originally Posted by Inferrd View Post
    The Shift key?

    m is for minutes
    M is for Months
    ohhhh

  13. #13
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,156

    Re: [RESOLVED] VS 2017 getting date error conversation why?

    You know, I looked up the Format function in the MS documentation before I posted and saw this,

    Code:
    mm	01-12 (Month of year, with a leading zero, starting with January = 01)
    
    MyStr = Format(MyDate, "dddd, mmm d yyyy")    ' Returns "Wednesday, Jan 27 1993"
    Guess I should have looked up SQL Format Function. Learn something new every day.

  14. #14
    Frenzied Member
    Join Date
    Jul 2011
    Location
    UK
    Posts
    1,243

    Re: VS 2017 getting date error conversation why?

    Quote Originally Posted by wes4dbt View Post
    Code:
            Dim str As String = Format(dt, "mm-yyyy")
    Yeah, when I looked at salsa's code, that's the way I read it. Looking again, he's using the String.Format method, not the VB Format function.

    Code:
    PayTashlom.Points.Add(String.Format(CDate(Reader.Item("MonthGroup")), "mmmm-yyyy"), Reader.Item("SumOfAmount"))
    should be either
    Code:
    PayTashlom.Points.Add(String.Format("{0:MMMM-yyyy}", CDate(Reader.Item("MonthGroup"))), Reader.Item("SumOfAmount"))
    or
    Code:
    PayTashlom.Points.Add(Format(CDate(Reader.Item("MonthGroup")), "MMMM-yyyy"), Reader.Item("SumOfAmount"))

    That said, would it not make more sense to pull the value out of the database as a DateTime type instead of converting to a String, then to a Date and then back to a String again (and possibly even implicitly back to a Date yet again when added to the chart's series points collection)?

  15. #15

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,994

    Re: [RESOLVED] VS 2017 getting date error conversation why?

    That said, would it not make more sense to pull the value out of the database as a DateTime type instead of converting to a String, then to a Date and then back to a String again (and possibly even implicitly back to a Date yet again when added to the chart's series points collection)?
    good point
    the value in the database hold also a time
    29/06/2019 15:50

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width