Can't get a string from SQL query

[Andrew Morton]

I've got a stored procedure which works as expected using Run Stored
Procedure in VS.NET:-
-----------------------------------------------------------
ALTER PROCEDURE dbo.ValidateUser
@Username nVarChar(50),
@Password nVarChar(24),
@Valid nvarchar(36) OUTPUT
as
Select @Valid=cast(UserID as nvarchar(36)) From userdata
Where Username=@Username
and Password=@Password
-----------------------------------------------------------

UserID is a uniqueIdentifier.

But I can't get a value from @Valid in VB.NET using an output paramater,
it's always an empty string:-
-----------------------------------------------------------
Private sqlConnectionString As String =
ConfigurationSettings.AppSettings("sqlConnectionString")

Public Function validateUser(ByVal nam As String, ByVal pwd As String) As
String
Dim sqlCmd As New SqlCommand("validateUser", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

Dim sqlParam As New SqlParameter("@UserName", SqlDbType.NVarChar)
sqlParam.Value = Trim(nam)
sqlCmd.Parameters.Add(sqlParam)

sqlParam = New SqlParameter("@Password", SqlDbType.NVarChar)
sqlParam.Value = Trim(pwd)
sqlCmd.Parameters.Add(sqlParam)

Dim sqlParamUserID As New SqlParameter("@Valid", SqlDbType.NVarChar)
sqlParamUserID.Value = ""
sqlParamUserID.Direction = ParameterDirection.Output
sqlCmd.Parameters.Add(sqlParamUserID)

Try
If sqlConn.State <> ConnectionState.Open Then
sqlConn.Open()
End If
sqlCmd.ExecuteReader()
Catch e As Exception
Throw e
End Try

sqlConn.Close()

Return CType(sqlParamUserID.Value, String)
End Function
-----------------------------------------------------------

However, if I change the stored procedure to return an int (e.g. select
@Valid=count(*) ), and change the VB to expect a number then it does work.

What am I doing wrong?

Andrew

 

[Andrew Morton]

ALTER PROCEDURE dbo.ValidateUser
@Username nVarChar(50),

Dim sqlParam As New SqlParameter("@UserName", SqlDbType.NVarChar)

Solution: use the size parameter, as in
Dim sqlParam As New SqlParameter("@UserName", SqlDbType.NVarChar, 50)

Andrew