4 Replies - 3169 Views - Last Post: 10 August 2012 - 10:02 AM Rate Topic: -----

#1 tonyogbuji  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 09-August 12

ORA-01008: not all variables bound error when inserting

Posted 09 August 2012 - 11:01 AM

I am getting the error ORA-01008 when I run the attached code.

The field Trans_ID has been set to auto increment by a trigger in the oracle database. What am I doing wrong?

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
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 HouseRequest : System.Web.UI.Page
{
    public string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["HouseMgtConnectionString"].ConnectionString;
    public static string employeeno;
    public static string lastname;
    public static string firstname;
    public static string middlename;
    public static string email;
    public static string department;
    public static string designation;
    public static string staff_flag;
    public DateTime arrdate;
    public DateTime depdate;


    /*public string employeeno;
    public string lastname;
    public string firstname;
    public string middlename;
    public string email;
    public string department;
    public string designation;
    public string staff_flag;*/

    //Requests xx = new Requests();
    //RequestDAL request = new RequestDAL();

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ddlBranch.Items.Insert(0, "--Select--");
            ddlHouse.Items.Insert(0, "--Select--");
            ddlFlat.Items.Insert(0, "--Select--");
            ddlRoom.Items.Insert(0, "--Select--");
            FillBranch();

            //FillddlRooms();
            if ((Request.QueryString["param1"] != null && Request.QueryString["param2"] != null) && Request.QueryString["param3"] != null && Request.QueryString["param4"] != null && Request.QueryString["param5"] != null && Request.QueryString["param6"] != null && Request.QueryString["param6"] != null && Request.QueryString["param7"] != null && Request.QueryString["param8"] != null)

            employeeno = Request.QueryString["param1"];
            lastname = Request.QueryString["param2"];
            firstname = Request.QueryString["param3"];
            middlename = Request.QueryString["param4"];
            email = Request.QueryString["param5"];
            department = Request.QueryString["param6"];
            designation = Request.QueryString["param7"];
            staff_flag = Request.QueryString["param8"];


        }

    }

    protected void ddlBranch_SelectedIndexChanged(object sender, EventArgs e)
    {
        ddlHouse.Items.Insert(0, "--Select--");
        string Branch_Code = ddlBranch.SelectedValue.ToString();

        FillHouse(Branch_Code);

    }
    protected void ddlHouse_SelectedIndexChanged(object sender, EventArgs e)
    {

        ddlFlat.Items.Insert(0, "--Select--");
        string House_ID = ddlHouse.SelectedValue.ToString();
        FillFlat(House_ID);

    }

    protected void ddlFlat_SelectedIndexChanged(object sender, EventArgs e)
    {

        ddlRoom.Items.Insert(0, "--Select--");
        string Flat_ID = ddlFlat.SelectedValue.ToString();
        FillRooms(Flat_ID);
    }

    private void FillBranch()
    {
        //string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["HouseMgtConnectionString"].ConnectionString;
        OracleConnection con = new OracleConnection(connectionstring);
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT Branch_Code, Description FROM Branch WHERE Status='A'";
        DataSet objDs = new DataSet();
        OracleDataAdapter dAdapter = new OracleDataAdapter();
        dAdapter.SelectCommand = cmd;
        con.Open();
        dAdapter.Fill(objDs);
        con.Close();
        if (objDs.Tables[0].Rows.Count > 0)
        {
            ddlBranch.DataSource = objDs.Tables[0];
            ddlBranch.DataTextField = "Description";
            ddlBranch.DataValueField = "Branch_Code";
            ddlBranch.DataBind();
            ddlBranch.Items.Insert(0, "--Select--");
            lblResults.Text = "";
        }
        else
        {
            lblResults.Text = "No Branch found";
            ddlHouse.Items.Clear();
            ddlFlat.Items.Clear();
            ddlRoom.Items.Clear();

        }
    }

    private void FillHouse(string Branch_Code)
    {
        //string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["HouseMgtConnectionString"].ConnectionString;
        OracleConnection con = new OracleConnection(connectionstring);
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT House_ID, Description FROM House WHERE Branch_Code =:Branch_Code AND Status='A'";
        cmd.Parameters.AddWithValue(":Branch_Code", Branch_Code);
        DataSet objDs = new DataSet();
        OracleDataAdapter dAdapter = new OracleDataAdapter();
        dAdapter.SelectCommand = cmd;
        con.Open();
        dAdapter.Fill(objDs);
        con.Close();
        if (objDs.Tables[0].Rows.Count > 0)
        {
            ddlHouse.DataSource = objDs.Tables[0];
            ddlHouse.DataTextField = "Description";
            ddlHouse.DataValueField = "House_ID";
            ddlHouse.DataBind();
            ddlHouse.Items.Insert(0, "--Select--");
            lblResults.Text = "";
        }
        else
        {
            lblResults.Text = "No House found";
            ddlHouse.Items.Clear();
            ddlFlat.Items.Clear();
            ddlRoom.Items.Clear();

        }
    }

    private void FillFlat(string House_ID)
    {
        //string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["HouseMgtConnectionString"].ConnectionString;
        OracleConnection con = new OracleConnection(connectionstring);
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT Flat_ID, Flat_No FROM Flats WHERE House_ID =:House_ID AND Status='A'";
        cmd.Parameters.AddWithValue(":House_ID", House_ID);
        DataSet objDs = new DataSet();
        OracleDataAdapter dAdapter = new OracleDataAdapter();
        dAdapter.SelectCommand = cmd;
        con.Open();
        dAdapter.Fill(objDs);
        con.Close();
        if (objDs.Tables[0].Rows.Count > 0)
        {
            ddlFlat.DataSource = null;
            ddlFlat.DataBind();
            ddlFlat.DataSource = objDs.Tables[0];
            ddlFlat.DataTextField = "Flat_No";
            ddlFlat.DataValueField = "Flat_ID";
            ddlFlat.DataBind();
            ddlFlat.Items.Insert(0, "--Select--");
            lblResults.Text = "";
        }
        else
        {
            lblResults.Text = "No Flat found";
            ddlFlat.Items.Clear();
            ddlRoom.Items.Clear();

        }
    }

    private void FillRooms(string Flat_ID)
    {
        //string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["HouseMgtConnectionString"].ConnectionString;
        OracleConnection con = new OracleConnection(connectionstring);
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT Room_ID, Room_No FROM Rooms WHERE Flat_ID =:Flat_ID AND Room_Status='0' AND Status='A'";
        cmd.Parameters.AddWithValue(":Flat_ID", Flat_ID);
        DataSet objDs = new DataSet();
        OracleDataAdapter dAdapter = new OracleDataAdapter();
        dAdapter.SelectCommand = cmd;
        con.Open();
        dAdapter.Fill(objDs);
        con.Close();
        if (objDs.Tables[0].Rows.Count > 0)
        {
            ddlRoom.DataSource = objDs.Tables[0];
            ddlRoom.DataTextField = "Room_No";
            ddlRoom.DataValueField = "Room_ID";
            ddlRoom.DataBind();
            ddlRoom.Items.Insert(0, "--Select--");
            lblResults.Text = "";
        }
        else
        {
            lblResults.Text = "No Room found";
            ddlRoom.Items.Clear();

        }
    }


    protected void btnSave_Click(object sender, EventArgs e)
        //(int? Trans_ID, string Emp_No, string Last_Name, string Middle_Name, string First_Name, string E_Mail, string Department_Code, string Job_title, DateTime Trans_Date, string Staff_Flag, string Branch_Code, string Hotel_Code, string House_ID, string Flat_No, string Room_ID, DateTime Arr_Date, string Arr_Time, DateTime Dep_Date, string Dep_Time, string Remarks, string approval, string Usercode, string DSO_UserCode, string HR_UserCode, DateTime LastUpdated)
    {

        arrdate = DateTime.Parse(txtArrivalDate.Text);
        depdate = DateTime.Parse(txtDepDate.Text);
        OracleConnection con = new OracleConnection(connectionstring);

        string InsertSQL = "INSERT INTO Request(Trans_ID, 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(:Trans_Id, :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)";



        

            OracleCommand com = new OracleCommand(InsertSQL, con);

            com.Parameters.AddWithValue(":Trans_Id", null);
            com.Parameters.AddWithValue(":Emp_No", employeeno);
            com.Parameters.AddWithValue(":Last_Name", lastname);
            com.Parameters.AddWithValue(":Middle_Name", middlename);
            com.Parameters.AddWithValue(":First_Name", firstname);
            com.Parameters.AddWithValue(":E_Mail", email);
            com.Parameters.AddWithValue(":Department_Code", department);
            com.Parameters.AddWithValue(":Job_title", designation);
            com.Parameters.AddWithValue(":Trans_Date", DateTime.Now);
            com.Parameters.AddWithValue(":Staff_Flag", staff_flag);
            com.Parameters.AddWithValue(":Branch_Code", ddlBranch.SelectedItem.Value);
            com.Parameters.AddWithValue(":Hotel_Code", "na");
            com.Parameters.AddWithValue(":House_ID", ddlHouse.SelectedItem.Value);
            com.Parameters.AddWithValue(":Flat_No", ddlFlat.SelectedItem.Value);
            com.Parameters.AddWithValue(":Room_ID", ddlRoom.SelectedItem.Value);
            com.Parameters.AddWithValue(":Arr_Date", arrdate);
            com.Parameters.AddWithValue(":Arr_Time", (txtArrivalTime.Text).ToString());
            com.Parameters.AddWithValue(":Dep_Date", depdate);
            com.Parameters.AddWithValue(":Dep_Time", (txtDepTime.Text).ToString());
            com.Parameters.AddWithValue(":Remarks", txtRemarks.Text);
            com.Parameters.AddWithValue(":approval", "na");
            com.Parameters.AddWithValue(":Usercode", User.Identity.Name.ToUpper());
            com.Parameters.AddWithValue(":DSO_UserCode", "na");
            com.Parameters.AddWithValue(":HR_UserCode", "na");
            com.Parameters.AddWithValue(":LastUpdated", DateTime.Now);




int added = 0;

            try
            {
                con.Open();
                com.CommandType = CommandType.Text;
                added= com.ExecuteNonQuery();
                lblResults.Text = added.ToString() + " record inserted.";
        





        }

        catch (System.Data.OracleClient.OracleException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            con.Close();
        }
    }


