Error - "Invalid use of Null"

Any ideas on what may be causing this error?  If you need more info please let me know.
Thanks in advance.

I also attached a screen shot fron the debugger.

In the With cmd paragraph...
At the line...
intItemHeader = cmd.Parameters("iResult").Value

Open in new window

The .Value contains a NULL which I suspect is where the invalid use of NULL is coming from.

Seems to cause this error...
Error - Error Pulling from pending, Please contact the system administrator! Invalid use of Null
Which is at PULL_ERR:

Code...
Public Function MoveItemPending(strItemCode As String, intItemPendingID As Long, intShipUnitsPack As Long, intInvUnitsPack As Long, strHItemCode As String, Optional intItemHeaderID As Long = 0) As Long
    Dim cmd As New ADODB.Command
    Dim intItemHeader As Long
    
    On Error GoTo PULL_ERR
    
    With cmd
        .Parameters.Append .CreateParameter("iresult", adInteger, adParamOutput, , 0)
        .Parameters.Append .CreateParameter("iresult_string", adChar, adParamOutput, 200, "")
        .Parameters.Append .CreateParameter("iuser_id", adInteger, adParamInputOutput, , userID)
        .Parameters.Append .CreateParameter("iID_Item_Pending", adInteger, adParamInputOutput, , intItemPendingID)
        .Parameters.Append .CreateParameter("iHeaderItem_Code", adChar, adParamInputOutput, 15, Left(Trim(strHItemCode), 15))
        .Parameters.Append .CreateParameter("iItem_Code", adChar, adParamInputOutput, 15, Left(Trim(strItemCode), 15))
        .Parameters.Append .CreateParameter("iReceived", adInteger, adParamOutput, , 0)
        .Parameters.Append .CreateParameter("iUnits_Pack", adInteger, adParamInputOutput, , intShipUnitsPack)
        .Parameters.Append .CreateParameter("iInv_Units_Pack", adInteger, adParamInputOutput, , intInvUnitsPack)
        .Parameters.Append .CreateParameter("iAllocation_Code", adInteger, adParamInputOutput, , 59)
        .Parameters.Append .CreateParameter("iID_Item_Header", adInteger, adParamInputOutput, , intItemHeaderID)
    End With
    
    SQLStoredProc strDSN, "spITS_MovePendingItem", cmd
    intItemHeader = cmd.Parameters("iResult").Value
    
    'change to put hook in so that it ask to open up item maintenance at this point
    If cmd.Parameters("iResult").Value > 0 Then
        If MsgBox(Trim(cmd.Parameters("iResult_string").Value) & Chr(13) & "Do you want to Modify this Item Now?", vbYesNo) = vbYes Then
            EditItemHeader intItemHeader
        End If
        
        'return Value
        MoveItemPending = intItemHeader
    Else
        MsgBox Trim(cmd.Parameters("iResult_string").Value), vbExclamation
        MoveItemPending = 0
    End If
    Exit Function
PULL_ERR:
    MsgBox "Error Pulling from pending, Please contact the system administrator!" & Chr(13) & Err.Description, vbExclamation
    MoveItemPending = 0
End Function

Open in new window

2808---code-screen-shot.JPG
tesla764Asked:
Who is Participating?
 
SStoryConnect With a Mentor Commented:
You need code for any values that can be NULL going in or out if the DB field allows NULLS.
So when getting a field before assigning to a string for example, send it to a function called HandleNulls() that will take the parameter, check if it is NULL in the DB and if so return a string of "", or NOTHING, or if not, return a string with the actual value.

If you want to not store "" in your string, but null you need to do the reverse and when saving them pass to a function that looks at the string and if "" sets the param value to the DB NULL value.

Here is some code I have used in the past but there are many ways to do it.
   Private Function PrepareStr(ByVal strValue As String) As String
      ' This function accepts a string and creates a string that can
      ' be used in a SQL statement by adding single quotes around
      ' it and handling empty values.
      If strValue.Trim() = "" Then
         Return "NULL"
      Else
         Return "'" & strValue.Trim() & "'"
      End If
   End Function

Open in new window

0
 
ArkConnect With a Mentor Commented:
If IsNull(cmd.Parameters("iResult").Value) Then
     intItemHeader = -1 ' or whatever other value representin DB NULL value
Else
     intItemHeader =cmd.Parameters("iResult").Value
End If

Open in new window

0
 
tesla764Author Commented:
Sorry I haven't responded just yet. I will soon. I have been working on other problems that have arisen.
Thanks for you patience.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
aikimarkConnect With a Mentor Commented:
In addition to the IsNull() function, I find it useful to concatenate an empty string to variables/properties/fields that might be Null.
Example:
    With cmd
        .Parameters.Append .CreateParameter("iresult", adInteger, adParamOutput, , 0)
        .Parameters.Append .CreateParameter("iresult_string", adChar, adParamOutput, 200, "")
        .Parameters.Append .CreateParameter("iuser_id", adInteger, adParamInputOutput, , userID)
        .Parameters.Append .CreateParameter("iID_Item_Pending", adInteger, adParamInputOutput, , intItemPendingID)
        .Parameters.Append .CreateParameter("iHeaderItem_Code", adChar, adParamInputOutput, 15, Left(Trim(strHItemCode & vbNullstring), 15))
        .Parameters.Append .CreateParameter("iItem_Code", adChar, adParamInputOutput, 15, Left(Trim(strItemCode & vbNullstring), 15))
        .Parameters.Append .CreateParameter("iReceived", adInteger, adParamOutput, , 0)
        .Parameters.Append .CreateParameter("iUnits_Pack", adInteger, adParamInputOutput, , intShipUnitsPack)
        .Parameters.Append .CreateParameter("iInv_Units_Pack", adInteger, adParamInputOutput, , intInvUnitsPack)
        .Parameters.Append .CreateParameter("iAllocation_Code", adInteger, adParamInputOutput, , 59)
        .Parameters.Append .CreateParameter("iID_Item_Header", adInteger, adParamInputOutput, , intItemHeaderID)
    End With

Open in new window

0
 
tesla764Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for tesla764's comment #a39913616
Assisted answer: 167 points for SStory's comment #a39909621
Assisted answer: 167 points for Ark's comment #a39911538
Assisted answer: 166 points for aikimark's comment #a39914147

for the following reason:

Thanks all of these are great solutions.
0
 
tesla764Author Commented:
Thanks everybody these are all very helpful solutions
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.