locked
ORA-01008: Not all variables bound RRS feed

  • Question

  • User1958825706 posted
    This is the code behind of my first aspx page:
     
    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Data.OracleClient;
    //using System.Data.OracleClient.OracleDataReader;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    
    public partial class RequesterDataInput : System.Web.UI.Page
    {
        Requests xxx = new Requests();
        protected void Page_Load(object sender, EventArgs e)
        {
            ddlDepartment.DataSource = RequestDAL.GetDepartment();
            ddlDepartment.DataTextField = "Description";
            ddlDepartment.DataValueField = "Department_Code";
            ddlDepartment.DataBind();
            ddlDesignation.DataSource = RequestDAL.GetDesignations();
            ddlDesignation.DataTextField = "Description";
            ddlDesignation.DataValueField = "Designation_Code";
            ddlDesignation.DataBind();
        }
        
        protected void btnNext_Click(object sender, EventArgs e)
        {
            xxx.gTrans_Date = DateTime.Now;
    
            string constr = RequestDAL.GetConnectionString();
            string query = "SELECT Room_ID, Room_No FROM Rooms WHERE Room_Status= 'O' AND Status= 'A'";
            OracleConnection conn = new OracleConnection(constr);            
            OracleCommand command = new OracleCommand(query, conn);
            conn.Open();
            OracleDataReader reader = command.ExecuteReader();
            
                        if (reader.Read())
                        {
                             Response.Redirect("HouseRequest.aspx");
                            
                        }
                        else
                        {
                            Response.Redirect("HotelRequest.aspx");
                        }
                        reader.Close();
                        conn.Close();
                        GlobalsX.reqData = xxx;
         }
    
        protected void txtEmail_TextChanged(object sender, EventArgs e)
        {
            xxx.gEmail = txtEmail.Text;
     
        }
        protected void txtEmpNo_TextChanged(object sender, EventArgs e)
        {
            xxx.gEmp_No = txtEmpNo.Text;
        }
        protected void txtLastName_TextChanged(object sender, EventArgs e)
        {
            xxx.gLast_Name = txtLastName.Text;
        }
        protected void txtFirstName_TextChanged(object sender, EventArgs e)
        {
            xxx.gFirst_Name = txtFirstName.Text;
        }
        protected void txtMiddleName_TextChanged(object sender, EventArgs e)
        {
            xxx.gMiddle_Name = txtMiddleName.Text;
        }
        protected void ddlDepartment_SelectedIndexChanged(object sender, EventArgs e)
        {
            xxx.gDepartment_Code = ddlDepartment.SelectedValue;
        }
        protected void ddlDesignation_SelectedIndexChanged(object sender, EventArgs e)
        {
            xxx.gJob_title = ddlDesignation.SelectedValue;
        }
        protected void ddlStafFlag_SelectedIndexChanged(object sender, EventArgs e)
        {
            xxx.gStaff_Flag = ddlStafFlag.SelectedValue;
        }
    }
    
    
    
    When the button Next is clicked it should take me to the next page with the following code behind: 
    
    
    
    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    
    public partial class HotelRequest : System.Web.UI.Page
    {
        Requests xxx = new Requests();
        RequestDAL request = new RequestDAL();
        protected void Page_Load(object sender, EventArgs e)
        {
            ddlBranch.DataSource = RequestDAL.GetHotels();
            ddlBranch.DataTextField = "Branch_Code";
            ddlBranch.DataValueField = "Branch_Code";
            ddlBranch.DataBind();
            ddlHotels.DataSource = RequestDAL.GetHotels();
            ddlHotels.DataTextField = "Description";
            ddlHotels.DataValueField = "Hotel_Code";
            ddlHotels.DataBind();
            xxx.gUserCode = User.Identity.Name.ToUpper();
            xxx.gLastUpdated = DateTime.Now;
            xxx.gHouse_ID = "";
            xxx.gFlat_No = "";
            xxx.gRoom_ID = "";
            xxx.gArr_Date = (DateTime?)null;
            xxx.gArr_Time = (DateTime?)null;
            xxx.gDep_Date = (DateTime?)null;
            xxx.gDep_Time = (DateTime?)null;
            xxx.gDSO_UserCode = "";
            xxx.gHR_UserCode = "";
    
        }
        protected void ddlHotels_SelectedIndexChanged(object sender, EventArgs e)
        {
            xxx.gHotel_Code = ddlHotels.SelectedValue;
        }
        protected void ddlBranch_SelectedIndexChanged(object sender, EventArgs e)
        {
            xxx.gBranch_Code = ddlBranch.SelectedValue;
        }
        protected void btnSave_Click(object sender, EventArgs e)
        {
            request.AddRequest(xxx);  
        }
    }
    
    
    When the button Save is clicked the mothod AddRequest in the class below should be executed and data inserted into my
     Request table. Instead I ecounter the error ORA-01008: not all variables bound. Please can anyone help me out? 
     
    using System;
    using System.Data;
    using System.Data.OracleClient;
    using System.Configuration;
    //using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    //using System.Xml.Linq;
    //using ePerformDataSetTableAdapters;
    
    /// <summary>
    /// Summary description for PerformanceRankingsBLL
    /// </summary>
    [System.ComponentModel.DataObject]
    
    
    
    public class RequestDAL
    {
        
    
      public static string GetConnectionString()
         {
            //sets the connection string from your web config file "ConnString" is the name of your Connection String
            return System.Configuration.ConfigurationManager.ConnectionStrings["HouseMgtConnectionString"].ConnectionString;
         }
    
     public static DataTable GetHotels()
      {
          string constr = GetConnectionString();
          string query = "SELECT a.Hotel_Code, a.Description, b.Branch_Code FROM Hotels a, Branch b WHERE a.Branch_Code= b.Branch_Code AND a.Status='A' AND b.Status='A'";
          OracleDataAdapter da = new OracleDataAdapter(query, constr);
          DataTable table = new DataTable();
          da.Fill(table);
          return table;
      }
    
    
    
    public void AddRequest(Requests c)
    {
        string constr = GetConnectionString();
        string query = "INSERT INTO Request (Emp_No, Last_Name, Middle_Name, First_Name, E_Mail, Department_Code, Job_title, Trans_Date, Staff_Flag, Branch_Code, Hotel_Code, House_ID, Flat_No, Room_ID, Arr_Date, Arr_Time, Dep_Date, Dep_Time, Remarks, approval, Usercode, DSO_UserCode, HR_UserCode, LastUpdated) VALUES(:Emp_No, :Last_Name, :Middle_Name, :First_Name, :E_Mail, :Department_Code, :Job_title, :Trans_Date, :Staff_Flag, :Branch_Code, :House_ID, :Hotel_Code, :Flat_No, :Room_ID, :Arr_Date, :Arr_Time, :Dep_Date, :Dep_Time, :Remarks, :approval, :Usercode, :DSO_UserCode, :HR_UserCode, :LastUpdated)";
        OracleConnection con = new OracleConnection(constr);
        OracleCommand com = new OracleCommand(query, con);
        com.Parameters.AddWithValue(":Emp_No", c.gEmp_No);
        com.Parameters.AddWithValue(":Last_Name", c.gLast_Name);
        com.Parameters.AddWithValue(":Middle_Name", c.gMiddle_Name);
        com.Parameters.AddWithValue(":First_Name", c.gFirst_Name);
        com.Parameters.AddWithValue(":E_Mail", c.gEmail);
        com.Parameters.AddWithValue(":Department_Code", c.gDepartment_Code);
        com.Parameters.AddWithValue(":Job_title", c.gJob_title);
        com.Parameters.AddWithValue(":Trans_Date", c.gTrans_Date);
        com.Parameters.AddWithValue(":Staff_Flag", c.gStaff_Flag);
        com.Parameters.AddWithValue(":Branch_Code", c.gBranch_Code);
        com.Parameters.AddWithValue(":Hotel_Code", c.gHotel_Code);
        com.Parameters.AddWithValue(":House_ID", c.gHouse_ID);
        com.Parameters.AddWithValue(":Flat_No", c.gFlat_No);
        com.Parameters.AddWithValue(":Room_ID", c.gRoom_ID);
        com.Parameters.AddWithValue(":Arr_Date", c.gArr_Date);
        com.Parameters.AddWithValue(":Arr_Time", c.gArr_Time);
        com.Parameters.AddWithValue(":Dep_Date", c.gDep_Date);
        com.Parameters.AddWithValue(":Dep_Time", c.gDep_Time);
        com.Parameters.AddWithValue(":Remarks", c.gRemarks);
        com.Parameters.AddWithValue(":approval", c.gapproval);
        com.Parameters.AddWithValue(":Usercode", c.gUserCode);
        com.Parameters.AddWithValue(":DSO_UserCode", c.gDSO_UserCode);
        com.Parameters.AddWithValue(":HR_UserCode", c.gHR_UserCode);
        com.Parameters.AddWithValue(":LastUpdated", c.gLastUpdated);
        con.Open();
        com.ExecuteNonQuery();
        con.Close();
    }
    
    }
    
    
    public class Requests
    {
            public string gEmp_No { get; set; }
            public string gLast_Name { get; set; }
            public string gMiddle_Name { get; set; }
            public string gFirst_Name { get; set; }
            public string gEmail { get; set; }
            public string gDepartment_Code { get; set; }
            public string gJob_title { get; set; }
            public DateTime gTrans_Date { get; set; }
            public string gStaff_Flag { get; set; }
            public string gBranch_Code { get; set; }
            public string gHouse_ID { get; set; }
            public string gHotel_Code { get; set; }
            public string gFlat_No { get; set; }
            public string gRoom_ID { get; set; }
            public DateTime? gArr_Date { get; set; }
            public DateTime? gArr_Time { get; set; }
            public DateTime? gDep_Date { get; set; }
            public DateTime? gDep_Time { get; set; }
            public string gRemarks { get; set; }
            public string gapproval { get; set; }
            public string gUserCode { get; set; }
            public string gDSO_UserCode { get; set; }
            public string gHR_UserCode { get; set; }
            public DateTime gLastUpdated { get; set; }
    }
    
    
    


     

    Friday, July 20, 2012 1:18 PM

