Cant access ado return parameters when populating recordset.. 3

[matthewking]

Hi,

I've noticed that you can only retrieve ado parameters with direction adParamOutput when executing the command object e.g.
objCommand.Parameters.Append .CreateParameter("@Return", adInteger, adParamOutput)

objCommand.Execute

Response.Write(objCommand.Parameters("@Return&quot.Value)

if you do Set objRecordset = objCommand.Execute

you can no longer read the return values..Anyone know why? and/or any alternatives or clean ways around this problem..

thanks

matt.

 

[mwolf00]

hmm, I guess I've never tried to use both at the same time - I return all the needed values in the recordset if I need values returned other then just RETURN parameter. However, you could probably do something like....

Set objCmd.ActiveConnection = objConn
with objCmd
.commandText = "br_sp_templateComplete"
.CommandType = adCmdStoredProc
.parameters(0).direction = adParamReturnValue
.parameters(1) = tempID
.parameters(2) = "tbsTmpltProjNew"
.parameters(3) = personID
.parameters(4) = null
End With

set rs = objCmd.execute

returnVal = objCmd.parameters(0)



Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[JamesLean]

When returning a recordset and parameters, you have to close the recordset before you can get access to the parameters.

In situations where you need to Response.Write the parameter values before you output the recordset, I tend to use .GetRows or .GetString to get the recordset into an array or string, which enables me to close the RS, use the parameters and then output the RS data to screen.

--James

 

[mwolf00]

Nice to know James, have a star...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[matthewking]

thanks mwolf00 for your sugesstion.

and thanks very much JamesLean for clearing up why I cant access the return values, nice solution.