DannyTmoov2
IS-IT--Management
I have the following stored procedure in oracle: -
I am trying to call this from VB.net into a dataset using the following function: -
But i am getting the following error on the line ".fill(ds)" :-
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: The SelectCommand property has not been initialized before calling 'Fill'.
the procedure executes fine within oracle, any help much appreciated.
Cheers,
Dan
Code:
CREATE OR REPLACE PACKAGE pkg_machines
AS
TYPE rc_machines IS REF CURSOR;
END;
/
CREATE OR REPLACE PROCEDURE sp_select_machines
(i_order_no IN tbl_orders.order_no%type,
i_profile_no IN tbl_profiles.profile_no%type,
i_start_date IN date,
i_end_date IN date,
c_machines OUT pkg_machines.rc_machines )
IS
BEGIN
OPEN c_machines FOR
SELECT DISTINCT
mac.machine_id,
ord.order_no,
prof.profile_no,
ord.picked_date - 2 AS xfactory,
ord.picked_date - 4 AS deadline_to_online,
mac.machine_status_id,
ROUND(ord.picked_date - 2 - sysdate, 0) AS variance,
mac.serial_no,
ord.postcode,
ord.order_status,
ord.client_name,
mac.date_ordered
FROM tbl_machines mac, tbl_orders ord, tbl_profiles prof
WHERE ord.order_id = mac.order_id
AND prof.profile_id = mac.profile_id
AND ord.order_no LIKE '%' || i_order_no || '%'
AND prof.profile_no LIKE '%' || i_profile_no || '%'
AND (ord.picked_date - 2) BETWEEN TO_DATE(i_start_date) AND TO_DATE(i_end_date);
END;
/
I am trying to call this from VB.net into a dataset using the following function: -
Code:
'populate machines table
Public Function addScheduleToBuildData(ByVal I_ORDER_NO As String _
, ByVal I_PROFILE_NO As String _
, ByVal I_START_DATE As Date _
, ByVal I_END_DATE As Date)
Dim ds As New DataSet()
With dbAdaptr
' create the command for the stored procedure
Dim cmd = New OracleCommand()
cmd.Connection = cn
cmd.CommandText = "sp_select_machines"
cmd.CommandType = CommandType.StoredProcedure
' add the parameters for the stored procedure including the REF CURSOR
' to retrieve the result set
cmd.Parameters.Add("i_order_no", OracleType.VarChar).Value = I_ORDER_NO
cmd.Parameters.Add("i_profile_no", OracleType.VarChar).Value = I_PROFILE_NO
cmd.Parameters.Add("i_start_date", OracleType.DateTime).Value = I_START_DATE
cmd.Parameters.Add("i_end_date", OracleType.DateTime).Value = I_END_DATE
cmd.Parameters.Add("c_machines", OracleType.Cursor).Direction = ParameterDirection.Output
.TableMappings.Add("Table", "machines")
' use dataadpater to fill the DataSet
.Fill(ds)
.Dispose()
' output the results.
Console.WriteLine(ds.Tables(0).Rows.Count)
End With
Return ds
End Function
But i am getting the following error on the line ".fill(ds)" :-
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: The SelectCommand property has not been initialized before calling 'Fill'.
the procedure executes fine within oracle, any help much appreciated.
Cheers,
Dan