Getting a Dataset back from Oracle into vb.net ADO??

I have similar code (a procedure and function) working perfectly with a sql server data source. I'm trying to do the same things with Oracle, but have run into the problems.

 I'm not an oracle expert, so I'm not sure if producing a cursor is my only and best option.

PROCEDURE sp_Get_Order_History(v_ani IN number, output_cursor out PK_CURSORES.generic_cursor) AS BEGIN open output_cursor for
select o.account_number, p.phone, o.amount, o.name, o.init_date from orders o
join users_phones p on p.account_number = o.account_number where p.phone = v_ani
order by Init_date desc;
end sp_Get_Order_History;

in vb.net:

Function GetOrderHistory(ByVal Phone As String) As DataSet

            If p_cnn.State = ConnectionState.Closed Then
                p_cnn.Open()
            End If

            Dim cmd As New OracleCommand
            With cmd


                .CommandType = CommandType.StoredProcedure

                .CommandText = "PK_RETAILER.sp_Get_Order_History"

                .CommandTimeout = 0

                .Connection = p_cnn

                .Parameters.Add(New OracleParameter("v_ani",

OracleType.VarChar)).Value = Phone


                .Parameters.Add(New OracleParameter("output_cursor",

OracleType.Cursor)).Direction = ParameterDirection.Output
            End With

            Dim adpt As New
OracleDataAdapter(cmd.Parameters("output_cursor").Value)
            Dim ds As New DataSet
            adpt.Fill(ds, "orders")
            Return ds

            cmd.Dispose()
            p_cnn.Close()
        End Function

In my markup:

           <asp:ObjectDataSource ID="DSOrders" runat="server" SelectMethod="GetOrderHistory"

                TypeName="xxx.xxxx">
                <SelectParameters>
                    <asp:ControlParameter
ControlID="CustomerPhoneTextBox" PropertyName="Text" Name="Phone"
                        Type="String" DefaultValue=""/>
                </SelectParameters>
            </asp:ObjectDataSource>

I get this error:

The SelectCommand property has not been initialized before calling 'Fill'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The SelectCommand property has not been initialized before calling 'Fill'.

Source Error:

Line 61: Dim adpt As New
OracleDataAdapter(cmd.Parameters("output_cursor").Value)

Line 62:             Dim ds As New DataSet
Line 63:             adpt.Fill(ds, "orders")
Line 64:             Return ds
Line 65:

In sql server I have procedure that produces a Dataset,not a cursor. I'm likely needing to loop through the cursor right?

Thanks for any help or information. Received on Tue Jul 24 2007 - 09:51:30 CDT