nnaxor
asked on
Date Error
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/backh auls/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_lo gin")("use rid") = "" 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;Dr iver={SQL Server};Server=172.16.96.1 00;Databas e=csi_repo rting;UID= gagnon;PWD =alandgene ;"
set cn=server.createobject("AD ODB.Connec tion")
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
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/backh
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_lo
Response.Redirect "../../../menu/pages/login
End If
%>
<META HTTP-EQUIV="Cache-Control"
<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;Dr
set cn=server.createobject("AD
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_lo gin")("use rid") = "" 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;Dr iver={SQL Server};Server=172.16.96.1 00;Databas e=csi_repo rting;UID= gagnon;PWD =alandgene ;"
set cn=server.createobject("AD ODB.Connec tion")
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>
<%
Response.Expires = 0
If Request.Cookies("csinet_lo
Response.Redirect "../../../menu/pages/login
End If
%>
<META HTTP-EQUIV="Cache-Control"
<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;Dr
set cn=server.createobject("AD
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>
Please disregard my comment, I did not see jitganguly's previous post
ASKER
Okay we used what was suggested and now what we are getting the actual word Arrival for the variable. Anybody?
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.que rystring ("arrival"),2)
end if
Problem here
if not isnull(request.querystring
arrival = formatdatetime(request.que
end if
ASKER
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_lo gin")("use rid") = "" 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'].loca tion.repla ce("'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>Highl ight 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;Dr iver={SQL Server};Server=172.16.96.1 00;Databas e=csi_repo rting;UID= gagnon;PWD =alandgene ;"
set cn=server.createobject("AD ODB.Connec tion")
set rs=server.createobject("AD ODB.Record set")
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.backgrou ndColor='w hite';styl e.cursor=' hand'; onMouseOut=style.backgroun dColor='la vender'; " _
& "onClick=ConfirmUnload('" & rs("dept_name") & "','" & rs("po_number") & "','" & rs("arrive_date") & "','" _
& Request.Cookies("csinet_lo gin")("use rid") & "');>"
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>
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_lo
Response.Redirect "../../../menu/pages/login
End If
%>
<META HTTP-EQUIV="no-cache">
<html>
<script language="JavaScript">
function forceReload() {
if(document.images)
location.replace(location.
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'].loca
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>Highl
<br><br>
</center>
<body bgcolor="lavender">
<%
dim cn, rs, cnStr, fontcolor, cn2
cnStr="Provider=MSDASQL;Dr
set cn=server.createobject("AD
set rs=server.createobject("AD
cn.open cnStr
rs.open "exec csi_get_backhauls;",cn,0,1
%>
<iframe style=="visibility:visible
<center>
<table cellspacing="0" cellpadding="0">
<tr>
<th align="left" width="85"><font face=arial size=2>Department</font></
<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.backgrou
& "onClick=ConfirmUnload('" & rs("dept_name") & "','" & rs("po_number") & "','" & rs("arrive_date") & "','" _
& Request.Cookies("csinet_lo
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();'><
</div>
</th>
<th align="right" width="150"><div id='CloseVis' style='VISIBILITY: visible;'>
<input type="button" value="Close" onClick="CloseWindow();"><
</div>
</th>
</tr>
</table>
</center>
</body>
</html>
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
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
ASKER
Thanks much
????
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.
??????????
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.
??????????
Any date string has to be wrapped in #..#, like '"# & date() & #"'
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."
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."
Mark,
Thats not true for SQLServer. You are right about Access not SQLServer/Sybase/Oracle.
He is using SQLServer and not access
Thats not true for SQLServer. You are right about Access not SQLServer/Sybase/Oracle.
He is using SQLServer and not access
So Response.Write sSql probably cleared up the problem...
It doesn't matter to me, as long as the solution was found. :-)
It doesn't matter to me, as long as the solution was found. :-)
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.que rystring ("arrival"),2)
end if
user = request.querystring ("user")
cnStr="Provider=MSDASQL;Dr iver={SQL Server};Server=172.16.96.1 00;Databas e=csi_repo rting;UID= gagnon;PWD =alandgene ;"
set cn=server.createobject("AD ODB.Connec tion")
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
%>
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20324103
<%
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 = formatdatetime(request.que
end if
user = request.querystring ("user")
cnStr="Provider=MSDASQL;Dr
set cn=server.createobject("AD
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
%>
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20324103
I have to agree with jitganguly on this one.
jit,
Look for your question in this topic area for your assistance in this question.
Computer101
E-E Moderator
Look for your question in this topic area for your assistance in this question.
Computer101
E-E Moderator
ASKER
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.
>>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
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
<%
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 = formatdatetime(request.que
end if
user = request.querystring ("user")
cnStr="Provider=MSDASQL;Dr
set cn=server.createobject("AD
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
%>