Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Error - "Invalid use of Null"

Posted on 2014-03-06
7
Medium Priority
?
855 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This video teaches viewers about errors in exception handling.
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 …
Suggested Courses

647 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