Nvarchar overflowed an int column
Posted on 2004-11-18
In one of my Access form's event codes is the following:
Dim rs1 As ADODB.Recordset
Dim strCriteria1 As String
Dim strCriteria2 As String
Set rs1 = New ADODB.Recordset
strCriteria1 = Me.EbayNum
strCriteria2 = Me.Parent.OrderID
rs1.Open "SELECT * FROM dbo.[ORDER-DETAIL] WHERE dbo.[ORDER-DETAIL].EbayNum = " & strCriteria1 & " AND dbo.[ORDER-DETAIL].OrderID = " & strCriteria2, CurrentProject.Connection
Me.EbayNum refers to a control that is bound to an underlying SQL table where EbayNum is of type nvarchar(20).
The code runs when I enter an EbayNum value into a subform. As I enter subsequent EbayNums, the code runs fine -- UNLESS one of the two following things happens:
1) I enter an EbayNum that is shorter than the longest EbayNum value already in the subform
2) I enter an EbayNum that is as long as the longest EbayNum value, but is of lesser numerical value
Then, I'll get one or the other of these errors:
1)"Runtime error '-2147217833 (80040e57)': Arithmetic overflow error converting numeric to data type numeric."
2)"Runtime error '-2147217833 (80040e57)': The conversion of the nvarchar value <last value in the list> overflowed an int column. Maximum integer value exceeded."
Either error occurs on the rs1.open statement. Can anyone shed some light? Why is 'int' even involved in this?