none
Query By Date - VFP OLE DB Provider

    Question

  • Hi

    I am trying to query a Visual FoxPro database from a .aspx web page. I returned data using the following WHERE statement in Example 1 but when I add the two date parameters in Example 2 no data displays. I tested the query in a third-party FoxPro application and returned data.

    Example 1:

    WHERE
      p.regdate>Date()-3650 AND
      UPPER (m.test) LIKE 'F' AND
      m.status = 0

    Example 2:

      p.regdate>Date()-3650 AND
      UPPER (m.test) LIKE "F" AND
      (TTOD (m.birthdate) > {01/01/1990} AND
      TTOD (m.birthdate) < {31/12/1991}) AND
      m.status = 0

    Is this a problem with the format of my WHERE statement?

    Wednesday, June 9, 2010 3:37 AM

Answers

  • 1. What indexes you have in your table? If you have index on Birthdate and not on ttod(BirthDay) and BirthDay is a datetime column, then

    and Birthday >=datetime(1990,1,1) and Birthday <=datetime(1991,12,31)

    Also, it may be a better idea to use some different alias than m to not confuse with memory variables.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by RobNZ Wednesday, June 9, 2010 9:08 PM
    Wednesday, June 9, 2010 4:14 AM
    Moderator

All replies

  • 1. What indexes you have in your table? If you have index on Birthdate and not on ttod(BirthDay) and BirthDay is a datetime column, then

    and Birthday >=datetime(1990,1,1) and Birthday <=datetime(1991,12,31)

    Also, it may be a better idea to use some different alias than m to not confuse with memory variables.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by RobNZ Wednesday, June 9, 2010 9:08 PM
    Wednesday, June 9, 2010 4:14 AM
    Moderator
  • Hi Rob,
     
    a) are you sure that a field called BirthDATE is actually a DateTime field? In a normal VFP database I would expect a pure Date fieldtype.
     
    b) Your notation of the date values is depending on the regional settings. Interestingly you are using the american "/" as date-mark, but a european  Day-Month sequence. You better define the dates in an unambigious way like:
     
    WHERE (birthdate > {^1990-01-01} AND birthdate < {^1991-12-31})
    or even better:
    WHERE BETWEEN(Birthdate, {^1990-01-01}, {^1991-12-31})
     
    c) I noticed that you're adressing the fields in your WHEREclause with the "m." alias. This redirects to memory variables, thus if those fields are in your table they would never actually get queried.
     
     

    wOOdy
    Microsoft Visual FoxPro Technology Advisor
    Microsoft "Most Valuable Professional" from 1996 to 2009
    Visit my XING profile! Don't know what XING is?

    *´¨)
    ¸.·´¸.·*´¨) ¸.·*¨)
    (¸.·´. (¸.·` *
    .·`.Visual FoxPro: It's magic !
    (¸.·``··*


     

    Wednesday, June 9, 2010 7:08 AM
  • Just to clarify the local alias "m": When "m" is used as a local alias in SQL command then it has precedence to any memory variable and it always represents the table column. Of course, such SQL command cannot use "m." prefix for constants stored in memory variables but you still may use memory variables without prefix...

    A little bit confusing for classic FoxPro dinosaurus (like me) but memory variables are almost unusable in VFP OLE DB provider so "m" used as local alias is OK.

    Wednesday, June 9, 2010 8:39 AM
    Moderator
  • From an ASP.Net application you should call your queries with parameters to be sure that you are using the right syntax. ie:

    string strConn = @"Provider=VFPOLEDB;Data source=C:\myFolder\Mydata.dbc;";
    
    string strQuery = "select * from myTable where birthdate between ? and ?";
    	
    OleDbConnection cn = new OleDbConnection(strConn);
    cn.Open();
    OleDbCommand cmd = new OleDbCommand(strQuery,cn);
    cmd.Parameters.AddWithValue( "startDate", new DateTime(1990,1,2) );
    cmd.Parameters.AddWithValue( "endDate", new DateTime(1991,12,30) );
      
    OleDbDataReader rdr = cmd.ExecuteReader();
    DataTable tbl = new DataTable();
    tbl.Load(rdr);
    cn.Close();
    
    
    PS: As Pavel said already m. is a VFP reserved alias for memory.

    Wednesday, June 9, 2010 4:55 PM
  • excellent !

    this worked for my isue,  >>WHERE BETWEEN(Birthdate, {^1990-01-01}, {^1991-12-31})

    Thank for your aportation

    Friday, April 12, 2019 12:16 AM
  • excellent !

    this worked for my isue,  >>WHERE BETWEEN(Birthdate, {^1990-01-01}, {^1991-12-31})

    Thank for your aportation

    But you wouldn't want to hardcode values like that in the query. Instead use parameters. 
    Saturday, April 13, 2019 11:46 AM