DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Access SQL query in ASP page

  1. #1
    Terry Guest

    Access SQL query in ASP page


    I am using an access backend and need to query by current date. For example
    - late items are items where the due date is >= today (current date). My
    sql statement looks like this

    SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"

    I know that Access has funny little caveats like adding # to the start and
    end of dates but I still cannot seem to get this to work. Can anyone give
    me a hint?

    Thanks

    I was also thinking that my problem may have something to do with the way
    I am connecting and the type of query I am using - so here's my code if anyone
    is interested

    <%
    Dim Conn
    Dim rs

    Set Conn = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateObject("ADODB.Recordset")

    strDatabasePath = Server.MapPath("tracking.mdb")
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath
    & ";"

    SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"

    rs.Open SQL, Conn

    rs.MoveFirst
    howManyFields = rs.Fields.Count
    Response.Write sql
    %>



  2. #2
    garetjax Guest

    Re: Access SQL query in ASP page


    Well, I too once went through this and had the same issue...I figured out
    that what your posting is considered a text element whereas the Field value
    in the database is a Date/Time Element.

    Requiring: #DATE#
    The Comparison that your doing must also, contain this same format if I'm
    not mistaken. But, in my dev I also found that the field I was using contained
    Date/Time Stamp rather than a simple date. So, I had to part out what I
    wanted to match... ie

    SELECT * FROM tblMain WHERE Month(zDATE)='" & Month(reqDate) & "' AND WHERE
    YEAR(zDate)='" & Year(reqDate) & "'"

    Something like that anyways.


    "Terry" <snakbrat@yahoo.com> wrote:
    >
    >I am using an access backend and need to query by current date. For example
    >- late items are items where the due date is >= today (current date). My
    >sql statement looks like this
    >
    >SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"
    >
    >I know that Access has funny little caveats like adding # to the start and
    >end of dates but I still cannot seem to get this to work. Can anyone give
    >me a hint?
    >
    >Thanks
    >
    >I was also thinking that my problem may have something to do with the way
    >I am connecting and the type of query I am using - so here's my code if

    anyone
    >is interested
    >
    ><%
    >Dim Conn
    >Dim rs
    >
    >Set Conn = Server.CreateObject("ADODB.Connection")
    >Set rs = Server.CreateObject("ADODB.Recordset")
    >
    >strDatabasePath = Server.MapPath("tracking.mdb")
    >Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath
    >& ";"
    >
    >SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"
    >
    >rs.Open SQL, Conn
    >
    >rs.MoveFirst
    >howManyFields = rs.Fields.Count
    >Response.Write sql
    >%>
    >
    >



  3. #3
    Q*bert Guest

    Re: Access SQL query in ASP page


    Just curious... Why do you have to pass in the date if SQL provides a date
    function?
    SQL = "SELECT * FROM database where duedue >= Date() " --> MSAccess
    SQL = "SELECT * FROM database where duedue >= GetDate() " -->SQL SEVER

    "garetjax" <fusion_box@hotmail.com> wrote:
    >
    >Well, I too once went through this and had the same issue...I figured out
    >that what your posting is considered a text element whereas the Field value
    > in the database is a Date/Time Element.
    >
    >Requiring: #DATE#
    >The Comparison that your doing must also, contain this same format if I'm
    >not mistaken. But, in my dev I also found that the field I was using contained
    >Date/Time Stamp rather than a simple date. So, I had to part out what I
    >wanted to match... ie
    >
    >SELECT * FROM tblMain WHERE Month(zDATE)='" & Month(reqDate) & "' AND WHERE
    >YEAR(zDate)='" & Year(reqDate) & "'"
    >
    >Something like that anyways.
    >
    >
    >"Terry" <snakbrat@yahoo.com> wrote:
    >>
    >>I am using an access backend and need to query by current date. For example
    >>- late items are items where the due date is >= today (current date).

    My
    >>sql statement looks like this
    >>
    >>SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"
    >>
    >>I know that Access has funny little caveats like adding # to the start

    and
    >>end of dates but I still cannot seem to get this to work. Can anyone give
    >>me a hint?
    >>
    >>Thanks
    >>
    >>I was also thinking that my problem may have something to do with the way
    >>I am connecting and the type of query I am using - so here's my code if

    >anyone
    >>is interested
    >>
    >><%
    >>Dim Conn
    >>Dim rs
    >>
    >>Set Conn = Server.CreateObject("ADODB.Connection")
    >>Set rs = Server.CreateObject("ADODB.Recordset")
    >>
    >>strDatabasePath = Server.MapPath("tracking.mdb")
    >>Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabasePath
    >>& ";"
    >>
    >>SQL = "SELECT * FROM database where duedue >= '" & Date() & "'"
    >>
    >>rs.Open SQL, Conn
    >>
    >>rs.MoveFirst
    >>howManyFields = rs.Fields.Count
    >>Response.Write 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
  •  
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