Problem in Date and parameters

[sacramento]

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!

 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

[dday9]

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.:

CDate(Previsao_Nasc_1_Ovo) >= ?

By the way, I'd probably modify your code a bit to make it a bit simpler:

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:

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

[sacramento]

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"

[dday9]

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:

Dim da As OleDbDataAdapter

If so, then remove it. Same thing goes for cmd too.

[sacramento]

Same error with declaration

 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

[dday9]

That's what I'm saying, you need to remove the dim statement for da and cmd, wherever they live.

[sacramento]

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

"Name "da" is not declared"

If I declared return the other error

[dday9]

To clarify: keep the using statement declaration and remove the dim statement declaration. For example:

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.

[sacramento]

HI,
Sory the delay of the answear

The code work but i have a problem with NULL records and here:

da.Fill(ds, "table1")

I have an error that is: "Invalid use of Nulls"

Thanks

[dday9]

My guess is that CDate cannot be applied to null dates, maybe try adjusting the SQL to:

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.

[sacramento]

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

[dday9]

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:

CDate(Format(Previaso_Nasc_" & index & "_Ovo, ""dd-mm-yyyy""))

[sacramento]

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

[Zvoni]

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?