Returning A Value from a single function

S

sean

HI,

I would like to know if I can some set a variable to return a single value
from a function using the set command. Is this possible?


Thanks in advance for your answer

Sean

SET test = ReturnValue(OptionID)

Function ReturnValue(ByVal OptionID)

Set adoCmd = Server.CreateObject("ADODB.Command")
adoCmd.ActiveConnection = db_conn
adoCmd.CommandType = adCmdStoredProc
adoCmd.CommandText = "sp_findoption"
adoCmd.Parameters.Append adoCmd.CreateParameter("pOptionID",adInteger)
adoCmd.Parameters("pOptionID") = OptionID
Set adoRec = adoCmd.Execute()
OptionValue = adoRec(1)
adoRec.close Set
adoRec = Nothing
Set adoCmd = Nothing

End Function
 
R

Ray at

Have you tried it? Should be fine, although if you're just returning a
value from a recordset, which will be a string or a numeric, don't use SET.
Just do:

test = ReturnValue(OptionID)

Ray at home
 
B

Bob Barrows

sean said:
HI,

I would like to know if I can some set a variable to return a single
value from a function using the set command. Is this possible?


Thanks in advance for your answer

Sean

SET test = ReturnValue(OptionID)
Unless you are intending to return an object from your function, do not use
the "SET" keyword.
Function ReturnValue(ByVal OptionID)

Set adoCmd = Server.CreateObject("ADODB.Command")
adoCmd.ActiveConnection = db_conn
adoCmd.CommandType = adCmdStoredProc
adoCmd.CommandText = "sp_findoption"
adoCmd.Parameters.Append adoCmd.CreateParameter("pOptionID",adInteger)
adoCmd.Parameters("pOptionID") = OptionID
Set adoRec = adoCmd.Execute()
OptionValue = adoRec(1)
adoRec.close Set
adoRec = Nothing
Set adoCmd = Nothing

End Function

This "function" does not return anything. To return a value from a function,
you assign the value you wish to return to the name of the function within
the function code. I'm guessing that you want to return the value contained
in the second field of the recordset returned by the stored procedure -
adoRec(1) - which makes me wonder why your stored procedure is returning
more than one field ...

Anyways, to return that value, do this instead:
ReturnValue = adoRec(1)

HTH,
Bob Barrows
PS. I would consider using an output parameter in your stored procedure to
return that single value. Using a recordset is overkill
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,905
Latest member
Kristy_Poole

Latest Threads

Top