troubleshooting Question

ASP.NET how to provide input parameter for a DeleteCommand in a SqlDataSource

Avatar of toooki
toooki asked on
.NET ProgrammingASP.NET
8 Comments1 Solution687 ViewsLast Modified:
The attached code does not work fr the DeleteCommand (but works perfectly for SelectCommand) as I did not (probably) provide the input parameter correctly..

<asp:SqlDataSource
                                ID="ID1"
                                runat="server"
                                ProviderName= "System.Data.OracleClient"
                                ConnectionString="<%$ appSettings:ConnectString1 %>"
                                 SelectCommand="MyPkg.MyProc" SelectCommandType="StoredProcedure"
                                 onselecting="SqlDataSource1_Selecting"

                            DeleteCommand="MyPkg.MyProc2" DeleteCommandType="StoredProcedure">

           <SelectParameters>
                <asp:Parameter Name="resultCursor" Direction="Output" />
            </SelectParameters>  

                            <DeleteParameters>
                            <asp:Parameter Name="myInputStr" Direction="Input" />
                            </DeleteParameters>
</asp:SqlDataSource>

in the code behind:
protected void SqlDataSource1_Selecting
      (object sender, SqlDataSourceSelectingEventArgs e)
    {
        ((System.Data.OracleClient.OracleParameter)e.Command.Parameters[0]).OracleType =
             System.Data.OracleClient.OracleType.Cursor;
    }

The database package is:

CREATE OR REPLACE PACKAGE BODY MyPkg IS
  Procedure MyProc(resultCursor OUT SYS_REFCURSOR) AS
  BEGIN
    OPEN resultCursor for
      SELECT F1, F2 from MyTab;
END;

  Procedure MyProc2(myInputStr IN varchar2) AS
  BEGIN
    DELETE FROM MyTab WHERE F1 = myInputStr;
    COMMIT;
  END;


I get this error when load the page:
Server Error in '/' Application.
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MyPkg.MyProc2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
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.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MyPkg.MyProc2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros