ORA-01008: not all variables bound, when they are all bound

Question

I have a problem putting my Oracle query to an ASP.Net page with C# as its code behind. As you can see on the title, I'm getting the error ORA-01008: not all variables bound when the query is connected to the page. But when I try to run the query in Oracle SQL Developer, it runs just fine. Please help me solve my problem.

Here is my ASP.Net Code:

select 
      to_char(eventdate,'MM/DD/YYYY') as eventdate
      , workweek
      , shift
      , testerid
      , to_char(datefrom, 'MM/DD/YYYY HH24:MI:SS') as datefrom
      , to_char(dateto, 'MM/DD/YYYY HH24:MI:SS') as dateto
      , (((dateto - datefrom)*24)*60) as totalminutes
      , status
      , statusreason
      , serviceby
      , starjobreason
      , endjobreason
      , lotno
      , device
      , teststep
      , handlerid
      , handlertype
      , operatorid
      , operatorname
      , comments
      --, vendormodel
      from
      (
       with tbl_details as
         ( select
            *
            from
            (
            select 
            hm.resourceid
            , hm.calendarworkweek as workweek
            , hm.cdoname
            , hm.txndate
            , rsh.newstatusname
            , rsh.newreasonname
            , lead(jsh.joborderid) over (partition by hm.resourcename order by hm.resourcename, hm.historymainlineid, hm.txndate) as joborderid
            , last_value(weph.paramvalue ignore nulls) over (partition by hm.resourcename order by hm.resourcename, hm.historymainlineid, hm.txndate) as handlerid
            , last_value(hm.containername ignore nulls) over (partition by hm.resourcename order by hm.resourcename, hm.historymainlineid, hm.txndate) as lotno
            , last_value(hm.employeeid ignore nulls) over (partition by hm.resourcename order by hm.resourcename, hm.historymainlineid, hm.txndate) as employeeid
            , last_value(hm.specname ignore nulls) over (partition by hm.resourcename order by hm.resourcename, hm.historymainlineid, hm.txndate) as specname
            , last_value(p.brandname ignore nulls) over (partition by hm.resourcename order by hm.resourcename, hm.historymainlineid, hm.txndate) as packagecode
            , last_value(p.packagetype ignore nulls) over (partition by hm.resourcename order by hm.resourcename, hm.historymainlineid, hm.txndate) as packagetype
            , last_value(p.device ignore nulls) over (partition by hm.resourcename order by hm.resourcename, hm.historymainlineid, hm.txndate) as device
            , hm.comments
            , rd.vendormodel
            from historymainline hm 
            inner join resourcedef rd on hm.resourceid = rd.resourceid
            left join resourcestatushistory rsh on hm.historymainlineid = rsh.historymainlineid
            left join a_wipequipmenthistory weh on hm.containerid = weh.containerid and hm.resourceid = weh.equipmentid
            left join a_jobstarthistory jsh on hm.historymainlineid = jsh.historymainlineid
            left join a_wipequipmentparamshistory weph on weh.wipequipmenthistoryid = weph.wipequipmenthistoryid and weph.paramnamename = 'HANDLERID'
            left join product p on hm.productid = p.productid
            where hm.cdoname in ('TrackInLot','EquipmentSetStatus', 'JobStart') and hm.txndategmt > to_date(:startDate, 'MM/DD/YYYY')) 
            where cdoname = 'EquipmentSetStatus'),  
      tbl_details2 as (
            select 
             case when cast(to_char(nvl(dt.txndate, cldr.starttimestamp),'HH24.MISS') as float) < 6.0
                  then trunc(nvl(dt.txndate, cldr.starttimestamp)) - 1 else trunc(nvl(dt.txndate, cldr.starttimestamp)) end as eventdate
            , nvl(hml.calendarworkweek, cww.workweek) as workweek
            , case when cast(to_char(nvl(dt.txndate, cldr.starttimestamp),'HH24.MISS') as float) >= 6 
                  and  cast(to_char(nvl(dt.txndate, cldr.starttimestamp),'HH24.MISS') as float) < 18 then 'A' else 'B' end as shift
            , r.resourcename as testerid
            , nvl(dt.txndate, cldr.starttimestamp) as datefrom
            , lead(nvl(dt.txndate, cldr.starttimestamp)) over (partition by r.resourcename order by nvl(dt.txndate, cldr.starttimestamp)) as dateto
            , dt.newstatusname as status
            , dt.newreasonname as statusreason
            , af.fullname as serviceby
            , jph.jobreasonname as starjobreason
            , jphe.jobreasonname as endjobreason
            , dt.lotno as lotno
            , dt.device as device
            , dt.specname as teststep
            , dt.handlerid as handlerid
            , r.attr_09 as handlertype
            , ai.employeename as operatorid
            , ai.fullname as operatorname
            , ae.comments
            , r.vendormodel
            from resourcedef r 
              inner join  a_calendarshifts cldr on cldr.starttimestamp > (to_date(:startDate, 'MM/DD/YYYY')) + 6/24 and cldr.starttimestamp < (to_date(:endDate, 'MM/DD/YYYY')) + 6/24
              left join tbl_details dt on r.resourceid = dt.resourceid and dt.txndate between cldr.starttimestamp and cldr.endtimestamp
              inner join a_calendarworkweeks cww on dt.txndate between to_date(to_char(cww.startdate, 'MM/DD/YYYY'),'MM/DD/YYYY') and to_date(to_char(cww.enddate, 'MM/DD/YYYY'), 'MM/DD/YYYY')
              left join a_jobprogresshistory jph on dt.joborderid = jph.joborderid and jph.jobresultname = 'BEGIN' and upper(jph.fromstagename) like '%START%'
              left join a_jobprogresshistory jphe on dt.joborderid = jphe.joborderid and jphe.jobresultname = 'END'
              left join historymainline ae on jph.historymainlineid = ae.historymainlineid
              left join historymainline hml on ae.historymainlineid = hml.historymainlineid
              left join employee af on ae.employeeid = af.employeeid
              left join resourcedef ag on dt.handlerid = ag.resourcename
              left join location ah on r.locationid = ah.locationid
              left join employee ai on dt.employeeid = ai.employeeid
              where r.resourcename like :equipID and ah.locationname like '%' and r.attr_02 = 'TESTER'
              order by dt.txndate),

      tbl_details3 as (
              select 
                row_number() over (partition by aa.testerid order by aa.testerid, aa.eventdate, aa.datefrom desc) as numdata
                , row_number() over (partition by aa.testerid, aa.eventdate, aa.shift order by aa.testerid, aa.eventdate, aa.datefrom desc) as numdesc                
                , aa.eventdate
                , aa.workweek
                , aa.shift
                , aa.testerid
                , aa.datefrom
                , aa.dateto
                , aa.status
                , aa.statusreason
                , aa.serviceby
                , aa.starjobreason
                , aa.endjobreason
                , aa.lotno
                , aa.device
                , aa.teststep
                , aa.handlerid
                , aa.handlertype
                , aa.operatorid
                , aa.operatorname
                , aa.comments
                , aa.vendormodel
              from tbl_details2 aa) ,
        tbl_equipfirstdata as (
              select 
                row_number() over (partition by aa.testerid order by aa.testerid, aa.datefrom desc) as numdatdesc
                , row_number() over (partition by aa.testerid, aa.eventdate, aa.shift order by aa.testerid, aa.eventdate, aa.datefrom) as num                
                , aa.eventdate
                , aa.workweek
                , aa.shift
                , aa.testerid
                , aa.datefrom
                , aa.dateto
                , aa.status
                , aa.statusreason
                , aa.serviceby
                , aa.starjobreason
                , aa.endjobreason
                , aa.lotno
                , aa.device
                , aa.teststep
                , aa.handlerid
                , aa.handlertype
                , aa.operatorid
                , aa.operatorname
                , aa.comments
                , aa.vendormodel
              from tbl_details2 aa
              )

      select 
                ba.eventdate
                , ba.workweek
                , ba.shift
                , ba.testerid
                , ba.datefrom
                , (case when ba.shift = 'A' and ba.numdesc = 1 then trunc(ba.eventdate) + 18/24 
                       when ba.shift = 'B' and ba.numdesc = 1  then trunc(ba.eventdate + 1) + 6/24 else ba.dateto end) as dateto
                , ba.status
                , ba.statusreason
                , ba.serviceby
                , ba.starjobreason
                , ba.endjobreason
                , ba.lotno
                , ba.device
                , ba.teststep
                , ba.handlerid
                , ba.handlertype
                , ba.operatorid
                , ba.operatorname
                , ba.comments
                , ba.vendormodel
        from tbl_details3 ba
    union all
             select 
                 case when ba.shift = 'B' then ba.eventdate + 1 else ba.eventdate end as eventdate
                , ba.workweek
                , case when ba.shift = 'B' then 'A'
                      when ba.shift = 'A' and ba.dateto > (trunc(ba.eventdate) + 18/24) then 'B' else ba.shift end as shift
                , ba.testerid
                , case when ba.shift = 'B' then (trunc(ba.eventdate + 1) + 6/24)
                     when ba.shift = 'A' and ba.dateto > (trunc(ba.eventdate) + 18/24) then (trunc(ba.eventdate) + 18/24) else ba.datefrom end as datefrom
                , case when ba.shift = 'B' and ba.dateto > (trunc(ba.eventdate + 1) + 18/24)  then (trunc(ba.eventdate + 1) + 18/24) else ba.dateto end as dateto
                , ba.status
                , ba.statusreason
                , ba.serviceby
                , ba.starjobreason
                , ba.endjobreason
                , ba.lotno
                , ba.device
                , ba.teststep
                , ba.handlerid
                , ba.handlertype
                , ba.operatorid
                , ba.operatorname
                , ba.comments
                , ba.vendormodel
        from tbl_details3 ba
            where ba.numdesc = 1 and cast(to_char(ba.datefrom,'HH24.MISS') as float) <> 6.0 
                  and cast(to_char(ba.datefrom,'HH24.MISS') as float) <> 18.0 
                  and cast(to_char(ba.dateto,'HH24.MISS') as float) <> 18.0
                  and ba.shift = 'A'                  
   union all
             select 
                 case when ba.shift = 'B' then eventdate + 1 else ba.eventdate end as eventdate
                , ba.workweek
                , case when ba.shift = 'B' then 'A' else ba.shift end as shift
                , ba.testerid
                , case when ba.shift = 'B' then (trunc(ba.eventdate + 1) + 6/24)  else datefrom end as datefrom
                , case when ba.shift = 'B' and ba.dateto > (trunc(ba.eventdate + 1) + 18/24) then (trunc(ba.eventdate + 1) + 18/24) else ba.dateto end as dateto
                , ba.status
                , ba.statusreason
                , ba.serviceby
                , ba.starjobreason
                , ba.endjobreason
                , ba.lotno
                , ba.device
                , ba.teststep
                , ba.handlerid
                , ba.handlertype
                , ba.operatorid
                , ba.operatorname
                , ba.comments
                , ba.vendormodel
        from tbl_details3 ba
            where ba.numdesc = 1  and cast(to_char(ba.datefrom,'HH24.MISS') as float) <> 6.0 and ba.shift = 'B' and cast(to_char(ba.dateto,'HH24.MISS') as float) <> 6.0
     union all
             select 
                ba.eventdate
                , ba.workweek
                , ba.shift
                , ba.testerid
                , (case when ba.shift = 'A' then trunc(ba.eventdate) + 6/24 
                        when ba.shift = 'B'  then trunc(ba.eventdate) + 18/24 else ba.datefrom  end) as datefrom
                ,(case when ba.shift = 'B'  then ba.datefrom else ba.dateto  end) as dateto
                , ba.status
                , ba.statusreason
                , ba.serviceby
                , ba.starjobreason
                , ba.endjobreason
                , ba.lotno
                , ba.device
                , ba.teststep
                , ba.handlerid
                , ba.handlertype
                , ba.operatorid
                , ba.operatorname
                , ba.comments
                , ba.vendormodel
        from tbl_details3 ba
    where ba.numdata = 1 and cast(to_char(datefrom,'HH24.MISS') as float) <> 6.0 and cast(to_char(datefrom,'HH24.MISS') as float) <> 18.0 and ba.shift = 'B' and cast(to_char(dateto,'HH24.MISS') as float) <> 18.0)
