PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] how to query data in sql base on the date selected by a user in date time picker-VBForums
Results 1 to 11 of 11

Thread: [RESOLVED] how to query data in sql base on the date selected by a user in date time picker

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    69

    Resolved [RESOLVED] how to query data in sql base on the date selected by a user in date time picker

    hi All,

    I want to modify my program, how can i query data to sql server 2008 based on the date inputed by user in date time picker ?

    see image:
    Name:  Capturesss.jpg
Views: 374
Size:  11.5 KB

    i try code below but throw error : near ',',
    Code:
      sqlCmd.CommandText = "WITH Src AS (SELECT station_no,CASE WHEN TestResult = 'ok' THEN 1 END AS ok,CASE WHEN TestResult = 'NG' " &
                    "THEN 1 END AS NG,CASE WHEN TestResult = 'RR' THEN 1 END AS RR FROM Muscatel_MPCA_FCT1_LogData_T WHERE SQLDateTime >= '" + dtpFrom.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "', AND SQLDateTime <= '" + dtpTo.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "' and Station_No in (1,2,3,4,5,6))SELECT station_no, COUNT(ok) AS TotalOK,COUNT(NG) " &
                    "AS TotalNG,COUNT(RR) AS TotalRR,COUNT(ok) + COUNT(NG) + COUNT(RR) AS Total FROM Src GROUP BY ROLLUP (Station_No)ORDER BY " &
                    "Station_No DESC"
    thanks in advance.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,208

    Re: how to query data in sql base on the date selected by a user in date time picker

    Thread moved from the 'CodeBank VB.Net' forum (which is for you to post working code examples, not questions) to the 'VB.Net' forum


    Rather than appending values to the SQL statement, it is far safer and more reliable to use Parameters.

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


    In simple terms you should be changing from this:
    Code:
      sqlCmd.CommandText = "...WHERE SQLDateTime >= '" + dtpFrom.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "', AND SQLDateTime <= '" + dtpTo.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "' and Station_No ..."
    ...to something like this:
    Code:
      sqlCmd.CommandText = "...WHERE SQLDateTime >= @MinDate AND SQLDateTime <= @MaxDate and Station_No ..."
      sqlCmd.Parameters.AddWithValue("@MinDate", dtpFrom.Value)
      sqlCmd.Parameters.AddWithValue("@MaxDate", dtpTo.Value)

  3. #3
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    900

    Re: how to query data in sql base on the date selected by a user in date time picker

    In addition to using parameters you do have an extra comma that needs removed shown in red below
    WHERE SQLDateTime >= '" + dtpFrom.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "', AND SQLDateTime <= '" + dtpTo.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") "'

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    69

    Re: how to query data in sql base on the date selected by a user in date time picker

    Quote Originally Posted by si_the_geek View Post
    Thread moved from the 'CodeBank VB.Net' forum (which is for you to post working code examples, not questions) to the 'VB.Net' forum


    Rather than appending values to the SQL statement, it is far safer and more reliable to use Parameters.

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


    In simple terms you should be changing from this:
    Code:
      sqlCmd.CommandText = "...WHERE SQLDateTime >= '" + dtpFrom.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "', AND SQLDateTime <= '" + dtpTo.Value.ToString("yyyy-mm-ddThh:mm:ss:tt") + "' and Station_No ..."
    ...to something like this:
    Code:
      sqlCmd.CommandText = "...WHERE SQLDateTime >= @MinDate AND SQLDateTime <= @MaxDate and Station_No ..."
      sqlCmd.Parameters.AddWithValue("@MinDate", dtpFrom.Value)
      sqlCmd.Parameters.AddWithValue("@MaxDate", dtpTo.Value)
    hi Sir,
    what if i want to QUERY EXACTLY 1 DAY ? not just date. example CODE below.1 whole day. is there a way to do that ?
    Code:
    WHERE SQLDateTime >= '2018-07-02T00:00:01 AM'
    AND SQLDateTime <= '2018-07-02T23:59:59 AM'
    I did it already using string query as below: is that also possible using parameters and sql server 2008 ?
    Code:
    and TEST_START BETWEEN TO_DATE('" + dtpFrom.Value.ToString("yyyyMMdd") + "','yyyymmdd')and to_date('" + dtpTo.Value.ToString("yyyyMMdd 23:59:59") + "','yyyymmdd hh24:mi:ss')
    Last edited by BONITO; Jul 12th, 2018 at 09:57 AM.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: how to query data in sql base on the date selected by a user in date time picker

    If you care about the date and not the time then use the date and not the time, e.g.
    vb.net Code:
    1. Dim startDate = myDateTimePicker.Value.Date
    2. Dim endDate = startDate.AddDays(1)
    3. Dim sql = "SELECT * FROM MyTable WHERE [Date] >= @StartDate AND [Date] < @EndDate"
    Take particular note of the comparison operators. If the user selects, for instance, #1/01/2000# then that SQL would be equivalent to:
    vb.net Code:
    1. Dim sql = "SELECT * FROM MyTable WHERE [Date] >= '2000-01-01 00:00:00' AND [Date] < '2000-01-02 00:00:00'"
    so it's any time from midnight on the morning of the specified date to before midnight on the following morning.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    69

    Re: how to query data in sql base on the date selected by a user in date time picker

    hi Sir,
    I care about time and date. example i query from july 12 2018 to july 12 2018, i choose same date from 2 date time picker.
    it should return data from morning of July 12 to current time.

    and if i query july1 2018 to july 12 2018 thats the time i care about date.
    from my query on oracle below it works .
    Code:
    and TEST_START BETWEEN TO_DATE('" + dtpFrom.Value.ToString("yyyyMMdd") + "','yyyymmdd')and to_date('" + dtpTo.Value.ToString("yyyyMMdd 23:59:59") + "','yyyymmdd hh24:mi:ss')
    but it not use parameter and i think not support in sql server 2008 . please adviced thanks.

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,208

    Re: how to query data in sql base on the date selected by a user in date time picker

    You can still use jmcilhinney's suggestion, you just need to set the values like this:
    Code:
    Dim startDate = dtpFrom.Value.Date
    Dim endDate = dtpTo.Value.Date.AddDays(1)

  8. #8
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    900

    Re: how to query data in sql base on the date selected by a user in date time picker

    Quote Originally Posted by BONITO View Post
    I care about time and date. example i query from july 12 2018 to july 12 2018, i choose same date from 2 date time picker.
    it should return data from morning of July 12 to current time.
    By time, jmc was meaning that you are not worried about start or end times in the middle of a day, just results from full days. If you wanted results from 1 "shift" of work (0600-1800 for example) then time would be important.

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    69

    Re: how to query data in sql base on the date selected by a user in date time picker

    hi Sir,

    I do that but still cannot query from July 13,2018 to July 13,208.
    Code:
     
    Dim startDate = dtpFrom.Value.Date
    Dim endDate = dtpTo.Value.Date.AddDays(1)
    
    'Query
    WHERE  SQLDateTime >=  @startDate AND SQLDateTime < @endDate  and
    I try to query in sql server 2008 using same date it really return nothing . but when i put time to the Date_To it can able to query .
    see my code in sql . this one works.
    Code:
    WHERE SQLDateTime >= '2018-07-11'
    AND SQLDateTime < '2018-07-11 23:59:59'
    but this one below , not works
    Code:
    WHERE SQLDateTime >= '2018-07-11'
    AND SQLDateTime < '2018-07-11'
    so i think i need to declare time also for my endTime not just date , so i try code below, but still not work.

    Code:
      Dim startDate = dtpFrom.Value.Date
            Dim endDate = dtpTo.CustomFormat
            dtpTo.Format = DateTimePickerFormat.Custom
            dtpTo.CustomFormat = "dddd, MMMM dd, yyyy HH:mm:ss"
    please advice.

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,026

    Re: how to query data in sql base on the date selected by a user in date time picker

    No you do not need to declare time. You're really not thinking about this. Look at the code you posted:
    Code:
    WHERE SQLDateTime >= '2018-07-11'
    AND SQLDateTime < '2018-07-11'
    How can any value of SQLDateTime ever possibly be both greater than or equal to a specific date AND less than that same date at the same time? Why do you think that we used AddDays(1) in our code? The whole point is that the end date in the query is one day greater than the last day you want records for. If you want records for just '2018-07-11' then your start date in the query needs to be '2018-07-11' and you end date in the query needs to be '2018-07-12'. That would mean this code:
    Code:
    WHERE SQLDateTime >= '2018-07-11'
    AND SQLDateTime < '2018-07-12'
    When you omit the time your are implicitly specifying '00:00:00' i.e. midnight on the morning of the date specified. That means that that second query is going to get every record where the date part of SQLDateTime is '2018-07-11', regardless of the time.

    Going back to your first code snippet:
    Code:
    Dim startDate = dtpFrom.Value.Date
    Dim endDate = dtpTo.Value.Date.AddDays(1)
    
    'Query
    WHERE  SQLDateTime >=  @startDate AND SQLDateTime < @endDate  and
    if the user selects July 13, 2018 in both DateTimePickers then 'startDate' will be #7/13/2018# and 'endDate' will be #7/14/2018# so that query will get every record where the date part of SQLDateTime is '2018-07-13', regardless of time. That is it, that is all. I've done this myself many times and so have many other people, probably including all those others who have contributed to this thread. If you do as instructed, it will work as intended. If it doesn't work as intended, you must not have done as instructed.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    69

    Re: how to query data in sql base on the date selected by a user in date time picker

    Hi Sir,

    I got it already! thanks a lot !!
    I found the problem in my code. it was in the call of my PARAMETER.

Tags for this Thread

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