I had a hell of a time tracking down a problem that was occurring on a remote web service/web server today and finally came to this. Hopefully, if this helps someone else in the future, then my hours won’t have been totally wasted.
I have a webservice that is required to return a recordset (this is an old app that I wrote a lot about in the past) and has been in production for 9 months with no problems.
Since it had been written and deployed in aspnet 1.0, I just left it alone although all of my later apps were in 1.1. The application had a windows form front end and a web service backend. I finally updated the webserver at my client site to 1.1 and it broke that application.
I tracked down the problem finally to the ADODB wrapper, finally noticing that the ADODB.dll copied to my webservice was from 2/1/02 and the one on my development machine was 4/1/03. That still didn’t do the trick (grumble)
I had originally used some ASP style method for the parameters – since I have to use ADO to deal with the recordset, not ADO.NET which basically worked like this:
Dim oprm As New ADODB.Parameter
oprm = oCMD.Parameters.Item(1) ‘sampleid
oprm.Value = SampleID
Dim oprm2 As New ADODB.Parameter
oprm2 = oCMD.Parameters.Item(2) ‘testenum
oprm2.Value = testenum
Now the application was crapping out at “oCMD.Parameters.Item(1)” telling me that the object variable was not set.
This was working perfectly fine on my development machine against my w2k3, IIS6 and SQL2000 but NO LONGER WORKING on my client’s W2000 server, IIS5 and SQL7.
Finally after a lot of testing, watching sql profiler, and general state of aggravation, I found that this worked:
Dim pid = oCMD.CreateParameter(“sampleid”, ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 4, SampleID)
oCMD.Parameters.Append(pid)
Dim paramtest = oCMD.CreateParameter(“testenum”, ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 4, testenum)
oCMD.Parameters.Append(paramtest)
This finally gave me success. I’m sure if I had realized exactly what the problem was I could have found information via google or msdn, but the real problem was pinpointing the problem.