dcsimg
Results 1 to 13 of 13

Thread: [RESOLVED] Query and date

  1. #1

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

    Resolved [RESOLVED] Query and date

    Hi to all,

    Hi have this query, and objectif of this query is to extract dates that are >= to the date of today, but the query don't work well once for example i had try today and i have sort of values from 02-03-2019.

    Attached see my code and BD


    Thanks
    Attached Images Attached Images  
    Attached Files Attached Files

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,838

    Re: Query and date

    Which DBMS? In your Query you're encasing the Date with "#" implying Access.
    Next: Why in blazes are you formating your Date to dd/MM/yyyy? Use ISO-Format for Date
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3

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

    Re: Query and date

    Hi,

    I don't know wich information you want with DBMS, but i use DB Access and VB2008, with this kind of code to read DB:

    con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & My.Application.Info.DirectoryPath & "\slot.mdb"
    con.Open()

    ds = New DataSet
    Dim adapter As OleDb.OleDbDataAdapter


    Concerning Format of date is just to shure the format of the date is equal to the database records.

    When you mean ISO-Format you say somethin like this?:
    DateTime.UtcNow.ToString("yyyy-MM-ddTHH\\:mm\\:ss.fffffffzzz");

    Thanks

  4. #4

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

    Re: Query and date

    Hi,

    I don't know wich information you want with DBMS, but i use DB Access and VB2008, with this kind of code to read DB:

    con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & My.Application.Info.DirectoryPath & "\slot.mdb"
    con.Open()

    ds = New DataSet
    Dim adapter As OleDb.OleDbDataAdapter


    Concerning Format of date is just to shure the format of the date is equal to the database records.

    When you mean ISO-Format you say somethin like this?:
    DateTime.UtcNow.ToString("yyyy-MM-ddTHH\\:mm\\:ss.fffffffzzz");

    Thanks

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,838

    Re: Query and date

    DBMS = DataBase Managment System
    ISO-Format = yyyy-MM-dd
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,023

    Re: Query and date

    So that people can see it without lots of unnecessary effort, here is the query that was attached in post #1:
    Code:
    Dim data_de_hoje As String = Date.Today.ToString("dd/MM/yyyy")
    
     sql = "SELECT primeira_data as ColumnZ, local_prova, nome_evento, ano_evento, path_imagem from " & tabela_abrir_provas & "" & _
                      " where primeira_data >= #" & data_de_hoje & "#" & _
                      " union all " & _
                      " select segunda_data as columnZ, local_prova, nome_evento, ano_evento, path_imagem from " & tabela_abrir_provas & "" & _
                      " where segunda_data >= #" & data_de_hoje & "#" & _
                      " union all " & _
                      " select terceira_data as columnZ, local_prova, nome_evento, ano_evento, path_imagem from " & tabela_abrir_provas & "" & _
                      " where terceira_data >= #" & data_de_hoje & "#" & _
                      " union all " & _
                      " select quarta_data as columnZ, local_prova, nome_evento, ano_evento, path_imagem from " & tabela_abrir_provas & "" & _
                      " where quarta_data >= #" & data_de_hoje & "#" & _
                      " union all " & _
                      " select quinta_data as columnZ, local_prova, nome_evento, ano_evento, path_imagem from " & tabela_abrir_provas & "" & _
                      " where quinta_data >= #" & data_de_hoje & "#" & _
                      " union all " & _
                      " select sexta_data as columnZ, local_prova, nome_evento, ano_evento, path_imagem from " & tabela_abrir_provas & "" & _
                      " where sexta_data >= #" & data_de_hoje & "#" & _
                      " union all " & _
                      " select setima_data as columnZ, local_prova, nome_evento, ano_evento, path_imagem from " & tabela_abrir_provas & "" & _
                      " where setima_data >= #" & data_de_hoje & "#" & _
                      " union all " & _
                      " select oitava_data as columnZ, local_prova, nome_evento, ano_evento, path_imagem from " & tabela_abrir_provas & "" & _
                      " where oitava_data >= #" & data_de_hoje & "#" & _
                      " order by columnZ "
    As to the issue you are having, make sure the relevant database columns (primeira_data etc) have a data type that is Date based (rather than text based), and then use Parameters rather than putting the values into the query... Parameters are something you should always use, because they automatically deal with a wide variety of problems (including formatting of values, which appears to be the problem here).

    For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).


    You haven't shown how you are running the query, but what you should use is something a bit like this:
    Code:
                      " where primeira_data >= ? " & _
                      ...
                      " where segunda_data >= ? " & _
                      ...
    Code:
        cmd.Parameters.Add("", OleDbType.Date, , Date.Today)
        cmd.Parameters.Add("", OleDbType.Date, , Date.Today)

  7. #7

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

    Re: Query and date

    Hi,

    Thanks for your help si_the_geek, but, after read the post about parameters i remaing a little bit confused once i don't know where i declare the "?" used in querys...

    You had send me this code:
    Code:
    cmd.Parameters.Add("", OleDbType.Date, , Date.Today)
    1 - I don't know the way to declare cmd...is something like this?:
    Dim cmd As New OleDb.??????? (but i had try i few extensions and all give error)

    2 - This is the code that declare the parameters to use on "?" on query?



    Thanks

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,023

    Re: Query and date

    Quote Originally Posted by sacramento View Post
    i don't know where i declare the "?" used in querys...
    You put them into the query, where values (and the delimiters around them) would go.

    So instead of the original:
    Code:
                      " where primeira_data >= #" & data_de_hoje & "#" & _
    ..use the method I showed in my previous post.
    Code:
                      " where primeira_data >= ?" & _

    You had send me this code:
    Code:
    cmd.Parameters.Add("", OleDbType.Date, , Date.Today)
    1 - I don't know the way to declare cmd...is something like this?:
    Dim cmd As New OleDb.??????? (but i had try i few extensions and all give error)
    As implied by the sentence in my previous post just above the code boxes, what cmd should be (or should be changed to) depends on your current code.

  9. #9

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

    Re: Query and date

    Hi,

    Sory but i'm still lost

    I had change my query to incorporate paramaters ("?"), but I still without know where i can declare/put the rest of the code

    The all code that i have...sory the image but i can't post with Code, when reply the website return errors

    Name:  0001.jpg
Views: 29
Size:  61.2 KB


    Thanks

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,023

    Re: Query and date

    There are various different constructors ("new" options) you can use with OleDbDataAdapter, and you are currently using: (String, OleDbConnection) , you just need to change to one that allows a Command object, which is set up appropriately.

    Replace this line:
    Code:
    da = New OleDbDataAdapter(sql, con)
    with:
    Code:
    Dim cmd as New OleDbCommand(sql, con)
    For i as Integer = 1 To 8  
       cmd.Parameters.Add("", OleDbType.Date, , Date.Today)
    Next i
    
    da = New OleDbDataAdapter(cmd)
    You need to add the same amount of parameters as ? placeholders in your query, but as the values are all the same in this case, a loop is a good way to do it.

  11. #11

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

    Re: Query and date

    Thanks for your help...

    I had replace the line for your code, and i have this error:

    Name:  Error.jpg
Views: 29
Size:  31.8 KB

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,023

    Re: Query and date

    Try this version:
    Code:
       cmd.Parameters.Add("", OleDbType.Date).Value = Date.Today

  13. #13

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

    Re: Query and date

    Fantastic si_the_geek....work perfectlly.

    From today I will use parameters in my querys.

    Thanks a lot for the help

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