ORA-01008: not all variables bound (in table adapter with parameterized query)

Question

Environment:

Visual Studio 2010 (.NET FrameWork 4)
ASP.NET web Application
Oracle Database
using System.Data.OracleClient

I have a web form where a user can enter any or no data into text boxes corresponding to each parameter in my query.if user entered no data the text box will get "0" value.

here is my query:

SELECT "CardNo" , "Name"
FROM CSC.CommercialCardList
WHERE("CardNo"=:CardNo or :CardNo=0) and ("Name"=:Name or :Name=0)

when I right click on the query from the tableadaptor in the MYDATABASE.xsd panel, and select 'Preview Data' and fill parameter values with "0" i get the error (" ORA-01008 : Not all variables bound" )

can anyone help me?

--EDIT--

CardNo = (CardNoTextBox.Text != String.Empty) ? CardNoTextBox.Text : "0";
Name = (NameTextBox.Text != String.Empty) ? NameTextBox.Text : "0";
CommercialCardList.Fill(dt, CardNo,Name);
ds.Tables.Add(dt);

CardNo = (CardNoTextBox.Text != String.Empty) ? CardNoTextBox.Text : "0"; Name = (NameTextBox.Text != String.Empty) ? NameTextBox.Text : "0"; CommercialCardList.Fill(dt, CardNo,Name); ds.Tables.Add(dt); — elh4m v, Dec 03 '15 at 14:39
Can you also add the code where you actually talk to the Oracle server? It should have an `OracleCommand` object... — Hambone, Dec 03 '15 at 14:56

score 1 · Answer 1 · answered Dec 02 '15 at 08:24

1

You need to bind 4 variables as part of your code.

2 for "CardNo"=:CardNo or :CardNo=0

2 for "Name"=:Name or :Name=0

answered Dec 02 '15 at 08:24

Durga Viswanath Gadiraju

3,896
2
14
21

i used CardNo = (CardNoTextBox.Text != String.Empty) ? CardNoTextBox.Text : "0"; you mean another one for :CardNo?? – elh4m v Dec 02 '15 at 08:51
That might be satisfying only "CardNo"=:CardNo not :CardNo=0 – Durga Viswanath Gadiraju Dec 02 '15 at 08:53
I don't think this is right. If you have `:CardNo` declared twice in the SQL, it's still a single bind variable. I think he only needs two bind variables. – Hambone Dec 02 '15 at 12:56

score 0 · Answer 2 · answered May 09 '19 at 11:24

You have to set command BindByName As true :

1- In OracleCommand as the below

 OracleCommand cmd = new OracleCommand();

2- In Tableadaptor as the below and call the SetBindByName with ture value

namespace MyTableAdapters
{
    public partial class MyTabTableAdapter
    {
        public bool SetBindByName
        {
            set
            {
                this.Adapter.InsertCommand.BindByName = value;
                this.Adapter.UpdateCommand.BindByName = value;
                this.Adapter.DeleteCommand.BindByName = value;
                foreach (Oracle.DataAccess.Client.OracleCommand cmd in this.CommandCollection)
                {
                    cmd.BindByName = value;
                }
            }
        }
    }
}

ORA-01008: not all variables bound (in table adapter with parameterized query)

2 Answers2