Date and Time quering !-VBForums
Results 1 to 2 of 2

Thread: Date and Time quering !

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Mar 2012
    Posts
    26

    Exclamation Date and Time quering !

    Code:
    Dim purchasedate, purchasetime As String
            purchasedate = Date.Today
            purchasetime = Now.ToString("HH:mm:ss")
            Dim purchasetime2 As String = Now.AddSeconds(120).ToString("HH:mm:ss")
            Dim subtotal As String
    
            'Totals the amount spent by the customer in one purchase
            Dim Str As OleDbCommand = New OleDbCommand
            'Totals the amount spent within the purchasing time period to ensure customer is not entitled to a prize more than once if theier spending is not over 100 
            Str.CommandText = "SELECT SUM(Total_spent) FROM Customer_Order WHERE CustomerID = " & GetcustomerID & " AND Order_Date = #" & purchasedate & "# AND Order_Time >= #" & purchasetime & "# AND Order_Time <= #" & purchasetime2 & "# "
            Str.Connection = cn
            subtotal = CType(Str.ExecuteScalar(), String)
    The "SELECT SUM(Total_spent) FROM Customer_Order WHERE CustomerID = " & GetcustomerID & " AND Order_Date = #" & purchasedate & "# AND Order_Time >= #" & purchasetime & "# AND Order_Time <= #" & purchasetime2 & "# " query seems to work in access but not in VB it passes down a null value to my stubtotal variable which is weird because it work fine in access

    Any ideas?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,078

    Re: Date and Time quering !

    For a start, don't use string concatenation to insert values into SQL code. Always use parameters. To learn why and how, follow the Blog link in my signature and check out my post on Parameters In ADO.NET. Once you've fixed that, post back if the issue persists.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

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

Survey posted by VBForums.