Out of present range

Question

_{Saturday, January 9, 2010 10:55 PM}

Whenever I try inserting more than 32767 records into my sql server 2005 express table using a select statement I get a "out of present range" error.  The insert statement still works but that error is very annoying.  See code below.

strSQL1 = "INSERT INTO TempClaimDetails (ClaimDetailID,ClaimDate,ClaimantID,EmployeeNumber,DependentID,ClaimNumber,BenefitCode,Year,AmountClaimed,AmountRefunded,"
strSQL1 = strSQL1 & " ChequeStatus,ChequeDate,ChequeNumber,ClaimType,JobCatgCode,DepartmentCode,PlanCode,LocationCode,UserID)"
strSQL1 = strSQL1 & " SELECT ClaimDetailID,ClaimDate,ClaimantID,EmployeeNumber,DependentID,ClaimNumber,BenefitCode,Year,AmountClaimed,AmountRefunded,"
strSQL1 = strSQL1 & " ChequeStatus,ChequeDate,ChequeNumber,ClaimType,JobCatgCode,DepartmentCode,PlanCode,LocationCode,UserID FROM ClaimDetails"
strSQL1 = strSQL1 & " WHERE ChequeStatus = 'S'
adoConnection.Execute strSQL1, intRecordsAffected

All replies (3)

_{Sunday, January 10, 2010 1:40 PM ✅Answered}

This is  because you are passing a value that is too large for the data type when using an ADODB.Connection.

For example, sending 2^32 to a parameter that is specified as adInteger, will cause this

or in other words when you insert** a value of ten billion** in an integer column in SQL server; ADO will throw
provider error 8002000A 

http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-8002000a-errors.html

---

_{Wednesday, January 20, 2010 4:33 PM}

I am not sure I buy the too large for data type argument .  I simplified my code to this:

strSQL1 = "INSERT INTO TempClaimDetails (ClaimDetailID)"
strSQL1 = strSQL1 & " SELECT ClaimDetailID FROM ClaimDetails"
adoConnection.Execute strSQL1, intRecordsAffected

So I am inserting only one field into the table, and I checked the values in the field and the largest value being passed is 37262, and both ClaimDetailID fields are of type int.  So why am I still getting an "out of present range" error?  Bear in mind that when I limit the number of records being inserted to less than 32700 no error is generated.

---

_{Monday, April 11, 2011 8:18 PM}

Chirag is right.

If your query is trying to insert more than  32,767 records then your intRecordsAffected variable which is Integer will not hold the value and this error will occur.

Declare intRecordsAffected variable using the following and you will not see this error

Dim   intRecordsAffected   As   Long

::Mac