ASP Classic & SQL date out-of-range error

问题:

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

%>

回答1:

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

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

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

回答2:

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

回答3:

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
%>