Retrieving Output Parameters

[Jim C]

I am using the following stored procedure in SQL called spGetUserName

ALTER Procedure spGetUserName
As
-- declare variables
set nocount on
DECLARE @name varchar(30)
,@lenindex int
,@lenall int
,@username varchar(10)
-- return the user logged into the SQL server
SELECT @name = suser_sname()
SELECT @lenall = Len(@name)
SELECT @lenindex = CHARINDEX('\', @name)

-- clean up the variables and return a field named NTUserName
SELECT SUBSTRING(@name, @lenindex+1, @lenall-@lenindex) AS NTUserName
return

Then I am retrieving this using the ADO append parameter method

Function GetUserName()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim strUser As String

Set cmd = New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "spGetUserName"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("@NTUserName", adVarChar,
adParamOutput, 10)
cmd.Parameters.Append prm
cmd.Execute
strUser = cmd.Parameters("@NTUserName")
Set cmd = Nothing
End Function

When I run this function I get the following error message
spGetUserName ha no parameters and arguments were supplied.


I am trying to get the value of the logged on person in code. Any
clues?

 

[Rod Scoullar]

Jim,

Parameters passed to a stored procedure appear between the procedure name
and the AS keyword.

The must be surrounded by brackets ().

eg

ALTER Procedure spGetUserName
(
@name varchar(30),
@leindex int,
@lenall int,
@username varchar(10)
)
AS
....

I hope this helps (and that others add extra details if needed.)

Rod Scoullar.