Solved

Date Error

Posted on 2002-07-16
19
216 Views
Last Modified: 2012-06-21
Hello.  We are having trouble finding an error we get when running an asp page.  The error is:

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

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

/csinetbeta2/queries/backhauls/pages/backhaul_update.asp, line 25

I'm sure it is probably something silly but we can't see it, so I'm hoping someone else will be able to catch it.  The code is as follows:

<%
     Response.Expires = 0
     If Request.Cookies("csinet_login")("userid") = "" then
          Response.Redirect "../../../menu/pages/login.asp"
     End If

%>
<META HTTP-EQUIV="Cache-Control" CONTENT="no-cache">

<html>
<%
  if request.querystring ("dept") <> "" then
    dim cn, cnStr, dept, po, arrival, user

    dept = request.querystring ("dept")
    po = request.querystring ("po")
    arrival = request.querystring ("arrival")
    user = request.querystring ("user")

    cnStr="Provider=MSDASQL;Driver={SQL Server};Server=172.16.96.100;Database=csi_reporting;UID=gagnon;PWD=alandgene;"
    set cn=server.createobject("ADODB.Connection")

    cn.open cnStr

    cn.execute "update tblbackhauls set unload_user = '" & user & "' , unload_time_stamp = '" & FormatDateTime(Now) _
     & "' , unload_date = '" & date() & "' where dept_name = '" & dept & "' and po_number = '" & po & "' and arrive_date = '" _
     & arrival & "';"
    set cn=nothing
  end if
 
%>
</html>

Thanks

nnaxor
0
Comment
Question by:nnaxor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +2
19 Comments
 
LVL 18

Accepted Solution

by:
mgfranz earned 50 total points
ID: 7157260
Try this;

sSql = "update tblbackhauls set unload_user = '" & user & "' , unload_time_stamp = '"# & FormatDateTime(Now) _
    & #"' , unload_date = '"# & date() & #"' where dept_name = '" & dept & "' and po_number = '" & po & "' and arrive_date = '"# & arrival & #"';"

cn.execute(sSql)

And do a Response.Write on the SQL to ensure it is formatted correctly.
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 7157422
I think its the problem with arrival variable. So do some validations before you assign


<%
 if request.querystring ("dept") <> "" then
   dim cn, cnStr, dept, po, arrival, user

   dept = request.querystring ("dept")
   po = request.querystring ("po")
if not isnull(request.querystring ("arrival")) or len(request.querystring ("arrival")) > 0 then
   arrival = formatdatetime(request.querystring ("arrival"),2)
end if
   user = request.querystring ("user")

   cnStr="Provider=MSDASQL;Driver={SQL Server};Server=172.16.96.100;Database=csi_reporting;UID=gagnon;PWD=alandgene;"
   set cn=server.createobject("ADODB.Connection")

   cn.open cnStr
if isdate(arrival) or len(arrival) > 0 then
   cn.execute "update tblbackhauls set unload_user = '" & user & "' , unload_time_stamp = '" & FormatDateTime(Now) _
    & "' , unload_date = '" & date() & "' where dept_name = '" & dept & "' and po_number = '" & po & "' and arrive_date = '" _
    & arrival & "';"
   set cn=nothing
else
response.write "Wrong Date is " & arrival
end if
 end if
 
%>
0
 
LVL 4

Expert Comment

by:mberumen
ID: 7157635
I agree, I've seen this error before when the value passed is not a valid date.   How about using the IsDate function to validate before attempting to update?

<%
    Response.Expires = 0
    If Request.Cookies("csinet_login")("userid") = "" then
         Response.Redirect "../../../menu/pages/login.asp"
    End If

%>
<META HTTP-EQUIV="Cache-Control" CONTENT="no-cache">

<html>
<%
 if request.querystring ("dept") <> "" then
   dim cn, cnStr, dept, po, arrival, user

   dept = request.querystring ("dept")
   po = request.querystring ("po")
   arrival = request.querystring ("arrival")
   user = request.querystring ("user")


if IsDate(arrival) then
   cnStr="Provider=MSDASQL;Driver={SQL Server};Server=172.16.96.100;Database=csi_reporting;UID=gagnon;PWD=alandgene;"
   set cn=server.createobject("ADODB.Connection")

   cn.open cnStr

   cn.execute "update tblbackhauls set unload_user = '" & user & "' , unload_time_stamp = '" & FormatDateTime(Now) _
    & "' , unload_date = '" & date() & "' where dept_name = '" & dept & "' and po_number = '" & po & "' and arrive_date = '" _
    & arrival & "';"
   set cn=nothing
else
Response.write arrival & "Is not a valid date"
end if

 end if
 
%>
</html>

0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 4

Expert Comment

by:mberumen
ID: 7157639
Please disregard my comment,  I did not see jitganguly's previous post
0
 
LVL 1

Author Comment

by:nnaxor
ID: 7157658
Okay we used what was suggested and now what we are getting the actual word Arrival for the variable.  Anybody?
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 7157672
Lets see your latest code. Most probably your querystrign is not workign properly.

Problem here

if not isnull(request.querystring ("arrival")) or len(request.querystring ("arrival")) > 0 then
  arrival = formatdatetime(request.querystring ("arrival"),2)
end if
0
 
LVL 1

Author Comment

by:nnaxor
ID: 7157719
Okay we found out what the error was, it wasn't passing actual values it was just passing variable names, we have it now where it sends the actual data.

Basically we have one asp page that calls another and the code below is the code that sends it to the other page.  

We are now getting a page cannot be found error.  The actual error is:

'http://172.16.96.104/csinetbeta2/queries/backhauls/pages/gackhaul_update.asp?dept=Fdm&po=3213514&arrival=7/16/02&user=dcote'

The code is:<%
      Response.Expires = 0
      If Request.Cookies("csinet_login")("userid") = "" then
            Response.Redirect "../../../menu/pages/login.asp"
      End If

%>
<META HTTP-EQUIV="no-cache">

<html>

<script language="JavaScript">
function forceReload() {
  if(document.images)
    location.replace(location.href + '?' + (new Date()).getTime());
  else
    location.href = location.href + '?' + (new Date()).getTime();
  }
</script>  
<script language="javascript1.2">
  function CloseWindow() {
    top.close();
  }
  function HideButtons() {
    PrintVis.style.visibility = "hidden";
    CloseVis.style.visibility = "hidden";
    print();
    PrintVis.style.visibility = "visible";
    CloseVis.style.visibility = "visible";
  }
  function ConfirmUnload(dept, po, arrival, user) {
    var user_answer;

    user_answer = confirm("Are you sure this backhaul has been unloaded?");
   
    if (user_answer == true) {
      document.frames['mH'].location.replace("'http://172.16.96.104/csinetbeta2/queries/backhauls/pages/backhaul_update.asp?dept=" + dept + "&po=" + po + "&arrival=" + arrival + "&user=" + user + "'");
        alert("'http://172.16.96.104/csinetbeta2/queries/backhauls/pages/backhaul_update.asp?dept=" + dept + "&po=" + po + "&arrival=" + arrival + "&user=" + user + "'");
        //forceReload();
        }
  }
</script>
 

<title>Backhauls To Be Unloaded</title>
<center>
<font face="arial" size="5"><b>Backhauls To Be Unloaded</b></font>
<br>
<font face="arial" size="2" color="slateblue"><b>Highlight the Backhaul That Has Been Unloaded and Click the Mouse Button</b></font>
<br><br>
</center>
<body bgcolor="lavender">
<%
  dim cn, rs, cnStr, fontcolor, cn2

  cnStr="Provider=MSDASQL;Driver={SQL Server};Server=172.16.96.100;Database=csi_reporting;UID=gagnon;PWD=alandgene;"
  set cn=server.createobject("ADODB.Connection")
  set rs=server.createobject("ADODB.Recordset")

  cn.open cnStr
  rs.open "exec csi_get_backhauls;",cn,0,1
%>
<iframe style=="visibility:visible;" id="mH" width="500" height="100"></iframe>
<center>
<table cellspacing="0" cellpadding="0">
<tr>
<th align="left" width="85"><font face=arial size=2>Department</font></th>
<th align="center" width="115"><font face=arial size=2>PO Number</font></th>
<th align="left" width="170"><font face=arial size=2>Vendor</font></th>
<th align="center" width="80"><font face=arial size=2>Trailer</font></th>
<th align="center" width="80"><font face=arial size=2>Arrive Date</font></th>
</tr>
<%
  do until rs.eof
    select case rs("dept_name")
      case "Grocery"
        fontcolor="red"
      case "Fdm"
        fontcolor="blue"
      case "Produce"
        fontcolor="green"
    end select
    response.write "<tr onMouseOver=style.backgroundColor='white';style.cursor='hand'; onMouseOut=style.backgroundColor='lavender'; " _  
      & "onClick=ConfirmUnload('" & rs("dept_name") & "','" & rs("po_number") & "','" & rs("arrive_date") & "','" _
      & Request.Cookies("csinet_login")("userid") & "');>"
    response.write "<td><font face=arial size=2 color=" & fontcolor & "><b>" & rs("dept_name") & "</b></font></td>"
    response.write "<td align=center><font face=arial size=2 color=" & fontcolor & "><b>" & rs("po_number") & "</b></font></td>"
    response.write "<td align=left><font face=arial size=2 color=" & fontcolor & "><b>" & rs("vendor_name") & "</b></font></td>"
    response.write "<td align=center><font face=arial size=2 color=" & fontcolor & "><b>" & rs("trailer_number") & "</b></font></td>"
    response.write "<td align=center><font face=arial size=2 color=" & fontcolor & "><b>" & rs("arrive_date") & "</b></font></td></tr>"
    rs.movenext
  loop

  set rs=nothing
  set cn=nothing
