Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Datetime error 1

Status
Not open for further replies.
arravalli12

arravalli12

Programmer
May 3, 2005
62
US
I am getting this error: It has something to do with isdate(). This happens only if I put say 13/11/2005
Can anyone help me what this error is and how to fix?

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
/PLCB_Intranet/Developers_Work_Area/mparikh/coupon_expire_update.asp, line 35

My coding is:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Server.ScriptTimeout = 1000000 %>
<!--#include virtual="/Connections//Agency.asp" -->
<!--#include virtual="/Connections/Datacom.asp" -->

<%
If Request.form("Submit") <> "" Then
Dim amt,st_dte,expr_dte,Code
dim emsg(50)
amt = Request.form("amt")
st_dte = trim(Request.Form("st_dte"))
expr_dte = trim(Request.form("expr_dte"))
Code = Request.form("Code")
if len(request.form("st_dte")) <> 0 then
if not isdate (request.form("st_dte")) then
MM_abortEdit = true
emsg(e) = "Start date should be in format mm/dd/yyyy"
e = e + 1
end if
end if
if len(request.form("expr_dte")) <> 0 then
if not isdate (request.form("expr_dte")) then
MM_abortEdit = true
emsg(e) = "Expiration date should be in format mm/dd/yyyy"
e = e + 1
end if
end if
if MM_abortEdit = false then
set Doupdate = Server.CreateObject("ADODB.Command")
Doupdate.ActiveConnection = MM_Agency_STRING
Doupdate.CommandText = "Update dbo.coupon_expiration_tracking Set amt ='" & amt& " ', st_dte ='" & st_dte& " ', expr_dte ='" &expr_dte& "' WHERE cd_no= '"&Code&"' and expr_dte = '" &expr_dte& " '"
Doupdate.CommandType = 1
Doupdate.CommandTimeout = 0
Doupdate.Prepared = true
Doupdate.Execute()
response.write " Record updated"
Response.Redirect("ec_couponRebateExpiration.asp")
end if
end if%>
<%
CodeNumber = request.querystring("cd_no")
expDate = request.querystring("expr_dte")
select case len(ProductNumber)
case "4" CodeNumber = "0" & CodeNumber
case "3" CodeNumber = "00" & CodeNumber
case "2" CodeNumber = "000" & CodeNumber
case "1" CodeNumber = "0000" & CodeNumber
end select
CodeNumber_str = CStr(CodeNumber)
%>

<%
Dim Recordset2
Dim Recordset2_numRows
Dim MM_abortEdit
'MM_abortEdit = false
Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_Agency_STRING
Recordset2.Source = "select cd_no,amt,st_dte,expr_dte from dbo.coupon_expiration_tracking where cd_no = '"&CodeNumber_str&"' and expr_dte ='" &expDate&"'"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()
%>


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Coupon Expiration Update</title>
<body>
<%
if MM_abortEdit= true then
amt = Request.Form("amt")
st_dte = Request.Form("st_dte")
expr_dte = Request.Form("expr_dte")
MM_abortEdit = false
else
amt = trim((Recordset2.Fields.Item("amt").Value))
st_dte = trim((Recordset2.Fields.Item("st_dte").Value))
expr_dte = trim((Recordset2.Fields.Item("expr_dte").Value))
end if%>
<!--#include virtual="/COMMON/DSFRemoteUI/DSF_RemoteUI_inc.asp" -->
<%=top&nonavtop%>
<table width="98%" border="1" align="center">
<tr>
<td><form name="form1" method="post" action=""coupon_expire_update.asp"">
<p align="center"><strong>Coupon Expiration Update</strong></p>
<font color="#FF0000">
<%
if e > 0 then
for i = 0 to e - 1
response.write "<br>Error - "&emsg(i)
next
end if %>
</font>
<table width="96%" border="1" align="center">
<tr>
<td width="162" bgcolor="#CCCC99"><div align="left">Code</div></td>
<td width="728" ><div align="left"><%=(Recordset2.Fields.Item("cd_no").Value)%></div><input name="Code" type="hidden" value="<%=(Recordset2.Fields.Item("cd_no").Value)%>" ></td>
</tr>
<tr>
<td bgcolor="#CCCC99"><div align="left">Amount</div></td>
<td><input name="amt" type="text" value="<%=amt%>" size="20" maxlength="18"></td>
</tr>
<tr>
<td bgcolor="#CCCC99"><div align="left">Start Date (&quot;mm/dd/yyyy&quot;) </div></td>
<td><input name="st_dte" type="text" value="<%=st_dte%>" size="10" maxlength="10"></td>
</tr>
<tr>
<td bgcolor="#CCCC99"><div align="left">Expiration Date (&quot;mm/dd/yyyy&quot;)</div></td>
<td><input name="expr_dte" type="text" value="<%=expr_dte%>" size="10" maxlength="10"></td>
</tr>

