ORA-01008: not all variables bound (dataset)

Question

I bind my gridview with ADO.net . In my Gridview I have for each column a filter. They are initialized in page load for the first time when the gridview is binding. At debugging my viewstates show that have the correct value, but it gives me that error. All the text from my textboxes(filter) are stored in a Viewstate.

This is my code.

 protected void Page_Load(object sender, EventArgs e)
{

    if (!IsPostBack)
    {
        Initialize();
        GridViewPlanning_Bind("like '%'");
    }
}

private void Initialize()
{
    ViewState["txtUnloadingPlaceSearch"] = "";
    ViewState["txtAutoTypeSearch"] = "";
    ViewState["txtExpeditionHouseSearch"] = "";
    ViewState["txtDeliveryConditionSearch"] = "";
    ViewState["txtTransporterNameSearch"] = "";       
    ViewState["txtDateLoadingSearch"] = "";
    ViewState["txtDateUnloadSearch"] = "";
    ViewState["txtCarNumberSearch"] = "";
    ViewState["DateLoadFirst"] = "0";
    ViewState["DateLoadLast"] = "32535208799000";
    ViewState["DateUnloadFirst"] = "0";
    ViewState["DateUnloadLast"] = "32535208799000";
    ViewState["DateLoadF"] = "";
    ViewState["DateLoadL"] = "";
    ViewState["DateUnloadF"] = "";
    ViewState["DateUnloadL"] = "";
}

private void GridViewPlanning_Bind(string sw)
{
    string select_sql_Planning_GW = "SELECT P.ID, " +
                          "P.DATE_LOADING," +
                          "UNLOAD.PLACE, " +
                          "P.DATE_UNLOAD," +
                          "AT.AUTO," +
                          "P.NUMBER," +
                          "EXPEDITION.NAME_EH," +
                          "DELIVERY.CONDITION," +
                          "NAME.NAME_T," +
                          "P.ID_PLACE," +
                          "P.ID_AUTO," +
                          "P.ID_EXPEDITION_HOUSE," +
                          "P.ID_DELIVERY_CONDITION," +
                          "P.ID_NAME," +
                          "P.STATUSP " +
                            "FROM UNLOAD " +
                            "RIGHT JOIN P " +
                            "ON UNLOAD.ID = P.ID_PLACE " +
                            "LEFT JOIN AT " +
                            "ON P.ID_AUTO = AT.ID " +
                            "LEFT JOIN EXPEDITION " +
                            "ON P.ID_EXPEDITION_HOUSE = EXPEDITION.ID " +
                            "LEFT JOIN DELIVERY " +
                            "ON P.ID_DELIVERY_CONDITION = DELIVERY.ID " +
                            "LEFT JOIN NAME " +
                           " ON P.ID_NAME = NAME.ID " +
                           " WHERE STATUSP " + sw +
                           "  AND lower(UNLOAD.PLACE) LIKE :upp" +
                            " AND lower(AT_AUTO) LIKE :att" +
                            " AND lower(EXPEDITION.NAME_EH) LIKE :ehh" +
                            " AND lower(DELIVERY.CONDITION) LIKE :dcc" +
                             " AND lower(NAME.NAME_T) LIKE :ntt" +
                             " AND lower(P.DATE_LOADING) BETWEEN :date_firstt and :date_lastt" +
                             " AND lower(P.DATE_UNLOAD) BETWEEN :dateu_firstt and :dateu_lastt" +
                             " AND lower(P.NUMBER) LIKE :ncc " +
                           " ORDER BY PLANNING.ID ASC ";

    using (OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString()))
    {
        con.Open();
        OracleCommand cmd = new OracleCommand(select_sql_Planning_GW, con);
        OracleDataAdapter adapter = new OracleDataAdapter(select_sql_Planning_GW, con);
        DataSet dss = new DataSet();         
        cmd.Parameters.Add("upp", "%" + ViewState["txtUnloadingPlaceSearch"].ToString().ToLower() + "%");
        cmd.Parameters.Add("att", "%" + ViewState["txtAutoTypeSearch"].ToString().ToLower() + "%");
        cmd.Parameters.Add("ehh", "%" + ViewState["txtExpeditionHouseSearch"].ToString().ToLower() + "%");
        cmd.Parameters.Add("dcc", "%" + ViewState["txtDeliveryConditionSearch"].ToString().ToLower() + "%");
        cmd.Parameters.Add("ntt", "%" + ViewState["txtTransporterNameSearch"].ToString().ToLower() + "%");
        cmd.Parameters.Add("date_firstt", ViewState["DateLoadFirst"]);
        cmd.Parameters.Add("date_lastt", ViewState["DateLoadLast"]);
        cmd.Parameters.Add("dateu_firstt", ViewState["DateUnloadFirst"]);
        cmd.Parameters.Add("dateu_lastt", ViewState["DateUnloadLast"]);
        cmd.Parameters.Add("ncc", "%" + ViewState["txtCarNumberSearch"].ToString().ToLower() + "%");

        adapter.Fill(dss);  // this is where I have the error
        GridViewPlanning.DataSource = dss;
        GridViewPlanning.DataBind();
    }
}

Answer 1

I simplified your code and received same error. The main problem was corrected by changing:

OracleDataAdapter adapter = new OracleDataAdapter(select_sql_Planning_GW, con);  

to:

OracleDataAdapter adapter = new OracleDataAdapter(cmd);

I'm not sure if you need any other changes, anyway here is the whole code, which worked for me:

Dictionary<string, string> ViewState = new Dictionary<string, string>();
ViewState.Add("txtUnloadingPlaceSearch", "");

string select_sql_Planning_GW = "SELECT dummy from dual where upper(dummy) LIKE :upp ";

using (OracleConnection con = new OracleConnection(connectionString))
{
    con.Open();
    OracleCommand cmd = new OracleCommand(select_sql_Planning_GW, con);
    cmd.Parameters.AddWithValue("upp", 
        "%" + ViewState["txtUnloadingPlaceSearch"].ToString().ToLower() + "%");
    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
    DataSet dss = new DataSet();

    adapter.Fill(dss);
    GridViewPlanning.DataSource = dss.Tables[0];
}

Hope this helps.

Answer 2

Can you add : to your bindings like

cmd.Parameters.Add(":upp",....

Rgds