Solved

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

Posted on 2006-11-10
10
188 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
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 discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now