72417

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

Answer1:

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:

<ul><li>Changes in Regional/Language Settings in IIS/Web Server </li> <li>Changes in Language settings in the Database User</li> </ul>

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:

<strong>The correct way</strong> 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.

<strong>Example code</strong>

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

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

<strong>the quick fix</strong> 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

<img alt="Regional Setting Windows 7" class="b-lazy" data-src="https://i.stack.imgur.com/nkVfC.png" data-original="https://i.stack.imgur.com/nkVfC.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/GFQN4.png" data-original="https://i.stack.imgur.com/GFQN4.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

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

<img alt="SQL management Studio" class="b-lazy" data-src="https://i.stack.imgur.com/RXLAH.png" data-original="https://i.stack.imgur.com/RXLAH.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

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

<img alt="User Language Setting" class="b-lazy" data-src="https://i.stack.imgur.com/8pu0V.png" data-original="https://i.stack.imgur.com/8pu0V.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />

Answer2:

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

Answer3:

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

Recommend

  • APNs messages are delivered but not received on iOS device
  • C# “cannot assign field because it is a foreach iteration variable”
  • getElementsByClassName and AJAX
  • wsContext.getMessageContext().get(MessageContext.SERVLET_REQUEST) returns null on Jetty?
  • Send Raw Email (with attachment) to Multiple Recipients
  • How to startActivity for Result from Activity under Group Activity Under Tabs?
  • Beautiful Soup findAll doesn't find them all
  • how to pass value from asp.net server control using jQuery?
  • How to send params in url query_string in Symfony?
  • HTTP Module intercept requests and breaks custom errors configuration
  • Updates to SolrConfig.xml file are not being reflected
  • UrlEncode non-string properties for HTTP Post through HttpClient
  • Share two different things in Windows Phone 8.1 C#
  • Excel 2007: Format of email address from Outlook 2007
  • MarkLogic Node.js Sort on “last-modified”
  • How to wait for all async tasks to finish in Node.js?
  • Updating and removing unique join relationships in CakePHP
  • Zend framework 2 : Add different authentication adapter for two different modules
  • PHP get selected value of select form
  • Negating Regex PO BOX
  • Mvc 3 posting content type application/json, action method arguments loose mapping?
  • retrieving data from url in iphone
  • How to apply a custom handlers to only specific folder
  • Google Maps api v3 get start and end coordinates of a street
  • HttpClient: disabling chunked encoding
  • How to get latest version of a artifact on Bintray using JSONP
  • IE11 throwing “SCRIPT1014: invalid character” where all other browsers work
  • How can I speed up CURL tasks?
  • Bad request using file_get_contents for PUT request in PHP
  • Seeking advice on Jetty HttpClient Hang
  • How would I use PHP exceptions to define a redirect?
  • Why doesn't :active or :focus work on text links in webkit? (safari & chrome)
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Symfony2: How to get request parameter
  • jQuery tmpl and DataLink beta
  • SQL merge duplicate rows and join values that are different
  • log4net write single file for each call to log.info
  • Proper way to use connect-multiparty with express.js?
  • Getting error when using KSoap library to consume .NET web services
  • How to set the response of a form post action to a iframe source?