All replies

  • User269602965 posted

    Order of the INSERT columns, BIND Variables, and the Parameters must be exactly the same,

    and they are not:

        string query = "INSERT INTO Request 
          (Emp_No, 
           Last_Name, 
           Middle_Name, 
           First_Name, 
           E_Mail, 
           Department_Code, 
           Job_title, 
           Trans_Date, 
           Staff_Flag, 
           Branch_Code, 
       >>>    Hotel_Code,   <<< you have problems with order starting here 
       >>>    House_ID, 
           Flat_No, 
           Room_ID, 
           Arr_Date, 
           Arr_Time, 
           Dep_Date, 
           Dep_Time, 
           Remarks, 
           approval, 
           Usercode, 
           DSO_UserCode, 
           HR_UserCode, 
           LastUpdated)
         VALUES
           (:Emp_No, 
            :Last_Name, 
            :Middle_Name, 
            :First_Name, 
            :E_Mail, 
            :Department_Code, 
            :Job_title, 
            :Trans_Date, 
            :Staff_Flag, 
            :Branch_Code, 
            :House_ID, 
            :Hotel_Code, 
            :Flat_No, 
            :Room_ID, 
            :Arr_Date, 
            :Arr_Time, 
            :Dep_Date, 
            :Dep_Time, 
            :Remarks, 
            :approval, 
            :Usercode, 
            :DSO_UserCode, 
            :HR_UserCode, 
            :LastUpdated)"; 
        OracleConnection con = new OracleConnection(constr); 
        OracleCommand com = new OracleCommand(query, con); 
        com.Parameters.AddWithValue(":Emp_No", c.gEmp_No); 
        com.Parameters.AddWithValue(":Last_Name", c.gLast_Name); 
        com.Parameters.AddWithValue(":Middle_Name", c.gMiddle_Name); 
        com.Parameters.AddWithValue(":First_Name", c.gFirst_Name); 
        com.Parameters.AddWithValue(":E_Mail", c.gEmail); 
        com.Parameters.AddWithValue(":Department_Code", c.gDepartment_Code); 
        com.Parameters.AddWithValue(":Job_title", c.gJob_title); 
        com.Parameters.AddWithValue(":Trans_Date", c.gTrans_Date); 
        com.Parameters.AddWithValue(":Staff_Flag", c.gStaff_Flag); 
        com.Parameters.AddWithValue(":Branch_Code", c.gBranch_Code); 
        com.Parameters.AddWithValue(":Hotel_Code", c.gHotel_Code); 
        com.Parameters.AddWithValue(":House_ID", c.gHouse_ID); 
        com.Parameters.AddWithValue(":Flat_No", c.gFlat_No); 
        com.Parameters.AddWithValue(":Room_ID", c.gRoom_ID); 
        com.Parameters.AddWithValue(":Arr_Date", c.gArr_Date); 
        com.Parameters.AddWithValue(":Arr_Time", c.gArr_Time); 
        com.Parameters.AddWithValue(":Dep_Date", c.gDep_Date); 
        com.Parameters.AddWithValue(":Dep_Time", c.gDep_Time); 
        com.Parameters.AddWithValue(":Remarks", c.gRemarks); 
        com.Parameters.AddWithValue(":approval", c.gapproval); 
        com.Parameters.AddWithValue(":Usercode", c.gUserCode); 
        com.Parameters.AddWithValue(":DSO_UserCode", c.gDSO_UserCode); 
        com.Parameters.AddWithValue(":HR_UserCode", c.gHR_UserCode); 
        com.Parameters.AddWithValue(":LastUpdated", c.gLastUpdated); 
        con.Open(); 
        com.ExecuteNonQuery(); 
        con.Close(); 
    

    Friday, July 20, 2012 3:15 PM
  • User1958825706 posted

    Thank you for your response, however, I have corrected the bound order by moving Hotel_Code to come immediately after Branch_Code then followed by House_ID as advised but the error: ORA-01008: Not all variables bound still comes up. Please I need help urgently.

    Saturday, July 21, 2012 6:47 AM
  • User269602965 posted

    The next thing you need to fix is the PARAMETER naming by removing the COLON symbol

    com.Parameters.AddWithValue(":Emp_No", c.gEmp_No);

    should be

    com.Parameters.AddWithValue("Emp_No", c.gEmp_No);  <<<NO COLON IN PARAMETER NAMING>>>

    The colon is only required in the insert statement

    INSERT ...  VALUES (:EmpNo)

    Saturday, July 21, 2012 8:39 AM
  • User1958825706 posted

    I have removed the colons as advised but when I click on the Save button on HotelRequest.aspx, it keeps showing me waiting for HotelRequest.aspx on the status bar of  IE.

    I have two classes Requests class where I decleared all the variables needed and RequestDAL class where I have the AddRequest method which should do the Insert. Now,  I have two aspx pages Default.aspx where I have some dropdownlist and textbox controls and HotelRequest.aspx. In the code behind of Default.aspx, I instantiated the class Requests and assigned values of controls on this page to some of the variables in the Requests class. On this page, I also have a Next button which when I click should take me to another page HotelRequest.aspx. Again on the code behind of HotelRequest page, I instantiated the class Requests and assign the values of controls to the remaining variables in the Requests class. (e.g Requests xxx = new Requests(); then on the selectindexchanged of a dropdownlist I did this xxx.gJob_title =  ddlDesignation.SelectedValue;). I also did RequestDAL request = new RequestDAL(); and Click method on the Save button I did request.AddRequest(xxx). What have I done wrong?

    Monday, July 23, 2012 9:22 AM
  • User269602965 posted

    INSERT INTO Request

    try adding the data schema name to the table name separated by period symbol

    INSERT INTO MyDataSchema.Request

    Ensure permissions to INSERT data are available to the connection account using the dataschema and table.

    Sorry for slow response, on another project day and night.

    Saturday, July 28, 2012 12:36 AM