ByRef Argument Type mismatch 1

[HezMac]

Hey

I'm having issues returning a value from a PL/SQL stored procedure.

I'm passing info from a vb6 front end, but not able to get the result, getting the ByRef argument type mismatch error.

Here's the code:

Private Sub cmdTestCount_Click()
Call CB_Test_Count(A_Combo_Code(cboSource.ListIndex))

'Calls the module Test Count. Here's the module code:

Public Function CB_Test_Count(SelectedUser As String)

Dim strSQL
Dim Prm_User As New ADODB.Parameter
Dim Prm_TestCount As New ADODB.Parameter
Dim Cmd As ADODB.Command
Dim GTC As ADODB.Recordset 'GetTestCount

strSQL = "{call PKG_BATCH_REASSIGN.P_TEST_COUNT" & _
"(?,{resultset 10000, TestCount})}"

Set Cmd = New ADODB.Command
Set Prm_User = Cmd.CreateParameter("in_UserID", adChar, adParamInput, 200, "XX")
Set Prm_TestCount = Cmd.CreateParameter("out_TestCount", adNumber, adParamOutput, 200, "XX")

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.ConnectionString = ConnectionString$
.CursorLocation = adUseClient
.Open
End With

'sets role
'cn.Execute RoleCommand$

With Cmd
Set .ActiveConnection = cn
.CommandText = strSQL
.CommandType = adCmdText
.Parameters.Append Prm_User

'set the value that's handed in
Prm_User.Value = SelectedUser

.Parameters.Append Prm_TestCount

.Execute
End With

'Get Test Count
Set GTC = New ADODB.Recordset
GTC.CursorLocation = 3

Set CB_Test_Count = Prm_TestCount

'cn.Close
Set cn = Nothing
Set Prm_User = Nothing
Set GTC = Nothing
Set Cmd = Nothing

End Function

Any ideas why the mismatch error? Thanks for any advice.

 

[HezMac]

Oh! Also:

The error occurs when I call the module

Call CB_Test_Count(A_Combo_Code(cboSource.ListIndex))

and A_Combo_Code is highlighted.

 

[DrJavaJoe]

The function A_Combo_Code is not returning a String data type.



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'.

 

[AndyLee100]

I say I would agree with that (easy to say now I know).

Public Function CB_Test_Count(SelectedUser As String) is expecting a string and you are not passing a string to it.

 

[AndyLee100]

or

A_Combo_Code(cboSource.ListIndex) is expecting a string and you are passing an integer to it.

 

[HezMac]

Ok - that definitely makes sense.

The way I want A_Combo_Code to work is to have it read

1. cboSource.ListIndex = 24

2. A_Combo_Code(24) = "HC_UNDER"

Then pass the "HC_UNDER" to the Stored Procedure.

Any idea on what step I'm missing?

Thanks.

 

[AndyLee100]

Can you post the A_Combo_Code code so we can get a better understanding of where you may have gone wrong?

 

[AndyLee100]

Could you do this?

CB_Test_Count(cboSource.Text).

Do you need the A_Combo_Code function?

Is the A_Combo_Code function start like this:

A_Combo_Code(i as integer) as String.

 

[HezMac]

Sure. I load a combo box with a description, and I want to pass the ID to the stored procedure.

Private Sub Combo_Load()

' Populates combo boxes when the form first loads and whenever the combo boxes need to be refreshed.

cboSource.Clear
cboDestination.Clear
Dim RS_Comboset As ADODB.Recordset
Dim X
X = 1

Set RS_Comboset = New ADODB.Recordset

'Get the new recordset
Set RS_Comboset = modGetDBInfo.CB_Get_Source()

ReDim A_Combo_Code(RS_Comboset.RecordCount)

'If RS is empty then inform the user
If RS_Comboset.EOF = True And RS_Comboset.BOF = True Then

cboSource.AddItem "No Collection Officers Available"
'cboDestination.AddItem "No Collection Officers Available"

Else ' Populate the drop down.

RS_Comboset.MoveFirst
' Add a header
cboSource.AddItem "Collection Officer/Holding Cell", 0
'cboDestination.AddItem "Collection Officer/Holding Cell", 0

Do
A_Combo_Code(X) = RS_Comboset!UserID
cboSource.AddItem RS_Comboset!UserName, X
'cboDestination.AddItem RS_Comboset!UserName, X
RS_Comboset.MoveNext
X = X + 1
Loop While Not RS_Comboset.EOF

End If

RS_Comboset.Close
Set RS_Comboset = Nothing

'sets the default index to the header item
cboSource.ListIndex = 0
'cboDestination.ListIndex = 0

End Sub

 

[DrJavaJoe]

ahhh yes A_Combo_Code is an array and I bet it is declared like this:

dim A_Combo_Code() 'bad

not

dim A_Combo_Code() as string 'good



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'.

 

[HezMac]

Yes! That worked! Thanks very much.