?
Solved

Error - "Invalid use of Null"

Posted on 2014-03-06
7
Medium Priority
?
844 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 25

Accepted Solution

by:
SStory earned 664 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 668 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 668 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
Suggested Courses

762 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