Results 1 to 14 of 14

Thread: Problem in Date and parameters

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,413

    Problem in Date and parameters

    Hi to all:

    This piece of code, try to extract all dates taht should be >= Date.today, but infornuttely this not happen, and i can't see why!

    Values in my database:
    Previsao_Nasc_1_Ovo - 10-05-2024
    Previsao_Nasc_2_Ovo - 13-05-2024
    Previsao_Nasc_3_Ovo - 16-05-2024

    The code sort in ColumnZ:
    03-07-2024
    03-07-2024
    03-07-2024

    Well, that i had write in my code is all the dates should be >= date.today but the code don't sort the dates correctly.

    In this example the code could sort nothing because all dates in "Previsao_Nasc_x_Ovo" is small the day today

    Any help please!

    Code:
     sql = _
            "SELECT Previsao_Nasc_1_Ovo as ColumnZ, Viveiro_N" & _
            " FROM " & eventos & "" & _
            " WHERE Previsao_Nasc_1_Ovo >= ? " & _
            " UNION all " & _
            " SELECT Previsao_Nasc_2_Ovo as columnZ, Viveiro_N" & _
            " FROM " & eventos & "" & _
            " where Previsao_Nasc_2_Ovo >= ? " & _
            " UNION all " & _
            " SELECT Previsao_Nasc_3_Ovo as columnZ, Viveiro_N" & _
            " FROM " & eventos & "" & _
            " WHERE Previsao_Nasc_3_Ovo >= ? " & _
            " UNION all " & _
            " SELECT Previsao_Nasc_4_Ovo as columnZ, Viveiro_N" & _
            " FROM " & eventos & "" & _
            " WHERE Previsao_Nasc_4_Ovo >= ? " & _
            " UNION all " & _
            " SELECT Previsao_Nasc_5_Ovo as columnZ, Viveiro_N" & _
            " FROM " & eventos & "" & _
            " WHERE Previsao_Nasc_5_Ovo >= ? " & _
            " UNION all " & _
            " SELECT Previsao_Nasc_6_Ovo as columnZ, Viveiro_N" & _
            " FROM " & eventos & "" & _
            " WHERE Previsao_Nasc_6_Ovo >= ? " & _
            " UNION all " & _
            " SELECT Previsao_Nasc_7_Ovo as columnZ, Viveiro_N" & _
            " FROM " & eventos & "" & _
            " WHERE Previsao_Nasc_7_Ovo >= ? " & _
            " UNION all " & _
            " SELECT Previsao_Nasc_8_Ovo as columnZ, Viveiro_N" & _
            " FROM " & eventos & "" & _
            " WHERE Previsao_Nasc_8_Ovo >= ? " & _
            " order by columnZ "
    
    
            Dim cmd As New OleDbCommand(sql, con)
            For i As Integer = 1 To 8
                cmd.Parameters.Add("", OleDbType.Date).Value = Date.Today
            Next i
    
            da = New OleDbDataAdapter(cmd)
    
    
            da.Fill(ds, "table1")

    Thanks to all

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,886

    Re: Problem in Date and parameters

    What are your data column definitions? If they are defined as text instead of date, then you will need to cast them first. E.g.:
    Code:
    CDate(Previsao_Nasc_1_Ovo) >= ?
    By the way, I'd probably modify your code a bit to make it a bit simpler:
    Code:
    Dim range = Enumerable.Range(1, 8)
    Dim unions = range.Select(Function(index) $"SELECT Previaso_Nasc_{index}_Ovo As ColumnZ, Viveiro_N FROM {eventos} WHERE CDate(Previaso_Nasc_{index}_Ovo) >= ?")
    Dim sql = String.Join($"{Environment.NewLine}UNION ALL{Environment.NewLine}", unions)
    Using cmd = New OleDbCommand(sql, con)
        For Each index In range
            cmd.Parameters.Add("", OleDbType.Date).Value = Date.Today
        Next
        Using da = New OleDbDataAdapter(cmd)
            da.Fill(ds, "table1")
        End Using
    End Using
    Edit - I see you're using VS 2005 which presumably means you are not using .NET Framework 3.5 or higher in which case my simplified code wouldn't apply. However, the point about CDate applies.

    Edit #2 - Here is a version of the simplified code that would work with .NET Framework 2.0:
    Code:
    Dim eventos = "table1"
    Dim upperBounds = 8
    Dim unions(upperBounds - 1) As String
    For index = 1 To upperBounds
        unions(index - 1) = "SELECT Previaso_Nasc_" & index & "_Ovo As ColumnZ, Viveiro_N FROM " & eventos & " WHERE CDate(Previaso_Nasc_" & index & "_Ovo) >= ?"
    Next
    Dim sql = String.Join(Environment.NewLine & "UNION ALL" & Environment.NewLine, unions)
    Using cmd = New OleDbCommand(sql, con)
        For index = 1 To upperBounds
            cmd.Parameters.Add("", OleDbType.Date).Value = Date.Today
        Next
        Using da = New OleDbDataAdapter(cmd)
            da.Fill(ds, "table1")
        End Using
    End Using
    Fiddle: https://dotnetfiddle.net/sWde7o
    Last edited by dday9; Jun 19th, 2024 at 01:19 PM.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,413

    Re: Problem in Date and parameters

    Hi,
    Thanks for your reply.
    I try to use your code EDIT #2 but i have an error:

    Using da = New OleDbDataAdapter(cmd)
    In Line Mark as bold VB return an error:
    "Variable "da" hides a variable in an enclosing block"

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,886

    Re: Problem in Date and parameters

    That is because it is essentially declaring the da variable twice. Based on your code, I'm assuming you have it declared somewhere above where that code executes like this:
    Code:
    Dim da As OleDbDataAdapter
    If so, then remove it. Same thing goes for cmd too.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,413

    Re: Problem in Date and parameters

    Same error with declaration

    Code:
     Dim da As OleDbDataAdapter
            Dim eventos = "table1"
            Dim upperBounds = 8
            Dim unions(upperBounds - 1) As String
            For index = 1 To upperBounds
                unions(index - 1) = "SELECT Previaso_Nasc_" & index & "_Ovo As ColumnZ, Viveiro_N FROM " & eventos & " WHERE CDate(Previaso_Nasc_" & index & "_Ovo) >= ?"
            Next
            Dim sql = String.Join(Environment.NewLine & "UNION ALL" & Environment.NewLine, unions)
            Using cmd = New OleDbCommand(sql, con)
                For index = 1 To upperBounds
                    cmd.Parameters.Add("", OleDbType.Date).Value = Date.Today
                Next
                Using da = New OleDbDataAdapter(cmd)
                    da.Fill(ds, "table1")
                End Using
            End Using

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,886

    Re: Problem in Date and parameters

    That's what I'm saying, you need to remove the dim statement for da and cmd, wherever they live.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,413

    Re: Problem in Date and parameters

    Well, if i remove the declaration then annother error emerge:

    "Name "da" is not declared"

    If I declared return the other error

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,886

    Re: Problem in Date and parameters

    To clarify: keep the using statement declaration and remove the dim statement declaration. For example:
    Code:
    Private Function GetUnionedTable(eventos As String, con As OleDbConnection)
        ' define the data set
        Dim ds = New DataSet()
    
        ' dynamically build the SQL
        Dim upperBounds = 8
        Dim unions(upperBounds - 1) As String
        For index = 1 To upperBounds
            unions(index - 1) = "SELECT Previaso_Nasc_" & index & "_Ovo As ColumnZ, Viveiro_N FROM " & eventos & " WHERE CDate(Previaso_Nasc_" & index & "_Ovo) >= ?"
        Next
        Dim sql = String.Join(Environment.NewLine & "UNION ALL" & Environment.NewLine, unions)
    
        ' define command, using the sql and connection
        Using cmd = New OleDbCommand(sql, con)
            ' dynamically build the parameters
            For index = 1 To upperBounds
                cmd.Parameters.Add("", OleDbType.Date).Value = Date.Today
            Next
            ' define the adapter, using the command
            Using da = New OleDbDataAdapter(cmd)
                ' fill the data set
                da.Fill(ds, "table1")
            End Using
        End Using
    
        ' return the data set
        Return ds
    End Function
    If you still get the error: Variable "da" hides a variable in an enclosing block

    Then that means you have da defined somewhere outside that function block.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,413

    Re: Problem in Date and parameters

    HI,
    Sory the delay of the answear

    The code work but i have a problem with NULL records and here:
    Code:
    da.Fill(ds, "table1")
    I have an error that is: "Invalid use of Nulls"

    Thanks

  10. #10
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,886

    Re: Problem in Date and parameters

    My guess is that CDate cannot be applied to null dates, maybe try adjusting the SQL to:
    Code:
    unions(index - 1) = "SELECT Previaso_Nasc_" & index & "_Ovo As ColumnZ, Viveiro_N FROM " & eventos & " WHERE IIF(Previaso_Nasc_" & index & "_Ovo IS NULL, #01/01/1900#, CDate(Previaso_Nasc_" & index & "_Ovo)) >= ?"
    What this does in the WHERE clause is coalesce the null value to the minimum date.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,413

    Re: Problem in Date and parameters

    Hi,
    The code is OK, no errors, but the initial problem Remain, the dates don't sort like desired

  12. #12
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,886

    Re: Problem in Date and parameters

    I just realized that your dates are stored in dd-MM-yyyy format which might affect the CDate.

    Shot in the dark, but modify the CDate to use:
    Code:
    CDate(Format(Previaso_Nasc_" & index & "_Ovo, ""dd-mm-yyyy""))
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    Nov 2004
    Posts
    1,413

    Re: Problem in Date and parameters

    Hi,

    Yes, the dates are stores in dd-MM-yyyy format and the fields are fields Date

    But the problem remain, even "Format" can't sort the dates like desired

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,608

    Re: Problem in Date and parameters

    Quote Originally Posted by sacramento View Post
    Hi,

    Yes, the dates are stores in dd-MM-yyyy format and the fields are fields Date

    But the problem remain, even "Format" can't sort the dates like desired
    Then STORE the Dates in ISO-Format in the Database ("YYYY-MM-DD") and DISPLAY them in whatever Format you want, and be done with it

    EDIT: Nevermind that this smells heavily like a misdesigned table.
    8 Columns with (different) dates, but querying for the same parameterised Filter?
    Last edited by Zvoni; Jun 24th, 2024 at 08:40 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width