CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: The orders after 12 am to 7 am not show timer problem

  1. #1
    Join Date
    Jan 2014
    Posts
    29

    The orders after 12 am to 7 am not show timer problem

    Hi guys

    I make program to select data every day automatically by timer every second in c#

    In sql server 2005 i write the following query

    ALTER proc [dbo].[JeddahSalesAll]
    as
    SELECT ROW_NUMBER() OVER (ORDER BY dbo.[Jeddah-Live$Sales Header].No_) AS [S.N], dbo.[Jeddah-Live$Sales Line].[Document No_] AS 'OrderNo',
    dbo.[Jeddah-Live$Sales Header].[Bill-to Name] AS 'CustomerNo', dbo.[Jeddah-Live$Sales Line].Area AS 'Shippment Type', dbo.[Jeddah-Live$Sales Line].Description AS 'Description',
    dbo.[Jeddah-Live$Sales Header].[Pump No_] AS 'PumpNo', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].Quantity, 0, 1) AS int) AS 'Required Qunatity',
    CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Quantity Shipped], 0, 1) AS int) AS 'Shipped Quantity', CAST(ROUND(dbo.[Jeddah-Live$Sales Line].[Outstanding Quantity], 0,
    1) AS int) AS 'Outstanding Qunatity '
    FROM dbo.[Jeddah-Live$Sales Header] INNER JOIN
    dbo.[Jeddah-Live$Sales Line] ON dbo.[Jeddah-Live$Sales Header].No_ = dbo.[Jeddah-Live$Sales Line].[Document No_] AND
    dbo.[Jeddah-Live$Sales Header].[Sell-to Customer No_] = dbo.[Jeddah-Live$Sales Line].[Sell-to Customer No_]
    WHERE DATEDIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0

    IN C# WINDOWS FORM VISUAL STUDIO 2008 IN TIMER TICK EVENT INTERVAL IS 1000MILISECOND(EVERY SECOND)

    ales.SalesClass SalesClass1 = new Sales.SalesClass();
    DataTable dt = SalesClass1.ShowSalesData("Data Source=192.168.1.5;Initial Catalog=Altawi-last06-01-2015;User ID=admin;Password=123");
    dataGridView1.DataSource = dt;
    dataGridView1[0, dataGridView1.Rows.Count - 1].Value = "Total Sum";
    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0].Style.BackColor = Color.Yellow;
    dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[1].Style.ForeColor = Color.Red;
    dataGridView1.Refresh();

    CLASS CODE AS FOLLOWING

    class SalesClass
    {
    public DataTable ShowSalesData(string ConnectionString)
    {
    SqlConnection con = new SqlConnection(ConnectionString);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "JeddahSalesAll";
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();
    da.Fill(ds);
    DataTable dt = ds.Tables[0];
    return dt;
    }

    code above work success no any problem

    only problem the order start 8 pm in date(31/03/2015) and finish 7 am in date(01/04/2015)

    at next day

    what i do to show the orders done from 12 am to 7 am to show

    if you have solution or suggestion to solve problem help me if possible

    thanks

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,277

    Re: The orders after 12 am to 7 am not show timer problem

    Are there any orders between 12am and 7am in the database?

    Does the following where clause properly span the two days? WHERE DATEDIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0

  3. #3
    Join Date
    Jan 2014
    Posts
    29

    Re: The orders after 12 am to 7 am not show timer problem

    Thank you for reply
    yes there are more orders from 12 am to 7 am
    and i need to show it with date start
    the order start in 31/03/2015 8 pm and finish 7 am
    so that the following statement retrieve only today
    WHERE DATEDIFF(d,dbo.[Jeddah-Live$Sales Line].[Shipment Date],GETDATE()) = 0
    how to change this statement to accept next day until 7 am if possible

  4. #4
    Join Date
    Jan 2014
    Posts
    29

    Re: The orders after 12 am to 7 am not show timer problem

    Meaning the statement above show from 8 pm to 12 am
    but what i need to show data orders from 8 pm to 7 am

  5. #5
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,277

    Re: The orders after 12 am to 7 am not show timer problem

    Instead of DATEDIFF use something else like calculating the start and end date times and use between.

    Debug this using a sql query before trying this in your C# code.

  6. #6
    Join Date
    Jan 2014
    Posts
    29

    Re: The orders after 12 am to 7 am not show timer problem

    Thank you for reply
    how to calculating the start and end date times and use between

  7. #7
    Join Date
    Jan 2014
    Posts
    29

    Re: The orders after 12 am to 7 am not show timer problem

    to more show what i need

    shipped date(user enter it) time dateof insert(server date)

    31/03/2015 8 pm 31/03/2015

    31/03/2015 10 pm 31/03/2015

    31/03/2015 1 am 01/04/2015

    31/03/2015 7 am 01/04/2015

    query above return 8 pm and 10 pm

    but not show 1 am and 7 am

    so taht what i need to show is 8 pm,10 pm,1 am,7 am

    only I need to show orderes that happen in same day and data shipped after 12 until 7 am

    Meaning if order still work two days i need to show it based on date starting in it

    we put 31/03/2015 in the day of 01/04/2015 because shipping date start in 31/03/2015

    but problem happen when order still work to another day

  8. #8
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,277

    Re: The orders after 12 am to 7 am not show timer problem

    The following will give you a date time that is tomorrow's date at 7am.

    SELECT CAST((CAST(CAST(GETDATE() + 1 AS DATE) AS NVARCHAR(10)) + ' 07:00:00') AS DATETIME)

    As I mentioned, do this in SQL and get the query working right before you run it in C#.

    Here's some test code:
    Code:
    DECLARE @ShipmentDates TABLE
    (
    	Id INT IDENTITY(1,1)
    	, ShipmentDate DATETIME
    )
    
    INSERT INTO @ShipmentDates SELECT '03/31/2015 08:00:00'
    INSERT INTO @ShipmentDates SELECT '03/31/2015 22:00:00'
    INSERT INTO @ShipmentDates SELECT '03/31/2015 08:00:00'
    INSERT INTO @ShipmentDates SELECT '04/01/2015 01:00:00'
    INSERT INTO @ShipmentDates SELECT '04/01/2015 07:00:00'
    
    INSERT INTO @ShipmentDates SELECT '04/01/2015 08:00:00'
    INSERT INTO @ShipmentDates SELECT '04/01/2015 22:00:00'
    INSERT INTO @ShipmentDates SELECT '04/01/2015 08:00:00'
    INSERT INTO @ShipmentDates SELECT '04/02/2015 01:00:00'
    INSERT INTO @ShipmentDates SELECT '04/02/2015 07:00:00'
    
    INSERT INTO @ShipmentDates SELECT '04/02/2015 08:00:00'
    INSERT INTO @ShipmentDates SELECT '04/02/2015 22:00:00'
    INSERT INTO @ShipmentDates SELECT '04/02/2015 08:00:00'
    INSERT INTO @ShipmentDates SELECT '04/03/2015 01:00:00'
    INSERT INTO @ShipmentDates SELECT '04/03/2015 07:00:00'
    
    SELECT [ShipmentDate] FROM @ShipmentDates
     -- Shipment dates that are greater than 7am today and less than/equal to 7am tomorrow
     WHERE [ShipmentDate] > CAST((CAST(CAST(GETDATE() AS DATE) AS NVARCHAR(10)) + ' 07:00:00') AS DATETIME)
    	AND [ShipmentDate] <= CAST((CAST(CAST(GETDATE() + 1 AS DATE) AS NVARCHAR(10)) + ' 07:00:00') AS DATETIME)
    When run today, this correctly returns the 'middle' 5 records that are past 7am today and are before (and including) 7am tomorrow.

    Code:
    ShipmentDate
    -----------------------
    2015-04-01 08:00:00.000
    2015-04-01 22:00:00.000
    2015-04-01 08:00:00.000
    2015-04-02 01:00:00.000
    2015-04-02 07:00:00.000

    Probably a SQL expert would be able to do this more cleanly or with fewer casts.
    Last edited by Arjay; April 1st, 2015 at 06:16 PM.

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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)