locked
ORA - 01008 not all variables bound RRS feed

  • Question

  • User-1678507862 posted

    Hi All,

    I am using Oracle, VS 2010, ASP.NET 3.5 for a web site.

    I have a data access layer which contains a datatable (Dtable).

    Dtable uses a query which has 2 parameters to select data from oracle database.

    While I am configuring the adapter using SQL builder I can send both parameter (which both are decimal) and get the result back.

    But when I try to preview it from DataAccessLayer.XSD. It gets the ORA-01008 error.

    I checked the number of parameter I had in query and it is only 2 parameters.

    And I am sending only numbers as parameters.

    Can anyone suggest me what's wrong with the adapter?

    regards,

    Thu

    Wednesday, August 24, 2011 12:52 PM

All replies

  • User269602965 posted

    Show the code

    you will get more responses.

    Wednesday, August 24, 2011 1:46 PM
  • User-1678507862 posted

    OK.

    As I said, I am using a table adapter. In that adapter, I use following query.

    SELECT     a.TRAN_NO, a.INSTAL_NO, b.corr_account_name, SUM(a.BASE_AMT_NONDEL) AS Base_outstanding                    
    FROM         (SELECT     REPT.POSTING.TRAN_NO, REPT.POSTING.INSTAL_NO, REPT.POSTING.POSTING_KEY, REPT.POSTING.CURR_CODE_SETT, 
                                                  REPT.POSTING.POSTING_LVL1_KEY, REPT.POSTING.RISK_REFERENCE, REPT.POSTING.TECH_TRAN_NUM, 
                                                  REPT.POSTING.TECH_TRAN_VERSN, REPT.POSTING.ACCOUNT_TYPE,  REPT.POSTING.CORR_ACCOUNT_REF, REPT.POSTING.BASE_AMT_NONDEL, 
                                                  REPT.POSTING.SETT_AMT_NONDEL
    FROM          REPT.POSTING INNER JOIN                                              REPT.ACCOUNT ON REPT.POSTING.ACCOUNT_REF = REPT.ACCOUNT.ACCOUNT_REF
                           WHERE      (REPT.POSTING.TRAN_NO = :PARAM1) AND (REPT.POSTING.CURRENT_STATUS <> 'V')) a INNER JOIN
                              (SELECT DISTINCT 
                                                       POSTING_1.CURRENT_STATUS, POSTING_1.TRAN_NO, POSTING_1.INSTAL_NO, POSTING_1.POSTING_LVL1_KEY,                                                 POSTING_1.CORR_ACCOUNT_REF, ACCOUNT_1.BA_MAIN_NAME AS corr_account_name
                                FROM          REPT.POSTING POSTING_1 INNER JOIN
                                                       REPT.ACCOUNT ACCOUNT_1 ON POSTING_1.CORR_ACCOUNT_REF = ACCOUNT_1.ACCOUNT_REF
                                WHERE      (POSTING_1.TRAN_NO = :PARAM2) AND (POSTING_1.CORR_ACCOUNT_REF IS NOT NULL) AND (POSTING_1.CURRENT_STATUS <> 'V')) 
                          b ON a.POSTING_KEY = b.POSTING_LVL1_KEY
    GROUP BY a.TRAN_NO, a.INSTAL_NO,a.account_name

    The above adapter is called from a following button click function

      protected void btnSearch_Click(object sender, EventArgs e)
        {
            if (IsValid)
            {
                try
                {
                    DataLayerTableAdapters.TransactionDetailsStatsTableAdapter adapter = new DataLayerTableAdapters.TransactionDetailsStatsTableAdapter();
                  //error occurs at the following line.
                    DataLayer.TransactionDetailsStatsDataTable table = adapter.GetData(Convert.ToDecimal(txtTransaction.Text.Trim()), Convert.ToDecimal(txtTransaction.Text.Trim()));
                    if (table.Rows.Count > 0)
                    {
                        gdVResult.DataSource = table;
                        gdVResult.DataBind();
                        trResult.Visible = true;
                        trError.Visible = false;
                    }
                    else
                    {
                        trResult.Visible = false;
                        trError.Visible = true;
                        lblError.Text = "No records found.";
                    }
                }
                catch (Exception ex)
                {
                    trResult.Visible = false;
                    trError.Visible = true;
                    lblError.Text = "Error has occured. \n " + ex.Message;
                }
    }

    Please note guys, The query itself is running fine when I am using sql builder to create the table adapter. I tested it by clicking execute button and I got the result. After I finish creating the adapter, I try to preview the data on that adapter, when I do that I got the error as well (same error ora-01008).

    So I suspect,  the problem will be asp.net framework sending parameters without binding to Oracle server.

    Thursday, August 25, 2011 4:53 AM
  • User269602965 posted

    Try posting your details on the Oracle ODP.NET forum.

    Not certain if DataLayerTableAdapters.TransactionDetailsStatsTableAdapter adapter  is supported in ODP.NET.

    https://forums.oracle.com/forums/forum.jspa?forumID=146

    ODP.NET Oracle technicians visit the site regularly.

     

    Sunday, August 28, 2011 8:30 PM