Classic ASP - Comm object sometimes doesn't return data and I have to requery

[GriffMG]

I have an application where I use a comm object to extract data from a table and set the result to a recordset

		strSQL = "SELECT * FROM myTable  where myTable.NameRef+','$? and myTable.Nameref<>'________' order by NameRef"
		cmd.CommandText = strSQL
		cmd.Parameters.Append cmd.CreateParameter("@A", 201, 1, Len(strExisting), strExisting)
		SET rsPopNM = cmd.execute()

In the code above rsPopNM already is a recordset and the strExisting variable is a (possibly lengthy) string of NameRef items separated by commas
cmd is a command object already open on a connection, it has to be because it was just used to assemble strExisting.

This code works very well 4 times out of 5, on the fifth(ish) attempt it doesn't return a recordset I can read - I can't get rsPopNM("NameRef") and I get
a 80020009 error in the server log. After much experimentation I have found that if I issue a requery it always works, I encapsulate that here:

			iRow = 0
			strNameRef = ""
			While Not rsPopNM.EOF and strNameRef = "" and iRow < 20000
				iRow = iRow +1
				ON ERROR RESUME NEXT
				strNameRef = rsPopNM("NameRef")
				ON ERROR GOTO 0
				IF strNameRef = "" then
					rsPopNM.Requery
				END IF 
			wend

Does anyone know why I need to do this? Is there a better way to test the recordset for quality?


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

I have found the problem and a solution...

I was reusing the cmd variable, thus probably appending a second parameter to it!

Why this seemed to work most of the time I do not know, but all I had to do was set it to nothing and redeclare it

	    Set cmd         = Nothing
	    Set cmd               = Server.CreateObject("ADODB.Command")
	    Cmd.activeconnection  = cnMyConnection
	    cmd.commandtype       = 1

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​