recordset issue

[toon10]

I have a very odd problem with some SQL.

If I use the following code:

“SQL2 = "SELECT Operations.OpID, OpDetail.OpNoID, OpDetail.SequenceNo From Operations, OpDetail WHERE Operations.OpID = OpDetail.OpNoID AND (Operations.PartNoID = '" & varPartNo & "') ORDER BY OpDetail.OpNoID, OpDetail.SequenceNo"
rs2.Open SQL2, db, adOpenStatic, adLockBatchOptimistic
rs2.MoveFirst

I get an error 3021 Either BOF or EOF is True on the rs2.MoveFirst line. (I have checked that the value in varPartNo is valid.)

If I copy and paste the exact code into the DataEnvironment and replace the varPartNo with a parameter value, it works in the designer no problem.

So, knowing that it works in the designer, I decided to use an alternative way of executing the command.

DEInstance1.Commands("cmdGetNewID").Parameters(0) = varPartNo
Set rs2 = DEInstance1.Commands("cmdGetNewID").Execute
rs2.MoveFirst

Once again though, despite the actual command working (and I’ve tested the fields output), as soon as the code hits the rs2.MoveFirst line, I get the same 3021 error. I use SQL a lot within my apps and have not come across this problem on a simple movefirst statement. Is there anything obviously wrong here? I am declaring and setting my recordset and variables properly.

Thanks

 

[taupirho]

I'm guessing here but the varpartno id in your code is not containing what you think it is. Maybe something like a extra space at its end. Stop your code on the rs2.movefirst line and display the SQL2 string that your trying to exceute. All should become clear.

 

[toon10]

Yeah, you're right. I've got it sorted. Thanks