Solved

Error - "Invalid use of Null"

Posted on 2014-03-06
7
817 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

733 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