where eventdate BETWEEN ((TRUNC(to_date(:startDate, 'MM/DD/YYYY'))) + 6/24) AND (trunc(to_date(:endDate, 'MM/DD/YYYY')) + 6/24) and teststep is not null and testerid like :equipID and vendormodel like :venmod and handlertype like :handType or handlerid like :handID or operatorid like :operID and handlerid is not null --and rownum > 1
order by testerid, eventdate, datefrom

Below is my code behind:

protected void btnFilter_Click(object sender, EventArgs e)
        {
            Session["startDate"] = string.Empty;
            Session["endDate"] = string.Empty;
            Session["equipID"] = string.Empty;
            Session["venmod"] = string.Empty;
            Session["handID"] = string.Empty;
            Session["handType"] = string.Empty;
            Session["operID"] = string.Empty;

            if (String.IsNullOrEmpty(txtDateFrom.Text))
            {
                Session["startDate"] = DateTime.Now.AddDays(-1).Date.ToShortDateString();
                txtDateFrom.Text = DateTime.Now.AddDays(-1).Date.ToShortDateString();
            }
            else
            {
                Session["startDate"] = txtDateFrom.Text.ToString();
            }

            if (String.IsNullOrEmpty(txtDateTo.Text))
            {
                Session["endDate"] = DateTime.Now.Date.ToShortDateString();
                txtDateTo.Text = DateTime.Now.Date.ToShortDateString();
            }
            else
            {
                Session["endDate"] = txtDateTo.Text.ToString();
            }

            if (ddlTesterID.SelectedItem.Value.ToString() == "")
            {
                Session["equipID"] = "%".ToString();
            }
            else
            {
                Session["equipID"] = ddlTesterID.SelectedItem.Value.ToString();
            }

            if (ddlTesterType.SelectedItem.Value.ToString() == "")
            {
                Session["venmod"] = "%".ToString();
            }
            else
            {
                Session["venmod"] = ddlTesterType.SelectedItem.Value.ToString();
            }

            if (ddlHandlerID.SelectedItem.Value.ToString() == "")
            {
                Session["handID"] = "%".ToString();
            }
            else
            {
                Session["handID"] = ddlHandlerID.SelectedItem.Value.ToString();
            }

            if (ddlHandlerType.SelectedItem.Value.ToString() == "")
            {
                Session["handType"] = "%".ToString();
            }
            else
            {
                Session["handType"] = ddlHandlerType.SelectedItem.Value.ToString();
            }

            if (String.IsNullOrEmpty(txtOperatorID.Text))
            {
                Session["operID"] = "%".ToString();
            }
            else
            {
                Session["operID"] = txtOperatorID.Text.ToString();
            }

            //X.MessageBox.Alert("Warning", Session["venmod"].ToString()).Show();

            UltraWebGrid1.DataSourceID = EDTDS.ID;
            UltraWebGrid1.DataBind();
        }

