Error trying to call Oracle function in VB.NET

my_lou
01-13-2004, 04:49 PM
hey all,
here is my question. i created this Oracle function i'd like to call in my VB.NET application, but i get the following error:

"Specified cast is not valid."

First, here is the code for creating the Oracle function:

CREATE FUNCTION "ORDERITEMS" (ShippableItemID NUMBER) RETURN NUMBER is
inventory_count NUMBER;
enrollment_count NUMBER;

BEGIN
SELECT InventoryCount INTO inventory_count
FROM SHIPPABLEITEM
WHERE SHIPPABLEITEMID = ShippableItemID
AND Rownum = 1;

SELECT DISTINCT COUNT (COURSERECORD.StudentID) INTO enrollment_count
FROM COURSEINSTANCEMATERIALS, COURSERECORD
WHERE COURSEINSTANCEMATERIALS.ShippableItemID = ShippableItemID
AND COURSERECORD.CourseInstanceID = COURSEINSTANCEMATERIALS.CourseInstanceID;

IF (inventory_count < 0.5*enrollment_count) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

I tested it in SQL *Plus and it worked fine. Now then, here is the part of my VB.NET code performing the call to the above function:

DBCommand = New ADODB.Command
DBRecords = New ADODB.Recordset
DBCommand.ActiveConnection = DBLink

Dim InputParameter As New ADODB.Parameter
Dim ReturnValue As New ADODB.Parameter

ItemIDValue = DBRecords.Fields(0).Value

DBCommand.CommandText = "OrderItems"
DBCommand.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc

InputParameter.Name = "ShippableItemID"
InputParameter.Direction = ADODB.ParameterDirectionEnum.adParamInput
InputParameter.Type = ADODB.DataTypeEnum.adInteger
InputParameter.Value = ItemIDValue

ReturnValue.Name = "myReturnValue"
ReturnValue.Direction = ADODB.ParameterDirectionEnum.adParamReturnValue
ReturnValue.Type = ADODB.DataTypeEnum.adInteger

'Add parameters to ADODB command.
DBCommand.Parameters.Append(InputParameter) --> ERROR ON THIS LINE
DBCommand.Parameters.Append(ReturnValue)

Try
'Executes the command.
DBFunctionResult = DBCommand.Execute()
Catch ex As Exception
MsgBox("Failed to call Oracle function ORDERITEMS.")
End Try

If Not DBFunctionResult.EOF Then
Do Until DBFunctionResult.EOF
ReturnFlag = DBFunctionResult.Fields(0).Value
MsgBox("Function return value: " & ReturnFlag)
Loop
End If

I get the error in the line marked (DBCommand.Parameters.Append(InputParameter)). I tried setting the type of parameter InputParameter as adNumeric, but got the same error. Any ideas?

Thanks.

Optikal
01-13-2004, 05:16 PM
uhhh, is this .Net? If so you should be using ADO.Net not ADO 2.x

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum