CODEWITHSUNDEEP
sql-serverstored-proceduresparameters
sevastopol
sevastopol

Reputation: 3

Can't return varchar value from stored procedure in asp.net

I am trying to return a varchar from stored procedure with the following code:

Dim uname As SqlCommand = New SqlCommand("userName", uconnect)
uname.CommandType = CommandType.StoredProcedure
Dim outVal As SqlParameter = uname.Parameters.Add("userName", SqlDbType.VarChar)
uname.Parameters.AddWithValue("userID", userLog)
uname.Parameters.AddWithValue("@userName", " ")
outVal.Direction = ParameterDirection.Output
uname.ExecuteReader() 'This line trows exception saying: 
' "String[1]: the Size property has an invalid size of 0."

uconnect.Close()
Dim name As String = outVal.Value

Here is SP code:

ALTER PROCEDURE [dbo].[userName] 
 -- Add the parameters for the stored procedure here
 @userID int,
 @userName varchar(30) OUTPUT
AS
   SELECT @userName = owner FROM users WHERE user_id = @userID

I also tryied setting the size of the returned varchar parameter like this:

uname.Parameters.Add("userName", SqlDbType.VarChar, 30)

but if I add size parameter to the line above, when I run a program it trows a new exception saying: "Stored Procedure UserName has too many arguments specified".

Upvotes: 0

Views: 1598

Answers (2)

Alan Draper
Alan Draper

Reputation: 1

If your code is really that simple, then it's probably easier to just select the result and execute scalar.

Dim uname As SqlCommand = New SqlCommand("userName", uconnect)
uname.CommandType = CommandType.StoredProcedure
uname.Parameters.AddWithValue("userID", userLog)

Dim name As String
name = uname.ExecuteScalar()
uconnect.Close()

With the sproc like this:

ALTER PROCEDURE [dbo].[userName] 
 -- Add the parameters for the stored procedure here
 @userID int,
AS
   SELECT owner FROM users WHERE user_id = @userID

Note: I'm a c# programmer, so sorry if this isn't perfect.

Upvotes: 0

Dexter
Dexter

Reputation: 18452

SqlParameter.Add has a number of overloads, but the one you need to use in this situation is the form you tried with the column size:

uname.Parameters.Add("userName", SqlDbType.VarChar, 30)

The error you're getting is because you've declared the userName parameter twice - once as userName, and again as @userName - this causes three parameters to be sent to Sql Server, when your stored procedure only accepts two. Remove this line and all will be well:

' Remove this line
uname.Parameters.AddWithValue("@userName", " ")

Upvotes: 1

Related Questions