Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 16th, 2013, 08:02 PM
Friend of Wrox
 
Join Date: May 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query Date Range in ASP Classic

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

Last edited by Varg_88; December 17th, 2013 at 03:06 AM.. Reason: [code]
 
Old December 17th, 2013, 03:29 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old December 17th, 2013, 03:41 AM
Friend of Wrox
 
Join Date: May 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!
 
Old December 17th, 2013, 03:49 AM
Friend of Wrox
 
Join Date: May 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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...

Last edited by Varg_88; December 17th, 2013 at 04:01 AM..
 
Old December 17th, 2013, 07:34 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old December 25th, 2013, 12:32 AM
Friend of Wrox
 
Join Date: May 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Default

you where quite helpful.

Thanks! My problem is solved!
 
Old December 26th, 2013, 12:53 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Thank you
 
Old December 27th, 2013, 10:34 AM
Friend of Wrox
 
Join Date: May 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

:)
 
Old December 27th, 2013, 01:23 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

OK... great.. thank you





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to find a date range between another date rang tayvonne Access 2 August 3rd, 2006 09:50 AM
Date Range Flipflop SQL Language 6 November 25th, 2005 08:36 AM
date range query killroy Access 2 August 17th, 2005 02:14 PM
Date Range mrideout BOOK: Beginning ASP.NET 1.0 0 January 2nd, 2005 07:18 PM
Query with a date range... PLEASE HELP! tlcable7 Access ASP 2 October 1st, 2003 03:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.