%>
</table>
<br><br>
<table>
<tr><th align="left" width="150"><div id='PrintVis' style='VISIBILITY: visible;'>
<input type='button' name='btnPrint' value='Print' onClick='HideButtons();'></input>
</div>
</th>
<th align="right" width="150"><div id='CloseVis' style='VISIBILITY: visible;'>
<input type="button" value="Close" onClick="CloseWindow();"></input>
</div>
</th>
</tr>
</table>
</center>





</body>
</html>

0
 
LVL 20

Expert Comment

by:jitganguly
ID: 7157736
So, your original questionw as solved and you are getting an entirely different erro.
Logically you shouel close this Q and open another one. But for this file not found error, I would sugegst you to go to your browser and paste the address and see if you get in to the page.

It could be a permission issue also. Make sure IUSR_{yourservername} user has permission to use the pages
0
 
LVL 1

Author Comment

by:nnaxor
ID: 7157878
Thanks much
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 7157889
????
You said
"Okay we found out what the error was, it wasn't passing actual values it was just passing variable names, we have it now where it sends the actual data"
and I answered
"Lets see your latest code. Most probably your querystrign is not workign properly."

I should get points here.
??????????
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 7158102
Any date string has to be wrapped in #..#, like '"# & date() & #"'
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 7158110
Mark,
No offense, but he said "it wasn't passing actual values it was just passing variable names, we have it now where it sends the actual data."
and I told him
"Lets see your latest code. Most probably your querystrign is not workign properly."
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 7158114
Mark,
Thats not true for SQLServer. You are right about Access not SQLServer/Sybase/Oracle.
He is using SQLServer and not access
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 7158123
So Response.Write sSql probably cleared up the problem...

It doesn't matter to me, as long as the solution was found.  :-)
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 7158132
Yes and thats why I had this isnull or len >0 check

<%
if request.querystring ("dept") <> "" then
  dim cn, cnStr, dept, po, arrival, user

  dept = request.querystring ("dept")
  po = request.querystring ("po")
if not isnull(request.querystring ("arrival")) or len(request.querystring ("arrival")) > 0 then
  arrival = formatdatetime(request.querystring ("arrival"),2)
end if
  user = request.querystring ("user")

  cnStr="Provider=MSDASQL;Driver={SQL Server};Server=172.16.96.100;Database=csi_reporting;UID=gagnon;PWD=alandgene;"
  set cn=server.createobject("ADODB.Connection")

  cn.open cnStr
if isdate(arrival) or len(arrival) > 0 then
  cn.execute "update tblbackhauls set unload_user = '" & user & "' , unload_time_stamp = '" & FormatDateTime(Now) _
   & "' , unload_date = '" & date() & "' where dept_name = '" & dept & "' and po_number = '" & po & "' and arrive_date = '" _
   & arrival & "';"
  set cn=nothing
else
response.write "Wrong Date is " & arrival
end if
end if

%>


http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20324103
0
 
LVL 4

Expert Comment

by:mberumen
ID: 7158378
I have to agree with jitganguly on this one.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7158404
jit,
Look for your question in this topic area for your assistance in this question.

Computer101
E-E Moderator
0
 
LVL 1

Author Comment

by:nnaxor
ID: 7160177
I don't understand what the problem is.  We used mgfranz' sql statement - which helped but in actuality we resolved it ourselves.  That is why I awarded the points to mgfranz.  If this is incorrect in the future I will be more careful to clearly state who and how the problem was solved.
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 7160194
>>We used mgfranz' sql statement - which helped but in actuality we resolved it ourselves.

But earlier you said
>>it wasn't passing actual values it was just passing variable names, we have it now where it sends the actual data.
So there is a confusion here ?

and you said you used # with date var in SQLServer ? Is that a new syntx in SQLServer ????
Only access db supports that. No SQLServer/Oracle would allow you to use # with a date var
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
key press alert 2 48
CREATE DATABASE 3 46
ASP and Looping Thru a XML Document 4 48
GitHub 1 41
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

710 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