Query Date Range in ASP Classic

Varg_88 · December 16th, 2013, 08:02 PM

Hello all!

I`m using the following code in order to filter some results with a date range. It just brings me up some wrong results... like the filters I`m using are wrong.

This is the code:

-------------------------

Code:

<html>
 <head>
  <title>Classic ASP Search Access Database Demo</title>
  <style>
   body {font-family:arial;}
   table.search {width:200px;}
   table.recordset {width:600px;padding:0px;}
   div.recordset {width:618px;height:300px;overflow-y:scroll;border:1px solid black;}
   th.recordset {color:white;background:black;padding:2px;}
   .search {width:100px;}
   .column-1 {width:200px;text-align:left;}
   .column-2 {width:200px;text-align:left;}
   .column-3 {width:200px;text-align:left;}
   .eof {color:red;padding:4px;}
   td.recordset {border-bottom: 1px solid silver;}
  </style>
 </head>
 <body>

<%

                   
  sSearch1 = Request.Form("search1")
  sSearch2 = Request.Form("search2")

  
%>
  
  <form action="1emvolio.asp" method="post">
   <table class="search">
    <tr>from
     <td class="search"><input name="search1" type="date" value="<%=sSearch1%>"></td>
     
    </tr>
    
        <tr>to
     <td class="search"><input name="search2" type="date" value="<%=sSearch2%>"></td>
     <td class="search"><input type="submit" value="Search">
    </tr>
   </table>
  </form>

  <h3>Search Results</h3>
  
  <div class="recordset"> 
   <table class="recordset" cellspacing="0">
    <tr>
     <th class="column-1 recordset">Description 1</th>
     <th class="column-2 recordset">Description 2</th>
     <th class="column-3 recordset">Description 3</th>
    </tr>

<%


  If sSearch1  <> "" Then

  


    sSearch1 = Trim(Replace(sSearch1,"'","''"))
    
  

   Set oConnection = Server.CreateObject("ADODB.Connection")

   oConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb")


   Set oRecordset = oConnection.Execute("SELECT * " & _
                                        "FROM emvolio " & _
                                        "WHERE next_date between '" & sSearch1 & "' and '" & sSearch2  "'")


                       
   If not oRecordset.EOF Then
    Do While not oRecordset.EOF
 %>

    <tr>
     <td class="column-1 recordset"><%=oRecordset("chip")%></td>   
     <td class="column-2 recordset"><%=oRecordset("emvolio")%></td>
     <td class="column-3 recordset"><%=oRecordset("next_date")%></td>
    </tr>
  
<%
    oRecordset.MoveNext
    Loop
   Else
%>

   <tr>
    <td class="eof" colspan="3">No Records</td>
   </tr>

<%

end if
end if
response.write(sSearch2)
response.write(sSearch1)
   
%>

   </table>
  </div>
 </body>
</html>

--------------------------

I believe that the problem is that the field "next_date" is not a DATE field so it just reads every first number of dd/mm/yyyy.

Please Help

Imar · December 17th, 2013, 03:29 AM

Hi there,

Quote:

I believe that the problem is that the field "next_date" is not a DATE field

The first step then is to figure out what the type of the field is, and how the data is stored. You could change the type to a date, or, if it's a string type, you need to know in which format is stored so you can update your query for that. If the data is stored in a sortable format (e.g. yyyy/mm/dd) you can still use BETWEEN or < and >.

Some other tips:

1. Your code is open for SQL Injection. With the current code, you can enter enter bad SQL in search1 and whipe out your database. Search Google for "SQL Injection" to learn more about it, and how to prevent it (use parameterized queries for example).

2. When posting a question about SQL, try to clean up your example as much as possible. All the CSS and table based HTML isn't needed to understand the problem, but makes it much harder to understand the issue and see the relevant code.

3. Microsoft Access uses # instead of ' to delimit dates in your SQL statements: http://stackoverflow.com/questions/3...-between-dates

Hope this helps,

Imar

Varg_88 · December 17th, 2013, 03:41 AM

ok... you are right that it`s hard to read my code. I just paste the whole .asp file for better inspection.

My problem is in those few lines

Code:

 Set oRecordset = oConnection.Execute("SELECT * " & _              "FROM emvolio " & _               "WHERE next_date between '" & sSearch1 & "' and '" & sSearch2  "'")

so... I have a form with 2 "date fields" (ssearch1, ssearch2) where I put 2 dates (e.g 2/2/2010, 3/3/2012).
On my access db I have to use the "text type" for the "next_date" field because when I use "date type" I get an error for my .asp page.

I`ve checked the records in my db and they are fine (e.g 12/2/2011....).

So if i make a query about "next_date>=1/1/2010 and < 3/1/2010" among the results I`ll get is also the record "12/2/2011" because from what I can get it only reads the first number of the whole date.

I will try the "#" and I`ll be back to post the results. thank you for your reply!

Varg_88 · December 17th, 2013, 03:49 AM

Code:

  Set oRecordset = oConnection.Execute("SELECT * " & _
                                        "FROM emvolio " & _
                                        "WHERE next_date between #" & sSearch1 & "# and #" & sSearch2 & "#")

still getting wrong results... :(
the good thing is that using the "#" symbol I managed to change the type of the "next_date field" to "date".

it`s working much better but I think that the problem now is that there is some "misunderstanding" on the date format. dd/mm/yyyy and mm/dd/yyyy is messing up my results.
I feel I`m near to the solution...

Imar · December 17th, 2013, 07:34 AM

I would print out the SQL statement that you're executing and then run it again Access to see what the results are there.

You may be having issues with date conversions where ASP uses a different date format than you think (e.g. mm/dd/yyyy instead of dd/mm/yyyy). A solution that has always work for me is to use the format yyy/mm/dd. ASP has various ways to build up the date in the proper format. The link I posted has some more info and also Google should give you lots of useful results.

Cheers,

Imar

Varg_88 · December 25th, 2013, 12:32 AM

you where quite helpful.

Thanks! My problem is solved!

rstelma · December 26th, 2013, 12:53 AM

How did you solve the problem? What did you change to make it work?

Thank you

Varg_88 · December 27th, 2013, 10:34 AM

First I had to change the field type in my db. Then I had to use the "#" sign in my code and the last thing was that I just had to deal with the fact that IIS server reads the date values like mm/dd/yyyy and that Access db saves those values like dd/mm/yyyy.

:)

rstelma · December 27th, 2013, 01:23 PM

OK... great.. thank you