Sunday 6 July 2014

Classic ASP Provider error '8002000a' Out of present range.

One of our older websites, written in classic ASP and using a SQL Server 2008 R2 database, needs data importing every couple of months  or so from an Access database and we a have an ASP script ready to do this. It has been run more than 40 times but this month it hit a problem.

Provider error '8002000a'
Out of present range.
(and the line number)

This typically happens when trying to pass a value that is too large for the data type when using an ADODB.Connection.

But the code where it was failing was pretty simple:

sSQL = "update ARTICLE set AR_DISPLAY=0 where AR_TYPE='R'"

Call adoConn.Execute(sSQL, lRecords)

It was hard to see how this could involve any overvalued types until I tried the query in SSMS and it reported: 33826 row(s) affected. The suspicion now was that the number of records being return was too big for the lRecords value, so it must be an Integer. As it's not possible to explicitly declare this as a Long, the solution was to use a function to covert it before running the query, as below: -

lRecords = CLng(0)
sSQL = "update ARTICLE set AR_DISPLAY=0 where AR_TYPE='R'"

Call adoConn.Execute(sSQL, lRecords)