2 Replies Latest reply: Sep 15, 2009 11:25 PM by 722332 RSS

    ODP.Net Executing Proc

      In Visual Studio I am having some difficulty returning data to a grid from a stored proc using ODP.net. I am using an OracleDataAdapter to fill a DataTable which in turn is used as the DataSource.
      I find that the first time I run the program ... the data is returned to the grid. If I then close the program ... then change the data and then run the program again ... the grid still displays the data as it was.
      The altered datset is not displayed. Can someone pleased help me.

      private OracleConnection conn = new OracleConnection();
      private OracleCommand cmd = null;
      private OracleDataAdapter adp = null;
      private DataTable dt = null;

      //I first set up the connection string
      conn.ConnectionString = ..........

      //create command using sql proc name
      cmd = new OracleCommand("SPS_USERSTATUS", conn);
      cmd.CommandType = CommandType.StoredProcedure;

      //setup ref cursor
      OracleParameter refCursor = new OracleParameter();
      refCursor.OracleDbType = OracleDbType.RefCursor;
      refCursor.Direction = ParameterDirection.Output;

      adp = new OracleDataAdapter(cmd);
      dt = new DataTable();
      dataGridView1.DataSource = dt;

      Edited by: user8803094 on Sep 14, 2009 12:12 AM

      Edited by: user8803094 on Sep 14, 2009 12:18 AM

      Edited by: user8803094 on Sep 14, 2009 12:18 AM
        • 1. Re: ODP.Net Executing Proc
          gdarling - oracle
          You probably need to call dataGridView1.DataBind() after you set the new datasource?

          This works for me anyway. After I call a page, i can update the underlying table via sqplus, and then see the new data when I reselect that option.


          <%@ Page CompilerOptions='/R:"d:\oracle\product\11.1.0\db_1\odp.net\BIN\2.x\Oracle.DataAccess.dll"' Language="c#" %>
          <%@ import Namespace="System.Data" %>
          <%@ import Namespace="Oracle.DataAccess.Client" %>
          <%@ import Namespace="Oracle.DataAccess.Types" %>
          <%@ import Namespace="System.Web.UI" %>
          <script runat="server">
          protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
              CREATE OR REPLACE procedure simplerefcur( v_deptno in number,ecur out sys_refcursor) is
                   OPEN ecur for select * from emp where deptno=v_deptno;
             using (OracleConnection con = new OracleConnection("data source=orcl;user id=scott;password=tiger;"))
                 using (OracleCommand cmd = new OracleCommand("simplerefcur", con))
                     cmd.CommandType = CommandType.StoredProcedure;
                     OracleParameter p1 = new OracleParameter("p1", OracleDbType.Decimal);
                     p1.Value = Int32.Parse(DropDownList1.Text);
                     OracleParameter p2 = new OracleParameter("p2", OracleDbType.RefCursor, ParameterDirection.Output);
                     OracleDataAdapter da = new OracleDataAdapter(cmd);
                     DataSet ds = new DataSet();
                     MyDataGrid.DataSource = ds;
              <form runat="server">
                        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
                      <ASP:DataGrid id="MyDataGrid" runat="server"></ASP:DataGrid>
          • 2. Re: ODP.Net Executing Proc
            Thanks for that .... seemed to fix it.