I have been having challenges inserting data into an oracle table. Please find attaced my source code. Whenever I click the Save button I get the exception ORA-01008: Not all variables bound. Please note that there is a trigger in the schema that is set to auto increment Trans_ID by 1 whenever a row is to be inserted into the table Request.
I dont know what I am doing wrongly. Please help!
Below is the script for the table
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();
}
}
Below is the script for creating the table Request
CREATE TABLE request
(
Trans_ID NUMBER(38) NOT NULL,
Emp_No VARCHAR2(9),
Last_Name VARCHAR2(25) NOT NULL,
Middle_Name VARCHAR2(25),
First_Name VARCHAR2(25) NOT NULL,
E_Mail VARCHAR2(25) NOT NULL,
Department_Code VARCHAR2(7),
Job_title VARCHAR2(25),
Trans_Date DATE NOT NULL,
Staff_Flag CHAR(1) DEFAULT '0',
Branch_Code VARCHAR2(7),
Hotel_Code VARCHAR2(7),
House_ID VARCHAR2(7),
Flat_No VARCHAR2(7),
Room_ID VARCHAR2(7),
Arr_Date DATE,
Arr_Time VARCHAR2(8),
Dep_Date DATE,
Dep_Time VARCHAR2(8),
Remarks VARCHAR2(35),
approval VARCHAR2(2),
Usercode VARCHAR2(20),
DSO_UserCode VARCHAR2(20),
HR_UserCode VARCHAR2(20),
LastUpdated DATE DEFAULT sysdate NOT NULL,
CONSTRAINT fk_Trans_one FOREIGN KEY(Branch_Code) REFERENCING Branch,
CONSTRAINT fk_Trans_two FOREIGN KEY(House_ID) REFERENCING House,
CONSTRAINT fk_Trans_three FOREIGN KEY(Room_ID) REFERENCING Rooms,
CONSTRAINT fk_Trans_Four FOREIGN KEY(Hotel_Code) REFERENCING Hotels,
CONSTRAINT fk_Trans_Five FOREIGN KEY(Emp_No) REFERENCING peoplesmandev.employee_master(emp_no),
CONSTRAINT pk_tran PRIMARY KEY (Trans_ID)
);