</table>
<p align="center"><a href="ec_couponRebateExpiration.asp">Coupon Management Page </a></p>
<p align="center">

<input name = "submit" type="submit" value="Update" >
</p>
</form>
<br></td>
</tr>
<tr> </tr>
</table>
</html>
 
Sort by date Sort by votes
Thanks for the quick reply.If this is not valid date then I should get an error message as per my programme. I get error message when I put 99999999 but here instead it is blowing up the page...
 
Upvote 0 Downvote
try using convert() or cast() functions...

try something like this:

isdate(Convert(datetime,'13/11/2005'))

-DNG
 
Upvote 0 Downvote
I added following code to check date and when I added 13/11/2005 Response statement shows as 11/13/2005. my question is why it takes 13 as date instead of month. Looks like that is where problem lies.

st_dte =FormatDateTime(st_dte,0)
Month1 = Month(st_dte)
if len (Month(st_dte)) = 1 then
Month1 ="0" & Month1
end if
Day1 = Day(st_dte)
if len (Day(st_dte)) = 1 then
Day1 ="0" & Day1
end if
st_dte = Month1&"/"&Day1&"/"&year(st_dte)
response.write st_dte
response.end
 
Upvote 0 Downvote
In some places they do dd/mm/yy instead of mm/dd/yy so the local settings may have something to do with it.... perhaps your server thinks it is in London?

 
Upvote 0 Downvote
The funny part is If I put 10/13/2005. Response statement shows as 10/13/2005 but for 13/11/2005 response statement shows as 11/13/2005.
Strange?.
Anyways - What all I am trying to do is validate date and if not valid date display error.
Any other way to do apart from what I am doing.
Thanks,Arl
 
Upvote 0 Downvote
You could do some additional error checking using the string functions instead of Month(), Day(), and Year()

Something like:
[tt]
iSlashPos = Instr(expr_dte, "/")

If (iSlashPos < 2) Or (iSlashPos > 3) Then
emsg(e) = "Start date should be in format mm/dd/yyyy"
'...
Else
sFirstNum = Left(expr_dte, iSlashPos - 1)
If Not IsNumeric(sFirstNum)
emsg(e) = "Start date should be in format mm/dd/yyyy"
'...
Else
If (cInt(sFirstNum) > 12) Or (cInt(sFirstNum) < 1) Then
emsg(e) = "Start date should be in format mm/dd/yyyy"
'...
Else
'OK so far
End If
End If
End If
[/tt]

 
Upvote 0 Downvote
Another thing you could do is use separate dropdowns for day, year, and month on your HTML form... but that adds an additional headache of leap-years or even Feb 31st!
 
Upvote 0 Downvote
I liked your idea of additional error checking using the string functions. I am going to try that today. Yesterday I had to attend meeting and could not work on this.
Thanks
 
Upvote 0 Downvote
Thanks once again Sheco. I used ur string functions first and then used isdate() for date validation. Application was tested thorougly and so far works excellent..
I never thought of string functions for date validations..
 
Upvote 0 Downvote
Yeah you can use them (string functions) to validate just about anything that comes in over the Request because HTTP is a text based protocol... you might even try to validate a binary upload since it would be mime/base64 encoded but I can't imagine a situation where that would be the BEST way to do it...
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
1K
Krus1972
Krus1972
leifoet
  • Locked
  • Question
Using date picker gives an error message, only at application start
Replies
1
Views
626
MarkSweetland
MarkSweetland
chikwendu
  • Locked
  • Question
OraOLEDB error '80040e4b'
Replies
1
Views
937
xwb
xwb
evr72
  • Locked
  • Question
formatting error
Replies
3
Views
370
evr72
evr72
BunBo900
  • Locked
  • Question
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Replies
1
Views
531
BunBo900
BunBo900

Part and Inventory Search

Sponsor

Back
Top