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
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,298

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width