This post has been edited by Curtis Rutland: 09 August 2012 - 03:29 PM
Reason for edit:: removed attachment, pasted code in body


Is This A Good Question/Topic? 0
  • +

Replies To: ORA-01008: not all variables bound error when inserting

#2 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4559
  • View blog
  • Posts: 7,980
  • Joined: 08-June 10

Re: ORA-01008: not all variables bound error when inserting

Posted 09 August 2012 - 03:30 PM

Since this is a C# issue, moved to C#.
Was This Post Helpful? 0
  • +
  • -

#3 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3650
  • View blog
  • Posts: 11,419
  • Joined: 05-May 12

Re: ORA-01008: not all variables bound error when inserting

Posted 09 August 2012 - 03:51 PM

If it is autoincrement, then you don't need to insert a value on lines 237 and 245.
Was This Post Helpful? 0
  • +
  • -

#4 tonyogbuji  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 09-August 12

Re: ORA-01008: not all variables bound error when inserting

Posted 10 August 2012 - 09:34 AM

@SkyDiver, I have tried not inserting data into Trans_ID since it is auto-increment but I am still geting the error.
Was This Post Helpful? 0
  • +
  • -

#5 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3650
  • View blog
  • Posts: 11,419
  • Joined: 05-May 12

Re: ORA-01008: not all variables bound error when inserting

Posted 10 August 2012 - 10:02 AM

You'll have to post your updated code and the complete error.

Have you tried stepping through the code and made sure that none of the parameters passed to AddWithValue() are nulls? Additionally, does the exception thrown at least hint at which parameters were not bound?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1