ASP Classic & SQL date out-of-range error

Question

I am moving an ASP classic app to a new server. I did not developed the app and I have no experience with ASP, hope someone can guide me.

One of the pages in the app drop this error:

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

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. 

/clientname/Admin/EntregasProcess.asp, line 49

I read that maybe could be the date format so I change it to yyyy-mm-dd.

Now it is showing this:

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

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. 

/clientname/Admin/EntregasProcess.asp, line 62

The date format on the database is writen like this:

2006-07-31 00:00:00.000

and this is the code of the asp file:

<%@ Language=VBScript %>
<!--#include file="StrConn.asp"-->
<%

if Session("Role") <> "AD" Then 'AD=Administrador, CG = Consulta Gral.
Response.Redirect "../home.asp"
end if

%>

<%

Dim month_number, year_number, day_number, tituloEntrega, Estatus, idCuestionario, date_number

month_number = Request.Form("month_number")
year_number = Request.Form("year_number")
day_number = Request.Form("day_number")
tituloEntrega = trim(Request.Form("tituloEntrega"))
idCuestionario = Request.Form("idCuestionario")
Estatus = Request.Form("Estatus")
idEntrega = Request.Form("idEntrega")
<!--BITACORA::ESC::Oscar Salgado 20090729-->
bitacora = Request.Form("bitacora")
page = Request.Form("page")
<!--=====================================-->


date_number = dateSerial(year_number,month_number+1,day_number)

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnPortal

<!--BITACORA::ESC::Oscar Salgado 20090729-->
SQL="Delete from dbo.tbBitacoraShow where idCuestionario = " & idCuestionario
conn.execute(SQL)
if bitacora = "1" Then
    SQL="Insert into dbo.tbBitacoraShow(idCuestionario) values(" & idCuestionario & ")"
end if
conn.execute(SQL)
<!--=====================================-->

Select Case Request.Form("btnGo")

    Case "Agregar"
    if trim(tituloentrega) <> "" Then
        SQL = "Insert into tbEntregas(TituloEntrega,Date,Estatus,IDCuestionario) values('" & _
            tituloEntrega & "','" & date_number & _
            "'," & estatus & ",'" & idCuestionario & "')"
        conn.Execute sql
    end if


    'Response.Write SQL
    'Response.End

    Case "Actualizar"

    SQL = "update tbEntregas Set TituloEntrega = '" & tituloEntrega & _
        "',Date='" & date_number & " ',Estatus=" & estatus & _
        ",IdCuestionario='" & idCuestionario & "' Where idEntrega = " & idEntrega

    conn.Execute sql

    'Response.Write SQL
    'Response.End

End Select


Response.Redirect "Entregas.asp?idEntrega="&idEntrega&"&page="&page

%>

Check the old server's date format. I suspect that the new environment's localization of dates is different from the old server. (Specifically, is this also a new SQL server? The default date format for the connection looks like it's expecting a different order) — Tetsujin no Oni, Jul 19 '12 at 20:04
you definitely want to at least change all your request.forms to replace single quotes with double single quotes or empty string, but better yet, change the code to use stored procedures or parameterized queries, or anybody will be able to see your database, update and delete your data — Rodolfo, Jul 19 '12 at 21:32
Thanks for the advice Tetsujin. I'm not a skilled programer and need it to be working ASAP. I did what podiluska sugested, but I had the same out-of-range error. May you help to make this work? Thanks! — Rodolfo Awenydd Luna Bernal, Jul 19 '12 at 21:45

Rafael · Accepted Answer · 2012-07-19T22:15:57.443

Ok this is a common problem when your Queries aren't parametrized correctly. i consider this a bad practice.

This problem occurs when the developer hardcode the date format into the application. and it's affected by one or more of the follwing factors:

Changes in Regional/Language Settings in IIS/Web Server
Changes in Language settings in the Database User

The Dates must be in ODBC cannonical format ('yyyy-mm-dd hh:MM:ss') to avoid any problems. this is the universal format where you avoid any of the factors i've metioned earlier.

I give you 3 possibles solution to this:

The correct way is to parametrize correctly in ASP your Queries using "ADODB.Command" Object using the method ".CreateParameter()" and append it to the command. the problem is that you need to add a good chunk of additional code to achieve this.

Example code

'-------------------------------------------------------------------'
Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.CommandText = sSQL
set oCmd.ActiveConnection= oConn
Set oPar = oCmd.CreateParameter("initial_date",7,1,,dDate) 'Date
oCmd.Parameters.Append(oPar)
'-------------------------------------------------------------------'

the practical way is to change the hardcoded format into cannonical format to avoid current and possible future problems.

the quick fix is change the regional settings in your Web Server or the language settings in your database Server, to match your past implementations.

how to do this:

first you need to know which format is that the developer used in the application: Example in Mexico the format it's dd/mm/yyyy and commonly the server are in english (mm/dd/yyyy)

Web Server - Regional Settings

Regional Setting Windows 7

enter image description here

Default Language Setting - Database Server

Open the SQL server Management Studio go to the section that image showss and select the

user that access the application database

SQL management Studio

Cahnge the default language to the date format used by the application

User Language Setting

Hi Rafael! Please tell how to do the quick fix. I already changed the language settings but I don't know how to do the rest. I'm a webdesigner so this is beyond my skills. Gracias! — Rodolfo Awenydd Luna Bernal, Jul 19 '12 at 21:54
I will change everything to US english, because that was the default language of the original server. I can't reboot the server right now (people still working here) but will tell you how it works ina a couple of hours. Thank you very much. — Rodolfo Awenydd Luna Bernal, Jul 19 '12 at 22:25
Por cierto, están buscando quién optimize y modernize esto, si te interesa contáctame: rodolfo at molcajete punto com punto mx — Rodolfo Awenydd Luna Bernal, Jul 20 '12 at 01:11

podiluska · Answer 2 · 2012-07-19T21:13:50.790

Try changing the Insert date line bits to

SQL = "Insert into tbEntregas(TituloEntrega,Date,Estatus,IDCuestionario) values('" & _  
        tituloEntrega & "',Convert(datetime, '" & date_number & "')," & estatus & ",'" & idCuestionario & "')"

and the update to

SQL = "update tbEntregas Set TituloEntrega = '" & tituloEntrega & _  
    "', Convert(datetime, '" & date_number & "'),Estatus=" & estatus & _  
    ",IdCuestionario='" & idCuestionario & "' Where idEntrega = " & idEntrega

You should also be aware that this code as it stands is a SQL Injection attack waiting to happen

score 0 · Answer 3 · edited Oct 01 '13 at 05:34

0

ASP gets the date from the OS not from the Database, a common error, but it is solved by use:

<%
' Date dd/mm/yyyy
Session.lcid=2057 '= UK English
%>

edited Oct 01 '13 at 05:34

Brad Larson

170,088
45
397
571

answered Oct 01 '13 at 05:00

Ronald Avendaño

31
1

ASP Classic & SQL date out-of-range error

3 Answers3

Linked