Hi There,
When I try and save a date its value changes.
I'm using MS Sql 2000 data base server and the field type is set to datetime.
For example if I try and save...
8 Oct 2003 it actually stores 10 Aug 2003
9 Aug 2003 it actually stores 8 Sep 2003
7 Sep 2003 it actually stores 9 Jul 2003
30 September results in an error
[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.
I'll try and paste the asprunner generated page below
In SQL enterprise manager I have no problem entering dates directly into the database.
And then displaying the dates correctly in my ASPrunner pages
Any ideas on what could be going on here?
Thanks in advance
David Pollard
========================================================================
<%@ Language=VBScript %>
<%
If Session("UserID")="" Then
Response.Redirect "login.asp"
End If
%>
<html>
<link REL="stylesheet" href="include/style.css" type="text/css">
<!--#include file="include/dbconnection.asp"-->
<!--#include file="include/variables.asp"-->
<!--#include file="include/aspfunctions.asp"-->
<body bgcolor=white>
<script language="JavaScript" src="include/ts_picker.js">
</script>
<form method="POST" action="Sales_Listinglist.asp" name="frmAdmin">
<input type=hidden id="TargetPageNumber" name="TargetPageNumber" value="<%=Request.Form("TargetPageNumber")%>" >
<input type=hidden id=SQL name=SQL value="<%=Request.Form("SQL")%>">
<input type=hidden id="sourceID" name="sourceID" value="<%=sourceID%>" >
<input type=hidden id="cmdGotoPage" name="cmdGotoPage">
<script language="JavaScript" >
<!--#INCLUDE FILE="include/jsfunctions.js"-->
function GotoPage(nPageNumber)
{
document.forms.frmAdmin.cmdGotoPage.value = 'GotoPage';
document.forms.frmAdmin.TargetPageNumber.value = nPageNumber;
document.forms.frmAdmin.submit();
}
</script>
</form>
<%
On Error Resume Next
message=""
mypage=Request.Form("targetpagenumber")
if mypage="" then
mypage=1
myaction=Request.Form("action")
if myaction="view" then
myaction="edit"
if myaction="add" then
myaction="added"
' open database connection
Set rs = server.CreateObject ("ADODB.Recordset")
set dbConnection = server.CreateObject ("ADODB.Connection")
dbConnection.ConnectionString = strConnection
dbConnection.Open
Call ReportError
if (Request.Form("NeedQuotes"&Replace(strKeyField," ","")))="True" then
strQuote="'"
else
strQuote=""
end if
' insert new record if we have to
if Request.Form("action")="added" then
strSQL = "insert into " & strTableName & " "
strFields = "("
strValues = "("
' fields list
For i = 1 To Request.Form("FieldName").Count
' skip key fields
do while Request.Form("Updatable")(i)<>"True"
i=i+1
if i>=Request.Form("FieldName").Count then exit do
loop
if i>Request.Form("FieldName").Count then exit for
strFields=strFields & AddWrappers(Request.Form("FieldName")(i)) & ", "
' build list of values
' boolean
if Request.Form("FieldType")(i)="11" then
if Request.Form(Request.Form("FieldName")(i))="on" then
strValue="1"
else
strValue="0"
end if
else ' not boolean
if (Request.Form("NeedQuotes")(i))="True" then
strValue= "'" & Replace( Request.Form(Request.Form("FieldName")(i)) , "'", "''") & "'"
else
if Request.Form(Request.Form("FieldName")(i))="" then
strValue="0"
else
strValue=Request.Form(Request.Form("FieldName")(i))
end if
end if
end if
strValues = strValues & strValue & ", "
Next
' adjust strFields and strValues
if Right(strFields,2)=", " then strFields = Left(strFields, Len(strFields)-2)
strFields = strFields & ")"
if Right(strValues,2)=", " then strValues = Left(strValues, Len(strValues)-2)
strValues = strValues & ")"
strSQL = strSQL & strFields & " values " & strValues
LogInfo(strSQL)
dbConnection.Execute strSQL
Call ReportError
message="<div class=message><<< Record was added >>></div>"
end if
' save changes
if (Request.Form("saveid")<>"" and Request.Form("action")="edit") then
strSQL="update " & strTableName & " set "
For i = 1 To Request.Form("FieldName").Count
if Request.Form("Updatable")(i)<>"True" then
i=i+1
strSQL=strSQL & AddWrappers(Request.Form("FieldName")(i)) & "="
' boolean
if Request.Form("FieldType")(i)="11" then
if Request.Form(Request.Form("FieldName")(i))="on" then
strValue="1"
else
strValue="0"
end if
else ' not boolean
if (Request.Form("NeedQuotes")(i))="True" then
strValue= "'" & Replace( Request.Form(Request.Form("FieldName")(i)) , "'", "''") & "'"
else
if Request.Form(Request.Form("FieldName")(i))="" then
strValue="0"
else
strValue=Request.Form(Request.Form("FieldName")(i))
end if
end if
end if
strSQL = strSQL & strValue
if i<>Request.Form("FieldName").Count then
strSQL = strSQL & ", "
Next
strSQL = strSQL & " where " & AddWrappers(strKeyField) & "=" & strQuote & Replace(Request.Form("saveid"),"'","''") & strQuote
if strKeyField2<>"" then
strSQL = strSQL & " and " & AddWrappers(strKeyField2) & "=" & strQuote & Replace(Request.Form("saveid2"),"'","''") & strQuote
if strKeyField3<>"" then
strSQL = strSQL & " and " & AddWrappers(strKeyField3) & "=" & strQuote & Replace(Request.Form("saveid3"),"'","''") & strQuote
LogInfo(strSQL)
dbConnection.Execute strSQL
Call ReportError
message="<div class=message><<< Record was saved >>></div>"
end if
if Request.form("action")="add" or Request.form("action")="added" then
strSQL="select * from " & strTableName & " where 1<0"
Response.Write "<h1>Add new record</h1>"
else
strSQL="select " & AddWrappers(strKeyField) & "," & AddWrappers("Listing_id") & " ," & AddWrappers("Date_Listed") & " ," & AddWrappers("List_Price") & " ," & AddWrappers("Ask_Price") & " ," & AddWrappers("Address_1") & " ," & AddWrappers("Address_2") & " ," & AddWrappers("Suburb") & " ," & AddWrappers("State") & " ," & AddWrappers("Postcode") & " ," & AddWrappers("Date_Sold") & " ," & AddWrappers("Internal_Image_1") & " ," & AddWrappers("External_Image1") & " from " & strTableName
strSQL=strSQL & " where " & AddWrappers(strKeyField) & "=" & strQuote & Replace(request.form("editid"),"'","''") & strQuote
if strKeyField2<>"" then
strSQL=strSQL & " and " & AddWrappers(strKeyField2) & "=" & strQuote & Replace(request.form("editid2"),"'","''") & strQuote
if strKeyField3<>"" then _
strSQL=strSQL & " and " & AddWrappers(strKeyField3) & "=" & strQuote & Replace(request.form("editid3"),"'","''") & strQuote
Response.Write "<h1>Edit record [" & strKeyField & ": " & request.form("editid") & "]</h1>"
end if
response.write message
response.write "<div align=left><hr width=300 noshade size=1></div>"
LogInfo(strSQL)
rs.open strSQL, dbConnection
Call ReportError
Response.write " <a href=Sales_Listing_list.asp onClick=""GotoPage( " & mypage & "); return false;"">Back to list</a>"
Response.Write "<table cellpadding=2>"
%>
<form name=editform method=post action=Sales_Listing_edit.asp>
<input type=hidden id="TargetPageNumber" name="TargetPageNumber" value="<%=Request.Form("TargetPageNumber")%>" >
<input type=hidden id=SQL name=SQL value="<%=Request.Form("SQL")%>">
<input type=hidden name=saveid value="<%=request.form("editid")%>">
<input type=hidden name=saveid2 value="<%=request.form("editid2")%>">
<input type=hidden name=saveid3 value="<%=request.form("editid3")%>">
<input type=hidden name=editid value="<%=request.form("editid")%>">
<input type=hidden name=editid2 value="<%=request.form("editid2")%>">
<input type=hidden name=editid3 value="<%=request.form("editid3")%>">
<input type=hidden id=NeedQuotes<%=Replace(strKeyField," ","")%> name=NeedQuotes<%=Replace(strKeyField," ","")%>
value="<%=Request.Form("NeedQuotes" & Replace(strKeyField," ",""))%>">
<%
for i=0 to rs.Fields.Count-1
for j=0 to i-1
if rs.Fields.Item(i).Name=rs.Fields.Item(j).Name then
i=i+1
j=i+1
end if
next
if i<=rs.Fields.Count-1 then
nType = rs.Fields.Item(i).Type
' is field updatable?
if rs.Fields.Item(i).Attributes and 4 or rs.Fields.Item(i).Attributes and 8 then
bUpdatable=true
else
bUpdatable=false
end if
if rs.EOF and rs.BOF then
strValue=""
else
strValue = GetData(rs.Fields.Item(i), FORMATNONE )
end if
Response.Write vbCRLF & "<tr><td class=shade>"
Response.Write Label(rs.Fields.Item(i).Name) & " "
Response.Write "</td><td>"
Response.Write "<input type=hidden name=FieldName value=""" & rs.Fields.Item(i).Name & """>"
Response.Write "<input type=hidden name=FieldType value=" & rs.Fields.Item(i).Type & ">"
if rs.Fields.Item(i).Type = 203 or rs.Fields.Item(i).Type = 8 or rs.Fields.Item(i).Type = 129 or
rs.Fields.Item(i).Type = 130 or rs.Fields.Item(i).Type = 7 or rs.Fields.Item(i).Type = 133 or
rs.Fields.Item(i).Type = 134 or rs.Fields.Item(i).Type = 135 or rs.Fields.Item(i).Type = 201 or
rs.Fields.Item(i).Type = 205 or rs.Fields.Item(i).Type = 200 or rs.Fields.Item(i).Type = 202 or
rs.Fields.Item(i).Type = 72 then
strNeedQuotes="True"
else
strNeedQuotes="False"
end if
if bUpdatable then
strUpdatable="True"
else
strUpdatable="False"
end if
Response.Write "<input type=hidden name=NeedQuotes value=" & strNeedQuotes & ">"
Response.Write "<input type=hidden name=Updatable value=" & strUpdatable & ">"
' long binary data
if (rs.Fields.Item(i).Attributes and 128) and ( rs.Fields.Item(i).Type = 204 or rs.Fields.Item(i).Type=205 ) then
Response.Write "LONG BINARY DATA - CANNOT BE DISPLAYED"
end if
if bUpdatable then
' text area
if nType = 203 or nType = 201 then
Response.Write "<textarea cols=50 rows=10 name=""" & rs.Fields.Item(i).Name & """>" & strValue & "</textarea>"
end if
' check box
if rs.Fields.Item(i).Type = 11 then ' boolean
Response.Write "<input type=checkbox name=""" & rs.Fields.Item(i).Name
if strValue="True" then
Response.write " checked "
end if
Response.Write """>"
end if
' set length for text or numeric
if nType = 3 then
strSize=10
elseif nType = 17 then
strSize=3
else
strSize=50
end if
' date or datetime field
if IsDateField(rs.Fields.Item(i)) then
Response.Write GetDateEdit(rs.Fields.Item(i).Name, strValue, DateEditType(rs.Fields.Item(i).Name))
elseif rs.Fields.Item(i).Type <> 201 and rs.Fields.Item(i).Type <> 203 and rs.Fields.Item(i).Type <> 11
and rs.Fields.Item(i).Type <> 204 and rs.Fields.Item(i).Type <> 205 then
Response.Write "<input type=text name=""" & rs.Fields.Item(i).Name & """ size = " & strSize & " value=""" & strValue & """>"
end if
else
Response.Write strValue
end if
' add icons if required
if rs.Fields.Item(i).Name=strKeyField or rs.Fields.Item(i).Name=strKeyField2 or rs.Fields.Item(i).Name=strKeyField3 then
Response.Write " <img src=images/icon_key.gif>"
else
if (rs.Fields.Item(i).Attributes and 96)=0 then
Response.Write " <img src=images/icon_required.gif>"
end if
end if
Response.Write "</td></tr>"
' Response.Write rs.Fields.Item(i).Name & " " & CStr(rs.Fields.Item(i).Type) & " " & rs.Fields.Item(i).Attributes & "
"
' for j=1 to rs.Fields.Item(i).Attributes.Count
' Response.Write rs.Fields.Item(i).Properties.Item(j).Name & " = " & rs.Fields.Item(i).Properties.Item(j).Value & "
"
' next
end if
next
%>
<!-- legend -->
<tr height=50><td colspan=2 align=left>
<hr width=300 noshade size=1>
<img src=images/icon_key.gif> - key field
<img src=images/icon_required.gif> - required field
</td></tr>
<!-- buttons -->
<tr height=50><td colspan=2 align=center>
<input class=button type=submit value="Save" id=submit1 name=submit1>
<input class=button type=reset value="Reset">
<input type=hidden name=action value=<%=myaction%>>
</td></tr>
</table>
</form>
</body>
</html>
