Array value lost out of Loop

[HezMac]

Hi

I'm trying to pass an array to a stored procedure in PL/SQL. I'm having an issue with the following sub, when I'm in the loop, I can read the value of the COArray (by hovering over it), but when I try to pass it to the module, the subscript is out range. Any ideas?

Here's the sub:

Private Sub cmdSubmitReassign_Click()

Dim COArray() As String
Dim i As Long

For i = 0 To lstReassign.ListCount - 1
ReDim Preserve COArray(i)
COArray(i) = D_Combo_Code(2, lstReassign.ItemData(i))
Next


Call ReassignIN(A_Combo_Code(cboSource.ListIndex), _
COArray(i), _
B_Combo_Code(cboFolderType.ListIndex), _
C_Combo_Code(cboFolderStatus.ListIndex), _
txtBalGreater.Text)

End Sub

Thanks for any suggestions.

 

[vladk]

Did you compare index values inside and outside of your loop? It used to get incremented in older languages. I didn't test your case.

vladk

 

[DrJavaJoe]

When you call ReassignIN you are passing COArray(i) where i is now = to lstReassign.ListCount which is too big because ubound(COArray) is = to lstReassign.ListCount - 1. The last time you iterated the for loop you set i to lstReassign.ListCount which told the for loop to stop. Make since?



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[jebenson]

You are getting the out of range error because the For loop increment variable (i) is one greater than the array's upper bound after the For loop has finished executing. For example, if you have the following:

Dim f() As Integer

For g = 0 To 10
ReDim Preserve f(g)
f(g) = g
Next g

MsgBox f(g)

g will increment from 0 to 11, and when it hits 11 the loop will exit. When the MsgBox line is executed, g has a value of 11 which throws an out of range error.

When passing an array as a parameter, it is done without any reference to a particular index. Try just putting the array's name - COArray - instead of the array with an index - COArray(i).

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[HezMac]

vladk: I'm not sure how to compare index values, but when I hovered over the COArray while I was in the loop, I got the right value. When I was sending the parameters to ReassingIN, I got the out of range error.

I fixed the code as suggested, jebenson, but get the same error (on the whole call)

Private Sub cmdSubmitReassign_Click()

Dim COArray() As String
Dim i As Long

For i = 0 To lstReassign.ListCount - 1
ReDim Preserve COArray(i)
COArray(i) = D_Combo_Code(2, lstReassign.ItemData(i))
Next


Call ReassignIN(A_Combo_Code(cboSource.ListIndex), _
COArray(i), _
B_Combo_Code(cboFolderType.ListIndex), _
C_Combo_Code(cboFolderStatus.ListIndex), _
txtBalGreater.Text)

End Sub

Still not sure how to fix it.

 

[HezMac]

Sorry, that should read:

Private Sub cmdSubmitReassign_Click()

Dim COArray() As String
Dim i As Long

For i = 0 To lstReassign.ListCount - 1
ReDim Preserve COArray(i)
COArray(i) = D_Combo_Code(2, lstReassign.ItemData(i))
Next


Call ReassignIN(A_Combo_Code(cboSource.ListIndex), _
COArray, _
B_Combo_Code(cboFolderType.ListIndex), _
C_Combo_Code(cboFolderStatus.ListIndex), _
txtBalGreater.Text)

 

[HezMac]

Now, I think the error might be where I send the array to the stored procedure.

Getting the error 3001 - arugments are of the wrong type..... on the following line:

Set PrmDestinationCO = CmdR.CreateParameter("in_DestinationCO", adArray, adParamInput, , DestinationCO)


in the following:


Public Function ReassignIN(SourceCO As String, _
DestinationCO As Variant, _
FolderType As String, _
Status As String)

Dim ConnR As ADODB.Connection
Dim CmdR As ADODB.Command
Dim PrmSourceCO As New ADODB.Parameter
Dim PrmDestinationCO As New ADODB.Parameter
Dim PrmFolderType As New ADODB.Parameter
Dim PrmStatus As New ADODB.Parameter
Dim PrmBalGreat As New ADODB.Parameter

'Open connection
Set ConnR = New ADODB.Connection
With ConnR
.ConnectionString = ConnectionString$
.CursorLocation = adUseClient
.Open
End With

'Open command object
Set CmdR = New ADODB.Command
With CmdR
Set .ActiveConnection = ConnR
.CommandText = "PackageCall.P_POPULATE_REASSIGN"
.CommandType = adCmdStoredProc
End With

'get parameter value and append parameter
With CmdR
Set PrmSourceCO = CmdR.CreateParameter("in_SourceCO", adChar, adParamInput, 10, SourceCO)
Set PrmDestinationCO = CmdR.CreateParameter("in_DestinationCO", adArray, adParamInput, , DestinationCO)
Set PrmFolderType = CmdR.CreateParameter("in_FolderType", adChar, adParamInput, 2, FolderType)
Set PrmStatus = CmdR.CreateParameter("in_Status", adChar, adParamInput, 200, Status)
Set PrmBalGreat = CmdR.CreateParameter("in_BalGreat", adChar, adParamInput, 200, BalGreat)

.Execute
End With

End Function

 

[jebenson]

I think you're getting that error because there is no value for the second-to-last parameter. It appears that the CreateParameter function is looking for a numeric value there, and it is not an optional parameter.





I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson