vb.net function and Oracle procedure to get an Oracle Dataset??

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.

create or replace package body PK_RETAILER is

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.

0
jcnet
7/24/2007 2:49:40 PM
asp.net.oracle 2269 articles. 0 followers. Follow

4 Replies
1878 Views

Similar Articles

[PageSpeed] 42

At first glance, I'd say you forgot to initialise the command property of your DataAdapter.

0
shados
7/24/2007 3:02:49 PM

This was not an issue when pointing to sql server.

 And how would I do that? beyond what I'm already doing?

Dim cmd As New OracleCommand

Thank you. 

 

0
jcnet
7/24/2007 3:07:02 PM

Usually when you declare a dataadapter, the first parameter is the command, SqlServer or Oracle or anything. If you don't set it in the constructor, you can simply:

myAdapter.SelectCommand = cmd;

0
shados
7/24/2007 3:21:46 PM

 The error is in this line

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

It should be

Dim adpt As New OracleDataAdapter(cmd)


Regards,
Prashant


Dont forget to click "Mark as Answer" on the post that helped you.
0
Prashant
7/25/2007 4:06:18 AM
Reply:

Web resources about - vb.net function and Oracle procedure to get an Oracle Dataset?? - asp.net.oracle

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Catholic hospital allowed to deny sterilization procedure, judge says
Hospital in California argued that the procedure known as ‚Äčtubal ligation would violate its religious freedom

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

Resources last updated: 2/3/2016 2:04:47 PM