Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Recordset filltered by dates does not work in Firefox (works fine in IE)

Posted on 2006-11-10
10
Medium Priority
?
196 Views
Last Modified: 2008-02-01

I'm creating a on-line reservation system for a timeshare organization using Dreamweaver 8 - JavaScript, MS SQL. On the first page I have InDate and OutDate parameters which are being passed to a second page where they are used for filetering a recordset.
everything works inIe but in Firefox I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

The code generated by Dreamweaver is the following:

<%
var DAvail = Server.CreateObject("ADODB.Recordset");
DAvail.ActiveConnection = MM_tswdata_STRING;
DAvail.Source = "SELECT DateAvailable, RoomType, Block, Segment, NumberAvailable  FROM dbo.t_TypeAvailability  WHERE (NumberAvailable > 3) AND  (Block = 'owner') AND (Segment = 'owner use') AND  (RoomType='"+ DAvail__Bdrm.replace(/'/g, "''") + "') AND DateAvailable >= '"+ DAvail__Begin.replace(/'/g, "''") + "' AND DateAvailable<'"+ DAvail__End.replace(/'/g, "''") + "'";
DAvail.CursorType = 0;
DAvail.CursorLocation = 2;
DAvail.LockType = 1;
DAvail.Open();
var DAvail_numRows = 0;
%>

Any suggestions will be appreciated.
0
Comment
Question by:ChristieLodge
  • 5
  • 3
  • 2
10 Comments
 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 17920032
What is the datatype of the field DateAvailable ? Datetime or varchar? If it is date use this,


DAvail.Source = "SELECT DateAvailable, RoomType, Block, Segment, NumberAvailable  FROM dbo.t_TypeAvailability  WHERE (NumberAvailable > 3) AND  (Block = 'owner') AND (Segment = 'owner use') AND  (RoomType='"+ DAvail__Bdrm.replace(/'/g, "''") + "') AND DateAvailable >= '"+ cdate(DAvail__Begin.replace(/'/g, "''")) + "' AND DateAvailable<'"+ cdate(DAvail__End.replace(/'/g, "''")) + "'";
0
 

Author Comment

by:ChristieLodge
ID: 17933388
Dateavailable is datetime.
I tried using the cdate but got an error:

Microsoft JScript runtime error '800a138f'

Object expected
0
 

Author Comment

by:ChristieLodge
ID: 17933467
Here is the code again:
<%
var DAvail__Bdrm = "1";
if (String(Request.Form("bdrm")) != "undefined" && 
    String(Request.Form("bdrm")) != "") {
  DAvail__Bdrm = String(Request.Form("bdrm"));
}
%>
<%
var DAvail__Begin = "1";
if (String(Request.Form("InDate")) != "undefined" && 
    String(Request.Form("InDate")) != "") {
  DAvail__Begin = String(Request.Form("InDate"));
}
%>
<%
var DAvail__End = "1";
if (String(Request.Form("OutDate")) != "undefined" && 
    String(Request.Form("OutDate")) != "") {
  DAvail__End = String(Request.Form("OutDate"));
}
%>
<%
Session.LCID = 1033
%>
<%
var DAvail = Server.CreateObject("ADODB.Recordset");
DAvail.ActiveConnection = MM_tswdata_STRING;
DAvail.Source = "SELECT DateAvailable, RoomType, Block, Segment, NumberAvailable  FROM dbo.t_TypeAvailability  WHERE (NumberAvailable > 3) AND  (Block = 'owner') AND (Segment = 'owner use') AND  (RoomType='"+ DAvail__Bdrm.replace(/'/g, "''") + "') AND DateAvailable >= '"+ DAvail__Begin.replace(/'/g, "''") + "' AND DateAvailable<'"+ DAvail__End.replace(/'/g, "''") + "'";
DAvail.CursorType = 0;
DAvail.CursorLocation = 2;
DAvail.LockType = 1;
DAvail.Open();
var DAvail_numRows = 0;
%>
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 16

Expert Comment

by:CWS (haripriya)
ID: 17936026
Hello ChristieLodge,

I am sorry to say that I am not much familiar with the combination "JavaScript and MSSQL database". So, please put pointer questions under the Topic Areas 'MS SQL' and 'JAVASCRIPT'. You will get more experts answering to your questions and it will be solved very easily.

Anyway, my last try : Declare the variables DAvail_Begin and DAvail_End as date variables.

Good Luck
0
 
LVL 10

Expert Comment

by:Dennis Maeder
ID: 17943316
Christie,
That is most peculiar because that is not a browser message, so the error message ( and the error) are generated by the DB server.
The code means "Data type mismatch in criteria expression" i.e. the data you are passing is not in datetime format.
What line is the point of error?
I suppose data gets passed from the first form to the server and you get a broken response?
Is your first page a form? It should pass dates which look like 'yyyy-mm-dd' and never be empty '' - that would also cause this error.
Do you validate your input?
I guess what I'm saying is look at the way the message is passed rather than the DB/ whatever-that-language-is interaction.
D
0
 

Author Comment

by:ChristieLodge
ID: 17950062
The thing is this code works fine in IE - the problem appears when I view the pages in Firefox.
I can see the dates are being passed correctly in IE. But they are being passed in mm-dd-yyyy format and this is the format in the database too... do you think this would be a problem for Firefox?
0
 
LVL 10

Expert Comment

by:Dennis Maeder
ID: 17950570
Christie,
Are they passed as a string or a datetime object?
Can you please show the browser view of the script - all that stuff is server-side ECMA.
view source in FF and copy the form / script stuff of your page 1.

Have you tried flipping them into yyyy-mm-dd format to see what it does?
ISO8601 is the sortable/universal DB standard.
See http://msdn2.microsoft.com/en-us/library/az4se3k1.aspx
D
0
 

Author Comment

by:ChristieLodge
ID: 17951688
Ok, Here is the form source view in FF:
  <!-- InstanceBeginEditable name="MainBody" -->
  <table width="750" height="613"   border="0" cellpadding="0" cellspacing="0" bordercolor="#B17C50" background="oimages/OMainTableBG.jpg" bgcolor="#FFFFEC">
    <tr>
      <td width="764" height="613" align="center" valign="top"><table width="730">

          <tr>
            <td height="17"><div align="left"><span class="style58">Today is</span>
              <script>

/*Current date script credit:
JavaScript Kit (www.javascriptkit.com)
*/

var mydate=new Date()
var year=mydate.getYear()
if (year < 1000)
year+=1900
var day=mydate.getDay()
var month=mydate.getMonth()+1
if (month<10)
month="0"+month
var daym=mydate.getDate()
if (daym<10)
daym="0"+daym
document.write("<small><font color='660000' face='Arial'><b>"+month+"/"+daym+"/"+year+"</b></font></small>")
                </script>
            </div></td>
            <td><div align="right" class="style58"><a href="/The Owners Link/o_makeres.asp?MM_Logoutnow=1">Logout</a></div></td>
          </tr>
        </table>

      </p>

       
       
          <table width="727" border="1" align="center">
           
            <tr>
              <td height="84" align="center" bordercolor="#EEE0B3"><div align="left"> Dear .......,<br>
                you can review the available usage and week rages for the year you have selected bellow. To proceed with your reservation request please select a Check In Date and Bedroom type under Check availability. </div></td>
              <td width="314" colspan="2" rowspan="3" align="right"><div align="center">
                <form name="form1" method="post" action="o_resresult.asp">

                 
                    <table width="300" border="1" bordercolor="#660000" background="images/TableBG_linear.jpg">
                      <tr>
                        <td colspan="2"><img src="oimages/o-checkavail.gif" width="308" height="26"></td>
                        </tr>
                      <tr>
                        <td colspan="2" bordercolor="#FFFFEB"><strong>Step 2 : Select week</strong> </td>
                        </tr>
                      <tr>

                        <td width="143" bordercolor="#FFFFEB">&nbsp;</td>
                          <td width="159" bordercolor="#FFFFEB"><div align="left"></div></td>
                        </tr>
                      <tr>
                        <td bordercolor="#660000"><div align="right">Check In
                         
                          Date*:</div></td>
                        <td bordercolor="#660000"><div align="left">
                          <select name="InDate" id="select" onChange="MM_callJS('doCalcDays()')">
                            <option value="Select date" >Select Date</option>

                           
                            <option value="12/16/2006"  >12/16/2006</option>
                           
                          </select>
                        </div></td>
                      </tr>
                      <tr>
                        <td bordercolor="#660000"><div align="right">Check Out* :</div></td>
                          <td bordercolor="#660000"><div align="left">
                              <input name="OutDate" type="text" id="OutDate" size="10">

                          </div></td>
                        </tr>
                      <tr>
                        <td bordercolor="#660000"><div align="right">Bedroom Type:</div></td>
                          <td bordercolor="#660000"><div align="left">
                              <select name="bdrm" id="select2">
                                <option value="1 Bdrm">1 Bdrm</option>
                                <option value="3 Bdrm">3 Bdrm</option>

                              </select>
                          </div></td>
                        </tr>
                      <tr>
                        <td bordercolor="#660000">&nbsp;</td>
                          <td bordercolor="#660000">&nbsp;</td>
                        </tr>
                      <tr>
                        <td bordercolor="#660000">&nbsp;</td>

                          <td bordercolor="#660000"><div align="left"><a href="o_resresult.asp?">
                              <input name="Submit2" type="submit" id="Submit2"  value="Check Availability">
                          </a></div></td>
                        </tr>
                      <tr>
                        <td colspan="2"><p align="left"><span class="style59">The Check Availability system displays  the weeks available for your season type. <br>Check In Date indicates the begining of the week (which is a Saturday). Check Out Date fills in automaticaly after the selection for Check Out Date is made </span></p></td>
                        </tr>
                    </table>

                   
<SCRIPT LANGUAGE="JavaScript"><!--
function doCalcDays() {
    // parse s for month, day, year
    var dateArray = document.form1.InDate.value.split('/');
    sdate = new Date(dateArray[2],dateArray[0]-1,dateArray[1]);

// figure out day, month, year 7 days ago
    var odate = new Date(sdate.getTime() + (7 * 86400000));

// return value
    date_7_days =(odate.getMonth()+1) + '/' + odate.getDate() + '/' + odate.getFullYear();
      document.form1.OutDate.value = date_7_days;
      
}


//--></SCRIPT>
                 

</form>

The In Date selections come from the Database and the Out Date is calculated based on In Date I add 7 days to In Date and this is my Out Date.

When I submit the page I get the error for the string date conversion.

Parameters are passed as strings I think..
I use form post method and
<%= Request.Form("InDate") %>
<%= Request.Form("OutDate") %>

I'll check out the link you pointed.
Thanks D
0
 
LVL 10

Accepted Solution

by:
Dennis Maeder earned 2000 total points
ID: 17953268
Thanks for the test code - it really helps.
Under IE you generate a request that looks so:
http://localhost/o_resresult.asp?InDate=12%2F16%2F2006&OutDate=12%2F23%2F2006&bdrm=1+Bdrm&Submit2=Check+Availability
Under FF:
http://localhost/o_resresult.asp?
that is the form data is not posting as expected.
What could be the matter?
Aaaah! I see.
                          <td bordercolor="#660000"><div align="left"><a href="o_resresult.asp?">
                              <input name="Submit2" type="submit" id="Submit2"  value="Check Availability">
                          </a></div></td>
You've got your submit button wrapped in an anchor tag which correctly takes precedence in FF unlike brain-dead IE.
FIX: remove the anchor to produce:
                          <td bordercolor="#660000"><div align="left">
                              <input name="Submit2" type="submit" id="Submit2"  value="Check Availability">
                          </div></td>
I've tested and a correct text formatted date is passed in both browsers.
D
0
 

Author Comment

by:ChristieLodge
ID: 17957059
Thank you so much Dennis,
This fixed my problem I didn't even think about the anchor.
You're the best.
CL
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question