ASP Classic& SQL日期超出范围错误 [英] ASP Classic & SQL date out-of-range error

问题描述

我正在将ASP经典应用程序移动到新的服务器。我没有开发应用程序，我没有ASP的经验，希望有人可以指导我。

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 

我读这可能是日期格式，所以我将其更改为yyyy-mm-dd。

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

现在显示：

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 

这是asp文件的代码：

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

%>

推荐答案

确定这是一个常见问题， t参数化正确。我认为这是一个不好的做法。

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:

• IIS / Web服务器中的区域/语言设置更改


• 数据库用户中的语言设置更改

日期必须采用ODBC大炮格式'yyyy-mm-dd hh：MM：ss'）以避免任何问题。这是通用格式，您可以避免以前使用的任何因素。

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.

我给你3个可能的解决方案：

I give you 3 possibles solution to this:

正确的方法是使用.CreateParameter（）方法使用ADODB.Command对象在ASP中正确参数化并附加到命令中。问题是您需要添加一大批额外的代码来实现这一点。

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.

示例代码

'-------------------------------------------------------------------'
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.

快速修复是更改Web服务器中的区域设置或数据库服务器中的语言设置，以符合您以前的实现。

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.

如何做到这一点：

首先你需要知道哪种格式是de veloper在应用程序中使用：
墨西哥的示例是dd / mm / yyyy的格式，通常服务器是英文（mm / dd / yyyy）

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服务器 - 区域设置

Web Server - Regional Settings

默认语言设置 - 数据库服务器

Default Language Setting - Database Server

打开SQL Server Management Studio，转到图像显示部分，然后选择

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

这篇关于ASP Classic& SQL日期超出范围错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！