Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Search between dates - not working

Results 1 to 2 of 2
Share This Thread →
  1. #1
    missy786 is offline Registered User
    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0

    Search between dates - not working

    Hello,

    I can not seem to get the date range clause to work. Currently it is outputting blank display on the client-side.

    Code:
    <?xml version = "1.0"?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
    <html>
    
    <head>
    <title> table Fail </title>
    <link rel="stylesheet" href="css/style.css" type="text/css" media="all" />
    <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
      <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
      <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
      
      <script type="text/javascript">
           $(function() {
                   $("#startdate").datepicker({ dateFormat: "dd-mm-yy" }).val()
           });
    
               $(function() {
                   $("#enddate").datepicker({ dateFormat: "dd-mm-yy" }).val()
           });
    
       </script> 
    
    
    </head>
    
    <body>
    
    <SCRIPT type="text/javascript">
    
    function tableHighlightRow() {
      if (document.getElementById && document.createTextNode) {
        var tables=document.getElementsByTagName('table');
        for (var i=0;i<tables.length;i++)
        {
          if(tables[i].className=='hilite') {
            var trs=tables[i].getElementsByTagName('tr');
            for(var j=0;j<trs.length;j++)
    {
              if(trs[j].parentNode.nodeName=='TBODY') {
                trs[j].onmouseover=function(){this.className='hilight';return false}
                trs[j].onmouseout=function(){this.className='';return false}
              }
            }
          }
        }
      }
    }
    window.onload=function(){tableHighlightRow();}
    
    </SCRIPT>
    
    <SCRIPT type="text/javascript">
    function butCheckForm_onclick()
    {
       var myForm = document.form1;
       
       
       if (myForm.txtName.value == "")
       {
          alert("Please enter into the choosen search criteria!");
         }
    }
    </SCRIPT>
    
    
    
    <div id="pagewrap">
    <div id="menu">
    <div id="navcontainer">
    
    <ul>
    <FORM NAME=form1 method="get">    
    
    <li>
    
    Cusip/Isin:<INPUT TYPE="text" NAME=txtName name="searchInput" size="10" onchange="txtName_onchange()">
    <INPUT TYPE="submit" VALUE="Search" name="Submit" onclick="butCheckForm_onclick()"> 
    
    </li>
    
    <li>
    Bond's Name<em>*</em><INPUT TYPE="text" NAME=txtName name="searchTerm" size="10" onchange="txtName_onchange()">
    <INPUT TYPE="submit" VALUE="Search" name="Submit" onclick="butCheckForm_onclick()"> 
    </li>
    
    <li>
    Start Date: <input type="text" id="startdate" size="10"/>    
    </li>
    
    <li>
    End Date: <input type="text" id="enddate" size="10"/>   
    </li>
    
    <li>
    Deal_type: <input type="text" size="10"/>  
    </li>
    </ul>
    		
    </FORM>
    </div>
    </div>
    
    <div id="content">
    <% 
    'open the connection
    Dim Connect, myRecordSet
    Set Connect = Server.CreateObject("ADODB.Connection")
    Connect.Open = "Provider=SQLOLEDB; Data Source=#####-PC\SQLEXPRESS; Initial Catalog=test; User ID=##; Password=####;"
    %>
    
    <%
    'collect the form input
    searchInput = Request.QueryString("searchInput")
    strdate1 = Request.Form("startdate")
    strdate2 = Request.Form("enddate")
    
    Set myRecordSet = Connect.Execute ("SELECT Deal_type, Price_type, COUNT(Deal_type)  AS 'Total_Assets' FROM [dbo].[all] WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo') and  date between '" & startdate & "' and '" & enddate & "' GROUP BY Deal_type,Price_type order by Deal_type")
    
    'display the results
    if myRecordSet.EOF then
    response.write("You searched for: " & searchInput & "<br>")
    response.write("A match was not found.<br>Sorry try again.")
    else
    response.write("You searched for: " & searchInput & "<br>")
    response.write("Records were found!<br>The matches are:<br>")
    %>
    
    <table class="hilite" id="highlight">
    <thead>
    
    <tr><th>Deal_type</th><th>Price_type</th><th>Asset_class</th></tr>
    </thead>
    
       <% do until myRecordSet.EOF %>
     <tr>
       <td><%response.write myRecordSet("deal_type") %> </td>
       <td><%response.write myRecordSet("price_type") %> </td>
       <td><%response.write myRecordSet("Total_Assets") %> </td>
     </tr>
    
    <%
       myRecordSet.MoveNext
       loop
    end if
    %>
    
    </table>
    
    <%
    
    'close the connection and recordset objects to free up resources
    myRecordset.Close
    Set myRecordset=nothing
    Connect.Close
    Set Connect=nothing
    %>
    
    </div>
    </body>
    </div>
    
    </html>


    Any help guidance with the code, would be really much appreciated.

  2. #2
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    Rep Power
    278
    try
    Code:
    Set myRecordSet = Connect.Execute ("SELECT Deal_type, Price_type, COUNT(Deal_type)  AS 'Total_Assets' FROM [dbo].[all] WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo') and  [date] between '" & startdate & "' and '" & enddate & "' GROUP BY Deal_type,Price_type order by Deal_type")
    date is reserved word if you use as field name it must be in []
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. Working with Dates in WMI
    By DevAdmin in forum Development Articles
    Replies: 1
    Last Post: March 5th, 2014, 10:38 AM
  2. Several issues working with dates
    By Pauld in forum Microsoft Access Help
    Replies: 8
    Last Post: March 22nd, 2010, 02:05 AM
  3. Working With Dates (Week)
    By Khrysller in forum ASP Development
    Replies: 1
    Last Post: June 11th, 2007, 02:48 PM
  4. Working with dates in SQL strings
    By Pluto in forum Microsoft SQL Server
    Replies: 8
    Last Post: March 13th, 2005, 01:28 PM
  5. Having Trouble with working with dates
    By buttons336 in forum SQL Development
    Replies: 0
    Last Post: February 13th, 2004, 08:06 AM

ASP Free Advertisers and Affiliates