Hi All,
    I am using Coomand object. But I am geeting One error. Can you please suggest me, what could be the wrong in my code.

'Asp Code
-------------------------------------------------------
Dim objCmd
set objCmd = server.CreateObject("ADODB.Command")
objCmd.CommandText ="GETEMPLOYEENAME"
objCmd.CommandType ="AdcmdStoredProc"

objCmd.ActiveConnection = "Provider = SQLOLEDB.1; Persist Security Info = False; User Id = sa; Password=007; Initial Catalog=sample; Data Source =user-37; Use Procedure For Prepare=1; Auto Translate = True; Packet Size = 4096;"
with objCmd
    .Parameters.Append objCmd.CreateParameter("@EMPNO",adInteger,adParamI nput,4,1)
    .Parameters.Append objCmd.CreateParameter("@EMPLOYEENAME",adVarChar,a dParamOutput,20)
end with
objCmd.Execute
Dim Name
Name =objCmd.Parameters.item("@EMPLOYEENAME")
Response.Write Name
----------------------------------------------------------
Stored Procedure
--------------------------------------------------------
CREATE PROCEDURE GETEMPLOYEENAME(@EMPNO INT, @EMPLOYEENAME VARCHAR(20) OUTPUT) AS

BEGIN

    SELECT @EMPLOYEENAME = ENAME FROM EMPLOYEE WHERE EMPNO=@EMPNO

END
RETURN
--------------------------------------------------------------------
ERROR:Procedure 'GetEmployeeName' expects parameter '@EMPNO', which was not supplied.

-----------

But I am sure that i am passing correct parameter to the Sp.

Please look into this and get back to me..
Thanks
Suresh Babu

Hey,

Shouldn't objCmd.CommandType ="AdcmdStoredProc" be objCmd.CommandType =AdcmdStoredProc? Other than that, I have no clue.

Brian

Hi,
  Thanks a lot for your reply.That is the exact mistake i did. Now i am getting correct results. By the by now i want to get return value from the user defined function. I am posting my code and sp and the error. Can u please find outh error..
Once again thanks a lot for your help..
Suresh Babu

This is the code
-------------------------------------------------------
Dim objCmd
    set objCmd = server.CreateObject("ADODB.Command")
    objCmd.CommandText ="returnEmployeeName"
    objCmd.CommandType = adCmdStoredProc

    objCmd.ActiveConnection = "Provider = SQLOLEDB.1; Persist Security Info = False; User Id = sa; Password=007; Initial Catalog=sample; Data Source =user-37; Use Procedure For Prepare=1; Auto Translate = True; Packet Size = 4096;"
    with objCmd
        .Parameters.Append objCmd.CreateParameter("@EMPNO",adInteger,adParamI nput,4,4)
        .Parameters.Append objCmd.CreateParameter("@x",adVarChar,adParamRetur nValue,20)

    end with
    objCmd.Execute
    Dim Name
    Name =objCmd.Parameters.Item("@X")
    Response.Write err.description
----------------------------------------------------------
Function
----------------
CREATE function returnEmployeeName (@EMPNO INT) returns varchar(20) As
begin
declare @x varchar(20)

SELECT @x= ENAME FROM EMPLOYEE WHERE EMPNO=@EMPNO

return @x
end


-----------------------------------------------------
Error:

Procedure or function returnEmployeeName has too many arguments specified.


I need your reply..

Hey,

One, I didn't know you could call functions when you declare adCmdStoredProc... is that correct?

Second, you have one param in the function, but create two params:

.Parameters.Append objCmd.CreateParameter("@EMPNO",adInteger,adParamI nput,4,4)
.Parameters.Append objCmd.CreateParameter("@x",adVarChar,adParamRetur nValue,20)

I don't think because @x is a variable in the function that you can do that... I think you may be able to define a @RETURN_VALUE parameter to do that... Not sure, didn't mess with calling functions.

Brian

Hi,
   can u suggest me what to use at commandtype and i am creating a returnvalueParam to get the return value.
i dont know what to do...
can u suggest me in this...
thanks
suresh

Hey,

Don't know. At worst case, you could have a stored proc that calls the function, and the stored proc returns the results....

Brian

Sureshbabu,

If I understood that right, you should use OUTPUT parameter within stored proc to return value. Not "return @x" will work for you in that case. Normally SPs output the values that are used in SELECT list or OUTPUT parameters. You cannot capture the value used with RETURN keyword.

Check BOL for output parameters in stored procedures.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi vijay,
      Actually I want to find the exact scenario where i can use "adretunparam" in the command object. For that i have choosen user defined function instead of stored procedure. What I beleive is for functions we have to use return statements. Now I want the return parameter in my ASP. How can I get it? can u suggest me
Thanks for your Reply.
Suresh babu

Sorry about my previous post, that was my bad. Somehow I was focussed on OUTPUT parameter of SP.

Take a look at this - this should be what you are looking for.

Using Return Values from a SQL Server Stored Procedure to Customize Error Messages - Actually this uses return keyword in returning values from SP to customize error message. In your case, you should be able to get @x using You can also have a look at this to know how output param of stored proc is handled.

Stored Procedure OUTPUT param usage in ASP

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection