inserting data using and oracle 8i

Posted by Lokeshbasana under ASP.NET on 6/25/2009 | Views : 8731 | Status : [Member] | Replies : 3
in web.config myconnection string

<add key="ConnectionString" value="Data Source=lok;User ID=lokesh;Password=lokesh;Unicode=True"/>

the query select *from tbluser is working fine in and oracle 8i database using System.Data.OracleClient But getting problem with
INSERT,UPDATE,Passing Parameter values

Showing Error in ExecuteNonQuery
ORA-01036: illegal variable name/number

public long AddEmpRegistrationDetails()
string query = "insert into TblEmpRegister(EmpID,ECode,EName,Designation,EmailID,MobileNo,ProdUserName,ProdUserPwd,ProdUserRole) values(IEmpID,IECode,IEName,IDesignation,IEmailID,IMobileNo,IProdUserName,IProdUserPwd,IProdUserRole)";

OracleCommand objCmd = new OracleCommand(query, objConn);
objCmd.CommandType = CommandType.Text;

objCmd.Parameters.Add(new OracleParameter("IEmpID",OracleType.Number)).Value = nEmpID;
objCmd.Parameters.Add(new OracleParameter("IECode",OracleType.VarChar,32)).Value = sECode;
objCmd.Parameters.Add(new OracleParameter("IEname",OracleType.VarChar,32)).Value = sEName;
objCmd.Parameters.Add(new OracleParameter("IDesignation",OracleType.VarChar,32)).Value = sDesignation;
objCmd.Parameters.Add(new OracleParameter("IEmailID",OracleType.VarChar,32)).Value = sEmailID;
objCmd.Parameters.Add(new OracleParameter("IMobileNo",OracleType.VarChar,32)).Value = sMobileNo;
objCmd.Parameters.Add(new OracleParameter("IProdUserName",OracleType.VarChar,32)).Value = sProdUserName;
objCmd.Parameters.Add(new OracleParameter("IProdUserPwd",OracleType.VarChar,32)).Value = sProdUserPwd;
objCmd.Parameters.Add(new OracleParameter("IProdUserRole",OracleType.VarChar,32)).Value = sProdUserRole;

long value = long.Parse(objCmd.ExecuteNonQuery().ToString()); //Error is
return value;

I have attached my project please help me solve the problem.

Thank you


Posted by: Raja on: 6/25/2009 [Member] Starter

Looks like the way of building the insert statement is wrong, the parameter name should start with : (semicolumn) in the insert query

for example if your
string query = "select count(deptno) from dept where " _

+ "dname = :dname and loc = :loc "; // C#

OracleParameter p1 = new OracleParameter("dname", OracleDbType.Varchar2);
p1.Value = textBox1.Text;

OracleParameter p2 = new OracleParameter("loc", OracleDbType.Varchar2);
p2.Direction = ParameterDirection.Input; ' optional
p2.Size = 13; ' optional
p2.Value = textBox2.Text;

Notice the words in bold in above.

So in your case, the query should look like

insert into TblEmpRegister(EmpID,ECode,EName,Designation,EmailID,MobileNo,ProdUserName,ProdUserPwd,ProdUserRole) values(:IEmpID,:IECode,:IEName,:IDesignation,:IEmailID,:IMobileNo,:IProdUserName,:IProdUserPwd,:IProdUserRole)

Hope this will help, for more example you can visit


Raja, USA

Lokeshbasana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lokeshbasana on: 6/25/2009 [Member] Starter

Thank u raja i solved my problem by changing column field using : symbol
but some other problem arise i.e

when i was using number datatype data is inserting successfully
i.e insert into employee(empCode,Salary) values(:pEmpcode,:pSalary)
here empcode and salary are number datatype

but when i was using varchar2 type getting error ORA-12571: TNS:packet writer failure

insert into employee(empname,Designation) values(:pEmpname,:pDesignation) //getting error when i was using varchar datatype

please tell me that Oracle.DataAccess.dll will support Oracle 8i

oracleDBtype.varchar2 is not supporting instead of this i am writing as
oracleType.Varchar this is also another problem what to do to support

Please send me solution as soon as possible getting errors while i am using string datatypes


Lokeshbasana, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja on: 6/26/2009 [Member] Starter

Login to post response