10 Replies Latest reply: Jun 1, 2009 3:23 PM by gbednars RSS

    Oracle.DataAccess.Client and SqlDataSource in ASP.NET

    624437
      Hello.
      I just wanted to confirm whether Oracle.DataAccess.Client (ODP.NET provider) is still incompatible with SqlDataSource when the query has got parameters.

      For example, this query in SqlDataSource tag:

      UpdateCommand=UPDATE ATLSM_OWNER."ATLSM_SITE" SET "SITE_DSCR" = :SITE_DSCR WHERE "SITE_ID" = :SITE_ID'

      with:

      <UpdateParameters>
      <asp:Parameter Name="SITE_DSCR" Type="String" />
      <asp:Parameter Name="SITE_ID" Type="Decimal" />
      </UpdateParameters>

      Works fine with: System.Data.OracleClient but it doesn't with: Oracle.DataAccess.Client

      Many thanks.
      Best Regards,
      Eduardo.
        • 1. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
          24208
          Hello,

          Using the 11g production release of ODP.NET (2.111.6.20) I was able to create a very simple ASP.NET page using the HR sample schema DEPARTMENTS table. I used a SqlDataSource and a GridView control. I enabled editing on the GridView and it appears to work as expected.

          Here is what the body of the page looks like... (sorry for any formatting issues):
          <body>
              <form id="form1" runat="server">
                 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="DEPARTMENT_ID">
                  <Columns>
                    <asp:CommandField ShowEditButton="True" />
                    <asp:BoundField DataField="DEPARTMENT_ID" HeaderText="DEPARTMENT_ID" ReadOnly="True"
                      SortExpression="DEPARTMENT_ID" />
                    <asp:BoundField DataField="DEPARTMENT_NAME" HeaderText="DEPARTMENT_NAME" SortExpression="DEPARTMENT_NAME" />
                    <asp:BoundField DataField="MANAGER_ID" HeaderText="MANAGER_ID" SortExpression="MANAGER_ID" />
                    <asp:BoundField DataField="LOCATION_ID" HeaderText="LOCATION_ID" SortExpression="LOCATION_ID" />
                  </Columns>
                </asp:GridView>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
                  ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
                     SelectCommand="SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID FROM DEPARTMENTS ORDER BY DEPARTMENT_NAME"
                     UpdateCommand="UPDATE DEPARTMENTS SET DEPARTMENT_NAME = :department_name, MANAGER_ID = :manager_id, LOCATION_ID = :location_id WHERE (DEPARTMENT_ID = :department_id)">
                  <UpdateParameters>
                    <asp:ControlParameter ControlID="GridView1" Name="department_name" PropertyName="SelectedValue"
                      Type="String" />
                    <asp:ControlParameter ControlID="GridView1" Name="manager_id" PropertyName="SelectedValue"
                      Type="Int32" />
                    <asp:ControlParameter ControlID="GridView1" Name="location_id" PropertyName="SelectedValue"
                      Type="Int32" />
                    <asp:ControlParameter ControlID="GridView1" Name="department_id" PropertyName="SelectedValue"
                      Type="Int32" />
                  </UpdateParameters>
                </asp:SqlDataSource>
              </form>
          </body>
          Regards,

          Mark
          • 2. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
            624437
            Many thanks, Mark.

            However, since the server my application is going to run has got installed ODP.NET 2.0 10.2.0.2.20 I cannot use 11g production release...

            I've tried changing <asp:Parameter> by <asp:ControlParameter> but it still doesn't work...

            Any other idea? Or just ODP.NET 10g is not able to manage parameters in GridView controls?

            Best regards,
            Eduardo.
            • 3. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
              624936
              This was a very helpful example. Thanks!

              Ken
              • 4. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
                494120
                I have a very similar issue with Oracle 11g ODP.NEt provider.

                When using a parament as part of my select statment with a SqlDataSource, a general Select will work (with no parameters), but if I add a parameter, no data is returned.

                eg.

                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
                SelectCommand="SELECT EMPLOYEE, NAME, HIRE_DATE, LNAME, FNAME, PROCESS_LEVEL FROM BAINQ_EMPLIST WHERE (LNAME = :param1)">

                <SelectParameters>


                <asp:ControlParameter ControlID="TextBox1" Name="param1" Direction="Input" Type="String" PropertyName="Text" />

                </SelectParameters>

                </asp:SqlDataSource>

                Even when I validate data that is known to exist, it never returns any data fro the SqlDataSource. (this will return data in the Query Builder window)

                Thanks1
                • 5. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
                  24208
                  Hello,

                  Have you enabled AutoPostBack on the TextBox or are you submitting the form so that the value can be read?

                  - Mark
                  • 6. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
                    663125
                    I was wondering if the issue with the SELECT statement parameters was resolved. I, too, can't get a result set returned if my SELECT statement has parameters.
                    • 7. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
                      663125
                      Never mind - Read some more, saw the BindByName property, put my parameters in order, and Voila, it worked.
                      • 8. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
                        545450
                        ODP.NET 11g
                        Oracle 10.2
                        With the following code, the default value appears in the GridView but when an item is selected from the ListBox, the GridView is not updated.

                        An earlier poster said there was a problem with a parameterized Select query and Mark Williams asked whether whether AutoPostBack was true. However, the earlier poster never replied. In my example, the ListBox does have AutoPostBack equal to true, but the problem exists. So is SqlDataSource with a parameterized select query working?

                        &lt;asp:SqlDataSource ID="SqlDataSource1" runat="server"
                        ProviderName="Oracle.DataAccess.Client"
                        ConnectionString="&lt;%$ ConnectionStrings:OracleConnectionString %&gt;"
                        SelectCommand="SELECT ORG_NAME, ORG_CODE FROM ORG_VIEW "&gt;
                        &lt;/asp:SqlDataSource&gt;
                        &lt;asp:SqlDataSource ID="SqlDataSource2" runat="server"
                        ProviderName="Oracle.DataAccess.Client"
                        ConnectionString="&lt;%$ ConnectionStrings:OracleConnectionString %&gt;"
                        SelectCommand="SELECT ORG_NAME, ORG_CODE, ORG_STREET_ADDR1, ORG_ZIP_CODE, ORG_CITY FROM ORG_VIEW WHERE
                        (ORG_CODE = :Org_Code)"&gt;
                        &lt;SelectParameters&gt;
                        &lt;asp:ControlParameter ControlID="ListBox1" Name="Org_Code" DefaultValue="L2B" PropertyName="SelectedValue" Type="String" Direction="Input" /&gt;
                        &lt;/SelectParameters&gt;
                        &lt;/asp:SqlDataSource&gt;
                        &lt;asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource1" DataTextField="ORG_NAME" DataValueField="ORG_CODE" AutoPostBack="True" &gt;
                        &lt;/asp:ListBox&gt;
                        &lt;asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2" &gt;
                        &lt;/asp:GridView&gt;

                        Hope someone can help here.
                        Thanks.
                        • 9. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
                          545450
                          Thank me.
                          Solved my own problem.
                          I found out that in my codebehind for the page, in the PageLoad() method, I had a DataBind() call which interfered with the SqlDataSource by resetting the SelectedValue of the ListBox to the DefaultValue on each postback. Elimininating the DataBind() resolved the problem.
                          • 10. Re: Oracle.DataAccess.Client and SqlDataSource in ASP.NET
                            gbednars
                            The answer to the original question is that when using 10.2 ODP.NET with Visual Studio and attempting to use a SqlDataSource and then asp:Parameter to bind in values into a SQL statement, if the bind parameter happens to be a string you will need to set the "Size" property of the asp:Parameter in order to get it to work and not return no data. Here is an example:

                            WEB.CONFIG
                            ------------------

                            <configuration>
                            ...
                            <connectionStrings>
                            <add name="odp_net" connectionString="User ID=SCOTT;Password=TIGER;Data Source=ORCL;" providerName="Oracle.DataAccess.Client"/>
                            <add name="ms_net" connectionString="User ID=SCOTT;Password=TIGER;Data Source=ORCL;" providerName="System.Data.OracleClient"/>
                            </connectionStrings>
                            ...
                            </configuration>



                            DEFAULT.ASPX
                            ---------------------

                            <asp:SqlDataSource ID="ResultsSqlDataSource" runat="server"
                            ConnectionString="<%$ ConnectionStrings:odp_net %>"
                            ProviderName="<%$ ConnectionStrings:odp_net.ProviderName %>"
                            SelectCommand="SELECT *
                            FROM emp
                            WHERE ename = :NAME"
                            SelectCommandType="Text"
                            CancelSelectOnNullParameter="False">

                            <SelectParameters>
                            <asp:Parameter Name="NAME" Type="String" Size="6" /> <!-- NOTE the use of SIZE -->
                            </SelectParameters>

                            </asp:SqlDataSource>

                            ...
                            <asp:DataGrid ID="SQLDataGrid" runat="server" DataSourceID="ResultsSqlDataSource" AutoGenerateColumns="true">
                            </asp:DataGrid>


                            Adding the "Size" property allows the bind to execute successfully. This is fixed in the latest version of 11g ODP.NET.