Here is the SqlDataSource:

<asp:SqlDataSource ID="EDTDS" runat="server" 
           ConnectionString="<%$ ConnectionStrings:Camstar %>" 
            ProviderName="<%$ ConnectionStrings:Camstar.ProviderName %>"  SelectCommand="select ..."
            <SelectParameters>
                <asp:SessionParameter ConvertEmptyStringToNull="False" Name="startDate" 
                    SessionField="startDate" />
                <asp:SessionParameter ConvertEmptyStringToNull="False" Name="endDate" 
                    SessionField="endDate" />
                <asp:SessionParameter ConvertEmptyStringToNull="False" Name="equipID" 
                    SessionField="equipID" />
                <asp:SessionParameter ConvertEmptyStringToNull="False" Name="venmod" 
                    SessionField="venmod" />
                <asp:SessionParameter ConvertEmptyStringToNull="False" Name="handType" 
                    SessionField="handType" />
                <asp:SessionParameter ConvertEmptyStringToNull="False" Name="handID" 
                    SessionField="handID" />
                <asp:SessionParameter ConvertEmptyStringToNull="False" Name="operID" 
                    SessionField="operID" />
            </SelectParameters>
        </asp:SqlDataSource>

Answer 1

This error often happens in case of ODP.NET. The issue lies in the OracleCommand’s default behavior when it comes to matching query parameters to supplied parameters. By default the position method is used instead of name method. In practice each parameter added to the command will be taken as is regardless of the name, and if you reference a parameter twice in the query text then you have to add the value for it twice!

This default behavior can be overriden by setting BindByName to true on OracleCommad but in case of SqlDataSource you don't have access to it and you need to provide values mutliple times in proper order:

<asp:SqlDataSource ID="EDTDS" runat="server" ConnectionString="<%$ ConnectionStrings:Camstar %>" ProviderName="<%$ ConnectionStrings:Camstar.ProviderName %>" SelectCommand="select ..."
    <SelectParameters>
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="startDate" SessionField="startDate" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="startDate" SessionField="startDate" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="endDate"  SessionField="endDate" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="equipID" SessionField="equipID" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="startDate" SessionField="startDate" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="endDate"  SessionField="endDate" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="equipID" SessionField="equipID" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="venmod" SessionField="venmod" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="handType" SessionField="handType" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="handID" SessionField="handID" />
        <asp:SessionParameter ConvertEmptyStringToNull="False" Name="operID" SessionField="operID" />
    </SelectParameters>
</asp:SqlDataSource>

(I hope I haven't missed any)

As an alternative you can consider creating your own Provider derivered from ODP.NET wwith BindByName adjusted.