Solved

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

Posted on 2006-11-10
10
190 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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 …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

15 Experts available now in Live!

Get 1:1 Help Now