call oracle ref cursor in vb.net to grid or any bound controls

  • Thread starter Thread starter rbutch
  • Start date Start date
R

rbutch

guys, im missing one little thing here and i just thought someone might be able to tell where that is.
im using an Input parameter, to retrieve a Ref Cursor from an Oracle stored procedure, and trying to display the information in a DataGrid object.
i'll include the ref cursor, but i know that's correct and its' pulling data. i can call this stored proc from oracle consistently.
and even when i debug in vb.net i can see its' pulling data by looking at the count property.
and it changes the datagrid to the columns that it's pulling, so i'm right there at the finish line but, nothing is populating and i know its got to be simple
please if someone could help, i'd really appreciate it.

here's the package spec for the ref cursor:::::

CREATE OR REPLACE PACKAGE cursor_pk
AS TYPE t_cursor IS REF CURSOR;
PROCEDURE get_orgs(empl IN OUT org_history.empl_id%type
,io_cursor OUT t_cursor);
end cursor_pk;


AND NOW THE PACKAGE BODY:::::::::::

CREATE OR REPLACE PACKAGE BODY cursor_pk
AS

PROCEDURE get_orgs(empl IN OUT org_history.empl_id%type
,io_cursor OUT t_cursor)
is
v_cursor t_cursor;
begin
open v_cursor for
select empl_id,org_lvl_1,org_lvl_2,org_lvl_3,org_lvl_4
from org_history
where empl_id = empl and balance_date = (select MAX(balance_date)
from org_history
where empl_id = empl);

io_cursor := v_cursor;
end get_orgs;
end cursor_pk;


and now the code in VB::::::::.
Sub btnGo_Click(THE REST YOU KNOW - ITS AUTOMATIC))

Dim empl As String
Dim x As Exception
Dim ds As New DataSet
empl = UCase(txtempl.Text)
Dim oraclecon As New OracleConnection("Server=trng;Uid=rik;Pwd=rikb53")
oraclecon.Open()
ds.Clear()
ds.EnforceConstraints = False
Dim myCmd As New OracleCommand
myCmd.Connection = oraclecon
myCmd.CommandText = "cursor_pk.get_orgs"
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.Add(New OracleParameter("empl", OracleClient.OracleType.VarChar)).Value = 123
myCmd.Parameters.Add(New OracleParameter("io_cursor", OracleClient.OracleType.Cursor)).Direction = ParameterDirection.Output
Dim myDa As New OracleDataAdapter(myCmd)
Try
myDa.Fill(ds)
If ds.Tables.Count = 0 Then
MessageBox.Show("nothing found")
ElseIf ds.Tables.Count = 1 Then
Else
End If
Catch ex As Exception
MessageBox.Show(x.Message.ToString)
End Try
DataGrid1.DataSource = ds.Tables(0)
oraclecon.Close()

END SUB;
like i said it's getting data just fine and the count property is 1 as it should be.

any help is appreciated
thanks as usual
rik


**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
 
On Wed, 21 Jul 2004 04:34:00 -0700, rik butcher ([email protected]) wrote:

¤ guys, im missing one little thing here and i just thought someone might be able to tell where that is.
¤ im using an Input parameter, to retrieve a Ref Cursor from an Oracle stored procedure, and trying to display the information in a DataGrid object.
¤ i'll include the ref cursor, but i know that's correct and its' pulling data. i can call this stored proc from oracle consistently.
¤ and even when i debug in vb.net i can see its' pulling data by looking at the count property.
¤ and it changes the datagrid to the columns that it's pulling, so i'm right there at the finish line but, nothing is populating and i know its got to be simple
¤ please if someone could help, i'd really appreciate it.
¤
¤ here's the package spec for the ref cursor:::::
¤
¤ CREATE OR REPLACE PACKAGE cursor_pk
¤ AS TYPE t_cursor IS REF CURSOR;
¤ PROCEDURE get_orgs(empl IN OUT org_history.empl_id%type
¤ ,io_cursor OUT t_cursor);
¤ end cursor_pk;
¤
¤
¤ AND NOW THE PACKAGE BODY:::::::::::
¤
¤ CREATE OR REPLACE PACKAGE BODY cursor_pk
¤ AS
¤
¤ PROCEDURE get_orgs(empl IN OUT org_history.empl_id%type
¤ ,io_cursor OUT t_cursor)
¤ is
¤ v_cursor t_cursor;
¤ begin
¤ open v_cursor for
¤ select empl_id,org_lvl_1,org_lvl_2,org_lvl_3,org_lvl_4
¤ from org_history
¤ where empl_id = empl and balance_date = (select MAX(balance_date)
¤ from org_history
¤ where empl_id = empl);
¤
¤ io_cursor := v_cursor;
¤ end get_orgs;
¤ end cursor_pk;
¤
¤
¤ and now the code in VB::::::::.
¤ Sub btnGo_Click(THE REST YOU KNOW - ITS AUTOMATIC))
¤
¤ Dim empl As String
¤ Dim x As Exception
¤ Dim ds As New DataSet
¤ empl = UCase(txtempl.Text)
¤ Dim oraclecon As New OracleConnection("Server=trng;Uid=rik;Pwd=rikb53")
¤ oraclecon.Open()
¤ ds.Clear()
¤ ds.EnforceConstraints = False
¤ Dim myCmd As New OracleCommand
¤ myCmd.Connection = oraclecon
¤ myCmd.CommandText = "cursor_pk.get_orgs"
¤ myCmd.CommandType = CommandType.StoredProcedure
¤ myCmd.Parameters.Add(New OracleParameter("empl", OracleClient.OracleType.VarChar)).Value = 123
¤ myCmd.Parameters.Add(New OracleParameter("io_cursor", OracleClient.OracleType.Cursor)).Direction = ParameterDirection.Output
¤ Dim myDa As New OracleDataAdapter(myCmd)
¤ Try
¤ myDa.Fill(ds)
¤ If ds.Tables.Count = 0 Then
¤ MessageBox.Show("nothing found")
¤ ElseIf ds.Tables.Count = 1 Then
¤ Else
¤ End If
¤ Catch ex As Exception
¤ MessageBox.Show(x.Message.ToString)
¤ End Try
¤ DataGrid1.DataSource = ds.Tables(0)
¤ oraclecon.Close()
¤
¤ END SUB;

Try changing a couple of lines of code:

myDa.Fill(ds) to myDa.Fill(ds, "DataTableName")

DataGrid1.DataSource = ds.Tables(0) to DataGrid1.SetDataBinding(ds, "DataTableName")

You could also change the above statement to:

DataGrid1.DataSource = ds
DataGrid1.DataMember = "DataTableName"


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top