Need Help With Output Parameter and Microsoft ApplicationBlocks SplHelper.ExecuteNonQuery RRS feed

  • Question

  • Hi fellow members. I am having a big problem. I was recently introduced to Microsoft application blocks. I love it, but i am having a big problem now. I have a stored procedure in sql server 2000 called "IsSiteUserBanned". It checks a table that has user information for a website, consisting of a field named "user_banned". I have provided the T-SQL for my stored procedure below:

    Code Snippet

    CREATE   procedure IsSiteUserBanned @user_id bigint, @status varchar(3) output


    select @status = user_banned
    from SiteUsers
    where user_id = @user_id


    As you can see, i have an output parameter named "@status" that will return the current value of the "user_banned" field for the selected user, which can either be "YES" or "NO". Running this in the SQL Query Analyzer confirms that the stored procedure is doing what I want it to do.

    Now, i am using the SqlHelper.ExecuteNonQuery method to run my stored procedure and get the value of the @status parameter. Here is my VB.NET code below:

    Code Snippet

    Public Shared Function IsSiteUserBanned(ByVal _UserID As Integer) As Boolean
            Dim parArray(2) As SqlParameter
            'Dim parUserStatus As New SqlParameter("@status", SqlDbType.VarChar, 3, ParameterDirection.Output)
            'Dim parUserID As New SqlParameter("@user_id", SqlDbType.BigInt, 8, ParameterDirection.Input)

            parArray(0) = New SqlParameter("@user_id", SqlDbType.BigInt, 8, ParameterDirection.Input)
            parArray(0).Value = _UserID

            parArray(1) = New SqlParameter("@status", SqlDbType.VarChar, 3, ParameterDirection.Output)

            SqlHelper.ExecuteScalar(ConfigurationManager.AppSettings("DBConnection"), Data.CommandType.StoredProcedure, "IsSiteUserBanned", parArray)

            If parArray(1).Value = "NO" Then
                Return False
                Return True
            End If
        End Function

        Public Shared Function GetSiteUserStatus(ByVal _UserID As Integer) As String
            If Not IsSiteUserBanned(_UserID) Then
                Return "Active"
                Return "Banned"
            End If
        End Function

    When i run the above code, i get an error and the code halts at this line:

    Code Snippet

    If parArray(1).Value = "NO" Then

    The error message says:
    Code Snippet

    System.InvalidCastException: Operator '=' is not defined for type 'DBNull' and string "NO".

    I cant understand why i am getting this message. There is no NULL value in the "user_banned" field of the SiteUsers table. Can someone please assist me. I am frustrated. I have been trying to debug for 2 hours. I give up! I am going to bed. Please help me guys with this problem. Thank you in advance.

    Thursday, August 16, 2007 9:12 AM

All replies

  • I believe you need to call ExecuteNonQuery, not ExecuteScalar, since no scalar value returned from the query. Try to use type of the parameter as InputOutput.

    If you wish to use ExecuteScalar, you could change your query inside of SP to return result without parameter like




    and remove @result parameter completely from the SP. It will return scalar value from the SP and you could use ExecuteScalar method, but to get result back you probably need to get result of the helper function


    Thursday, August 16, 2007 10:23 AM