Anyone use the VFP OLEDB interface? I have an odd issue...

[GriffMG]

I use the OLEDB interface to allow internet access to a VFP database, the server is classic ASP.

Sometimes I use a comm object to extract data from a table and set the result to a recordset (because I need to parameterise the query because an element can be very large)

		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?
I have asked this on the windows server asp forum, but there doesn't seem to be anyone there!


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. ​

 

[Chris Miller]

I never thought about this. When VFP is a client using an OLEDB provider, I use cursoradapter and its code just generates an ADODB.Recordset. That has an ActiveCommand as property used to fill this recordset and then create the VFP cursor from it. And how that's done is all behind the scenes in the default behavior of CursorFill(). "Active"Command hints towards using one command object per query.

I can't see anything wrong with reusing a command, though. If parameter names differ, the parameter used for the first query will play no role for the second one. Obviously, it's a good idea to always start with a new ADODB.Command object.

Chriss

 

[GriffMG]

Hi Chris

The *VFPOLEDB* doesn't support naming the parameters, they are just based on order... you can use a name, but if they aren't accessed in the right order, the driver gets p*ssed.

Reusing is good, but not if you want to use parameters, as I learnt today!

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. ​