locked
Need help passing "NULL" from VB.NET to SQL parameter when a string is empty RRS feed

  • Question

  • User-171414861 posted

    I'm using a function in VB.NET to pass values to parameters to call a stored procedure and return a DataTable.

    The stored procedure is expecting either a null  or single character to determine whether or not to set a lock

    on a record.

    Up until now, I have been testing for a single user permission to determine what to pass to the stored procedure.

    If the user has the permission, a character is passed and the record is locked by the stored procedure.

    If the user does not have the permission, then 'NULL' is passed and the record is not locked by the sproc.

    This works fine when only testing for one permission, however, I have situations coming up where I have to test

    for multiple permissions before passing a value to the stored procedure.

    I tried using a string variable and either passing a character if the user had the permission or passing an empty string

    if they did not have the permission.

    This didn't work because I was unable to turn the empty string into a null to pass to the parameter.  So I am stuck in trying to find a way

    to pass NULL when I have to test for multiple permissions.

    Dim lockRequested As String = ""

                    Select Case mSession_BreakoutStatus

                        Case "Unconfirmed"
                            If Not (mUser.Permissions.Contains("create_modify_breakout")) Then
                                lockRequested = "T"
                            End If

                        Case "Initiated"
                            If Not (mUser.Permissions.Contains("submit_breakout_qtys")) Then
                                lockRequested = "T"
                            End If

                        Case "Submitted"
                            If Not (mUser.Permissions.Contains("confirm_breakout")) Then
                                lockRequested = "T"
                            End If
                    End Select

                    Dim detailsTable As DataTable = _
                        mDataAccess.GetBreakoutDetails(
                            mCommand,
                            BreakoutKey,
                            lockRequested,
                            mUser.UserId,
                            Session.SessionID)

                    ItemLockObtained = CBool(detailsTable.Rows(0).Item("locked_obtained"))

    Any advice would be appreciated.

    Wednesday, December 19, 2012 3:32 PM

All replies

  • User-1590642642 posted

    If the string is empty, then don't pass the parameter at all, or set its value to dbnull.value.

    Wednesday, December 19, 2012 3:46 PM
  • User-236004499 posted

    Hi,

    In Your stored procedure the parameter you want it to be null. Declare like this

    @MyFiled nvarchar(10) = Null

    Now at VB code behind if value is blank then don't add that parameter to the command object.

    In Stored procedure specifying parameter with default value Null means this is optional parameter i.e. if you ignore in VB/C# will not throw error otherwise it will throws error.

    Thanks

    Pratik

     

    Wednesday, December 19, 2012 7:16 PM
  • User1999579388 posted

    This didn't work because I was unable to turn the empty string into a null to pass to the parameter. 

    Hi,

    Where do you turn the empty string into a null to a parameter? Would you please tell us more information about your stored procedure ? It will help us to help you sovle your problem. Thanks a lot.

    Best Regards,

    Friday, December 21, 2012 3:51 AM
  • User-2029400242 posted

    You could overload IsNull to return a specific value if your value is empty/nothing.

    Public Overloads Function IsNull(ByVal ValueIn As Object, ByVal ValueOut As Object) As Object  
    If ValueIn Is Nothing Then Return ValueOut Else Return ValueIn End If End Function

    IsNull(myString,DbNull.Value.ToString)

    IsNull(myBool, DbNull.Value)

    Wednesday, April 18, 2018 6:00 PM
  • User423816433 posted

    You can pass like this:

    New SqlParameter("@Value", SqlDbType.NVarChar) With {.Value = IIf(IsNothing(value), DBNull.Value, value)}

    If string is empty then you can check :

    instead of isnothing, you can check String.IsNullOrEmpty(value)
    Monday, May 6, 2019 4:32 PM
  • User364663285 posted

    Use isnull, in the command/statement like

    IsNull(Max(EmployeeDesignation),0) 
    Tuesday, May 7, 2019 8:47 AM