Type mismatch in array, occasionally

[PRMiller2]

I have adapated code found at

http://www.vb123.com.au/kb/index.html?200009_br_adod.htm.

The purpose of the functions in question is to build an array of paramaters, pass them to a function, parse the array into an ADODB command object, then execute the query.

One of my weak areas is in arrays and thus, the request for help. The function I've posted below, varStoredProc, works for me the majority of the time. However, I found that it failed just often enough that I could discern a pattern. I discovered that, if passed an array of 5, 9, 13, or 17, the function would fail. If passed an array of 3, 7, 11, 15 or 19, it would succeed. I'm sure the pattern would repeat itself if I continued to test. Clearly there is a pattern, and the cause should be obvious. However, I think I've been looking at this too long.

Here is the function I built to test (I ReDim the array in the test to more closely duplicate my production db), with successful runs commented:

Public Function TestIt2()
    
    Dim i As Integer
    Dim intTotal As Integer
    Dim varReturn As Variant
    Dim MyArray()
    
    intTotal = 3
    '3 = Success
    '5 = Type mismatch
    '7 = Success
    '9 = Type mismatch
    '11 = Success
    '13 = Type mismatch
    '15 = Success
    '17 = Type mismatch
    '19 = Success
    
    ReDim MyArray(intTotal) As Variant
    
    MyArray(0) = "spTest"
    
    For i = 1 To intTotal
        If ((i Mod 2) = 0) Then
            MyArray(i) = adInteger
        Else
            MyArray(i) = "10"
        End If
    Next i
    
    Call varStoredProc(MyArray)
  
End Function

Here is the function in question (cDB_CONN is populated elsewhere):

Private Function varStoredProc(varParam() As Variant, Optional varReturn As Variant) As Long
On Error GoTo Proc_Err
    'Author:    Author Burton Roberts, [URL unfurl="true"]http://www.vb123.com.au/kb/index.html?200009_br_ado.htm[/URL]

    Dim cmd As New ADODB.Command
    Dim cn As New ADODB.Connection
    Dim i As Long
    Dim lngElements As Long
    Dim prm As ADODB.Parameter
    Dim strConnect As String

    varStoredProc = 0

    cn.ConnectionString = cDB_CONN
    strConnect = cn.ConnectionString
    
    lngElements = UBound(varParam()) 'Get size of array
    If lngElements > 1 Then
        If Not (lngElements Mod 2) Then 'Confirm that total number of elements are not an even number
            With cmd
                .ActiveConnection = strConnect
                .CommandText = varParam(0) 'Assumes that the first element is the name of the stored proc
                .CommandType = adCmdStoredProc
                
                'This loop obtains the stored procedure parameters and their data types, looping through the
                'array elements two at a time.  The odd indexed elements are the stored procedure parameter
                'values, and the even indexed elements are the data types for those parameters.
                For i = 0 To lngElements / 2 - 1
                    Set prm = .CreateParameter("Param" & CStr(i + 1), CInt(varParam(2 * i + 2)), adParamInput, _
                               Len(varParam(2 * i + 1)), varParam(2 * i + 1))
                    .Parameters.Append prm
                Next i
            End With
        End If
    End If
    
    'If varReturn is passed to this function, then append one more parameter of the output type to accept the
    'variable to be returned from the stored procedure.
    If Not IsMissing(varReturn) Then
        Set prm = cmd.CreateParameter(varReturn, adVarWChar, adParamOutput, 255)
        cmd.Parameters.Append prm
    End If
    
 '   cmd.Execute
    Stop
    
    'If a variable was expected, return that variable to the calling procedure.  Else, return -1 to indicate success.
    If Not IsMissing(varReturn) Then
        varStoredProc = prm.Value
    Else
        varStoredProc = -1
    End If
    
Proc_Exit:
    Set prm = Nothing
    Set cmd = Nothing
    Set cn = Nothing
    
Exit Function
    
Proc_Err:
    MsgBox Err.Number, Err.Description
    Resume Proc_Exit
    
End Function

Any assistance or suggestions would be greatly appreciated!

 

[kjv1611]

It looks like you're feeding the array both numbers and strings. You need to do one or the other, don't mix and match data formats:

For i = 1 To intTotal
If ((i Mod 2) = 0) Then
MyArray(i) = adInteger
Else
MyArray(i) = "10"
End If
Next i[/CODE]


So the above should be changed to:
For i = 1 To intTotal
If ((i Mod 2) = 0) Then
MyArray(i) = adInteger
Else
MyArray(i) = 10
End If
Next i


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Sorry didn't list in CODE brackets, that part seemed to be going haywire, wouldn't post..

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[PRMiller2]

I'm afraid that change didn't resolve the issue. Removing the quotes around the 10 in MyArray(i) = 10 made no change when running this with an intTotal of 5 -- it still generated the type mismatch. Running it with an intTotal of 3 resulted in success.

This presents a problem because I have one function that calls this with an array of 17 elements, which fails. However, another function calls it with 15 elements and it runs successfully, and testing indicates that 19 elements will also be successful. My current workaround is a bad one: I'm going to pass an extra parameter to the stored procedure (or two elements in the array) which won't be used by the sproc. Rather silly, but it will work.

I'm just confused as to why this will work for an array of 15 elements, fail for 17 elements, work for 19 elements, etc.

 

[kjv1611]

I didn't dig through the entire function yet, but another thing I'm curious of, and it may not matter, is when using the Mod function, perhaps when the Mod returns a 0 it causes issues? I mean, I wouldn't think it would, but it does seem to me that it has to be something with the values being pulled in.

Is it perhaps that it's pulling the values from what LOOKS like Numbers, but in some cases is being stored as text?

I dunno, just trying to think through possibilities.

Another thing that keeps popping up in my mind is what about the possibility that the Array usage is somehow causing some data mishaps? It shouldn't, absolutely, but perhaps you could try with using totally separate array variables rather than Redimming? Just a thought.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[PRMiller2]

The MOD function esnures that an odd number of elements is passed. If an odd number is NOT passed, it skips the WITH loop (I should actually set it to exit the function, but that's neither here nor there). Whether I call the varStoredProc function with 5 or 7 elements, it will pass the MOD test and continue on. Where it fails is at the .Parameters.Append prm statement, which is where it errors out as Type Mistmach.

That's the thing: the elements being passed are essentially duplicates. But if fails on every other odd number. That's what I'm struggling with here.