Am I using the Command/Recordset object correctly

[BG12424]

Is the way that I am
1) getting the return value,
2) verifying it,
3) then opening a recordset if the return code is successful,
4) returning resultset

the best way to do this? I am concerned with the cmd.Execute and the Set rs = cmd.Execute (calling it twice).


Thanks

function WV_GetQuotePortfolio(userid)
' replace qry_WVGetQuotePortfolio.cfm

dim cmd, rs, dbConn, retval

strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=flibble;Initial Catalog=WV_DEV;Network Address=GAINES-XE3-W2K"

Set dbConn = Server.CreateObject("ADODB.Connection&quot
dbConn.Open strConn

Set cmd = Server.CreateObject("ADODB.Command&quot
with cmd
.ActiveConnection = dbConn
.CommandText = "WVGetQuotePortfolio"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,adParamReturnValue,4)
.Parameters.Append .CreateParameter("@userID",adInteger,adParamInput, ,userid)
.Execute
retval = .Parameters("RETURN_VALUE&quot
end with

if retval = 1 then
Response.Write("Invalid User&quot
else
Set rs = Server.CreateObject("ADODB.Recordset&quot
Set rs = cmd.Execute
do while not rs.EOF
Response.Write(rs(&quot;contract&quot & &quot;<br>&quot
rs.MoveNext
loop
CloseRS(rs)
end if

CloseCmd(cmd)
CloseDBConn(dbConn)

end function regards,
Brian

 

[GabeC]

Why not change your first

.Execute

code to

Set rs = Server.CreateObject("ADODB.Recordset")
Set rs = cmd.Execute

and remove it from the

if else then

logic?

Thanks,

Gabe

 

[BG12424]

I tried this before and was not able to get the return value back from the stored procedure. From what I have read, You can't get the return value when using the

Set rs = cmd.Execute

The recordset would have to be closed, or the rs.nextrecordset would have to be called in order to access the return value. This was a work-around, but I'm not sure if it has performance impacts.

Thanks,
Brian regards,
Brian