January 19, 2017 at 6:40 pm
We have a client running our help desk software (Web+Center) package and use SQL*Server on the backend. We have thousands of installations but this one fails in this one spot with one client. It appears to be failing on the Movenext instruction in the code listed below. This code has been in place for maybe 5-10 years without issues in other systems. Could there be something about database and cursors or cache that doesn't allow this work. The other odd thing is that it works correctly when there is just one returned record to delete, but more than 1 it fails.
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
mySQL2="select * from EmailCategoryLinks where customer_Id = '" & customer_ID & "'"
'response.write "MySQL2 " & mysql2
'response.flush
'response.end
Set rstemp = Server.CreateObject("ADODB.Recordset")
rstemp.Open mySQL2,ConnTemp,3,3
If rstemp.EOF <> True THEN
Do while (rstemp.EOF <> True)
response.write "Record Found " & rstemp("Customer_id")
rstemp.delete
response.write "<br>before movenext"
rstemp.movenext
loop
End If
rstemp.close
set rstemp=nothing
conntemp.close ' Close the connection
set conntemp=nothing
January 20, 2017 at 3:32 pm
Sue
January 20, 2017 at 3:52 pm
Sue_H - Friday, January 20, 2017 3:32 PMIt's not a SQL Server issue or error, doesn't have anything to do with SQL Server. It's an ADO recordset issue/error. You don't have the current recordset at the time you movenext. It's been too long that I have no idea how to address that. Some way to requery or use a different recordset type is all I can think of.
No idea what is a good ADO forum either but that would be where you would want to post this.Sue
Hi Sue, I had similar thoughts this was a ADO recordset issue but it doesn't occur with Access and didn't occur for this client with this same piece of code until recently so I didn't want to rewrite the code if the problem exists in some ADO driver that only allows a limited number of record sets to be returned. What's strange is that this code doesn't fail with 1 in the record set, just 2 or more so the movenext should fail with 1 too. I'm not that familiar with this forum so if you know of a more focused post area let me know.
January 20, 2017 at 4:26 pm
scott 87260 - Friday, January 20, 2017 3:52 PMHi Sue, I had similar thoughts this was a ADO recordset issue but it doesn't occur with Access and didn't occur for this client with this same piece of code until recently so I didn't want to rewrite the code if the problem exists in some ADO driver that only allows a limited number of record sets to be returned. What's strange is that this code doesn't fail with 1 in the record set, just 2 or more so the movenext should fail with 1 too. I'm not that familiar with this forum so if you know of a more focused post area let me know.
Everyone always blames the database....🙂
The error comes from the OLE DB provider itself. Being that you would use different providers for Access than for SQL Server and being that Access <> SQL Server, they aren't going to behave the same. Similar but not the same. Years ago, SQL Server supported things with the recordsets that Access did not. I have no idea if that's still true.
And drivers/providers/frameworks get updated so you can have scenarios where the database doesn't change but other pieces do which can affect the functionality and then you see new errors. And as I type this I thought of one other thing that can contribute to that issue - if the table doesn't have a primary key.
But I would just search on the error message and see what you find.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply