Solved

Error - "Invalid use of Null"

Posted on 2014-03-06
7
815 Views
Last Modified: 2014-03-11
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
0
Comment
Question by:tesla764
7 Comments
 
LVL 25

Accepted Solution

by:
SStory earned 166 total points
ID: 39909621
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
 
LVL 28

Assisted Solution

by:Ark
Ark earned 167 total points
ID: 39911538
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
 

Author Comment

by:tesla764
ID: 39913616
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 167 total points
ID: 39914147
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
 

Author Comment

by:tesla764
ID: 39921881
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
 

Author Closing Comment

by:tesla764
ID: 39921882
Thanks everybody these are all very helpful solutions
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question