ASP.NET

ORA-01008: Not all variables bound RSS

5 replies

Last post Jul 28, 2012 04:36 AM by Lannie

‹ Previous Thread|Next Thread ›

togbuji

Member

16 Points

8 Posts

ORA-01008: Not all variables bound

Jul 20, 2012 05:18 PM|LINK

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; }
}

Edited by togbuji on Jul 20, 2012 05:34 PM

Lannie

Contributor

3736 Points

727 Posts

Re: ORA-01008: Not all variables bound

Jul 20, 2012 07:15 PM|LINK

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();

- Reply
togbuji

Member

16 Points

8 Posts

Re: ORA-01008: Not all variables bound

Jul 21, 2012 10:47 AM|LINK

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.
- Edited by togbuji on Jul 21, 2012 10:49 AM
- Reply
Lannie

Contributor

3736 Points

727 Posts

Re: ORA-01008: Not all variables bound

Jul 21, 2012 12:39 PM|LINK

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)
- Reply
togbuji

Member

16 Points

8 Posts

Re: ORA-01008: Not all variables bound

Jul 23, 2012 01:22 PM|LINK

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?
- Reply
Lannie

Contributor

3736 Points

727 Posts

Re: ORA-01008: Not all variables bound

Jul 28, 2012 04:36 AM|LINK

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.

‹ Previous Thread|Next Thread ›

ORA-01008: Not all variables bound RSS

5 replies

togbuji

ORA-01008: Not all variables bound

Lannie

Re: ORA-01008: Not all variables bound

togbuji

Re: ORA-01008: Not all variables bound

Lannie

Re: ORA-01008: Not all variables bound

togbuji

Re: ORA-01008: Not all variables bound

Lannie

Re: ORA-01008: Not all variables bound

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft