#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,585
    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

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

IMN logo majestic logo threadwatch logo seochat tools logo