SQL + VB Help Please


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 29

Thread: SQL + VB Help Please

  1. #1
    Join Date
    Feb 2007
    Location
    Dorset, England
    Posts
    46

    Talking SQL + VB Help Please

    Hi, can some one correct my mistake. I have been trying this all day and I cant work it out

    Code:
            StartDate = Format(txt_From, "yyyy-mm-dd")
            EndDate = Format(txt_To, "yyyy-mm-dd")
            DoEvents
            Order.Open "SELECT product,`desc`,ordedate,weight From detail " & _
            "WHERE (ordedate between '" & (StartDate) And (EndDate) & "'" & _
            "ORDER BY ordedate", OrderStr, adOpenStatic, adLockOptimistic
            Order.MoveFirst
            Debug.Print Order.RecordCount
            
            Label1.Caption = "Number of Records Found = " & Order.RecordCount
            Order.Close
    I am having a proble with the WHERE bit of it, I am sure it is really easy, so please help

    Thanks

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Try
    Code:
    "WHERE orderdate BETWEEN '" & StartDate & "' AND '" & EndDate & "' "
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Feb 2007
    Location
    Dorset, England
    Posts
    46
    Thanks Hack, tried it and I get the same Error I have all day ODBC driver does not support the requested properties.
    I am using VB6 and windows 2000 sp4 (if that makes any difference)

    If I put in "WHERE (ordedate Between { d '2007-07-17' } and { d '2007-07-18' }) " it works fine but I want user to define start and end dates.
    Last edited by Chris Yard; 07-24-2007 at 11:40 AM. Reason: More Info

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Well, thats do. Do this
    Code:
    Dim sSQL As String
    sSQL = "SELECT product,`desc`,ordedate,weight From detail " 
    sSQL = sSQL & WHERE orderdate BETWEEN '" & StartDate & "' AND '" & EndDate & "' " 
    sSQL = sSQL & "ORDER BY ordedate"
    Debug.Print sSQL
    Order.Open sSQL, OrderStr, adOpenStatic, adLockOptimistic
    Post what is dumped to the immdiate window.

    Order is declared as an ADODB.Recordset object, right?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Feb 2007
    Location
    Dorset, England
    Posts
    46
    From immdiate window:

    SELECT product,`desc`,ordedate,weight From detail WHERE orderdate BETWEEN '2007-07-18' AND '2007-07-19' ORDER BY ordedate


    Code:
        OrderStr = "Provider=MSDASQL.1;" & _
        "Persist Security Info=False;" & _
        "Extended Properties=DSN=Visual FoxPro Database;" & _
        "UID=;" & "SourceDB=o:\database;" & "SourceType=DBF;" & _
        "Exclusive=No;" & "BackgroundFetch=Yes;" & _
        "Collate=Machine;" & "Null=Yes;" & "Deleted=Yes;"
        
        Set Order = New ADODB.Recordset

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Ah, I see you are using a FoxPro database. Have you run SQL queries against FoxPro database before, successfully?

    Lets break the query down and isolate the offending parts.

    Comment out the entire WHERE clause. Just do a SELECT blah, blah, FROM table - run it, and see if it errors.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Try
    Code:
    "WHERE orderdate BETWEEN { d '" & StartDate & "' } AND { d '" & EndDate & "' }"
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  8. #8
    Join Date
    Feb 2007
    Location
    Dorset, England
    Posts
    46
    Runs fine and returns 34355 records and I can move throu and view all records OK once WHERE is commented out

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Phil Weber
    Try
    Code:
    "WHERE orderdate BETWEEN { d '" & StartDate & "' } AND { d '" & EndDate & "' }"
    Is this something FoxPro specific? I ask because in all the years I've written SQL queries I've never used brackets in this fashion (I've also never used FoxPro. )
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Chris Yard
    Runs fine and returns 34355 records and I can move throu and view all records OK once WHERE is commented out
    Well, then we know it is definately the WHERE clause.

    Try Phil Weber's suggestion.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  11. #11
    Join Date
    Feb 2007
    Location
    Dorset, England
    Posts
    46
    Tried Phil's and I no longer get an error Yipeee BUT I have a -1 record count???!!!!!!?????

  12. #12
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    The is common with an ADO recordset. In fact, it was a feature purposely built in when ADO was created.

    Use a static cursor if you want a recordcount.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  13. #13
    Join Date
    Feb 2007
    Location
    Dorset, England
    Posts
    46
    Thank You Both, Really Really Quick Serivice And A Result !!!!!!

    :-) :-)

  14. #14
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    You are welcome and be sure to tell your friends and family about DevX.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  15. #15
    Join Date
    Feb 2007
    Location
    Dorset, England
    Posts
    46
    This is my WORKING code

    Code:
        OrderStr = "Provider=MSDASQL.1;" & _
        "Persist Security Info=False;" & _
        "Extended Properties=DSN=Visual FoxPro Database;" & _
        "UID=;" & "SourceDB=o:\database;" & "SourceType=DBF;" & _
        "Exclusive=No;" & "BackgroundFetch=Yes;" & _
        "Collate=Machine;" & "Null=Yes;" & "Deleted=Yes;"
        
        Set Order = New ADODB.Recordset
    
            StartDate = Format(txt_From, "yyyy-mm-dd")
            EndDate = Format(txt_To, "yyyy-mm-dd")
            DoEvents
            Order.Open "SELECT product,`desc`,ordedate,weight From detail " & _
            "WHERE ordedate BETWEEN { d '" & StartDate & "' } AND { d '" & EndDate & "' }" & _
            "ORDER BY ordedate", OrderStr, adOpenStatic
            
           
            Label1.Caption = "Number of Records Found = " & Order.RecordCount
            Order.Close
    Again Thank You Both

Similar Threads

  1. Forming SQL string for FIND in VB
    By Bhavesh in forum VB Classic
    Replies: 1
    Last Post: 02-12-2002, 12:08 PM
  2. They created J#, why couldn't they do VB#?
    By Thomas Eyde in forum .NET
    Replies: 290
    Last Post: 12-22-2001, 03:13 PM
  3. Access & SQL Server
    By David Jones in forum Database
    Replies: 0
    Last Post: 08-31-2001, 01:22 PM
  4. Comparing NULL values (VB, ADO, SQL Server)
    By Heather in forum VB Classic
    Replies: 1
    Last Post: 02-08-2001, 04:55 PM
  5. Replies: 84
    Last Post: 01-29-2001, 02:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center