inserting data using asp.net and oracle 8i

Posted by Lokeshbasana under ASP.NET on 6/25/2009 | Views : 11121 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

in web.config myconnection string


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

the query select *from tbluser is working fine in asp.net 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;

        objConn.Open();
        long value = long.Parse(objCmd.ExecuteNonQuery().ToString());  //Error is 
        objConn.Close();
        return value;
    }
    #endregion

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

Thank you

Attachment

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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; cmd.Parameters.Add(p1); OracleParameter p2 = new OracleParameter("loc", OracleDbType.Varchar2); p2.Direction = ParameterDirection.Input; ' optional p2.Size = 13; ' optional p2.Value = textBox2.Text; cmd.Parameters.Add(p2); 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 http://msdn.microsoft.com/en-us/library/ms973871.aspx Thanks Cheers Regards, Raja, USA Lokeshbasana, if this helps please login to Mark As Answer. \| Alert Moderator

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;

cmd.Parameters.Add(p1);



OracleParameter p2 = new OracleParameter("loc", OracleDbType.Varchar2);

p2.Direction = ParameterDirection.Input; ' optional

p2.Size = 13; ' optional

p2.Value = textBox2.Text;

cmd.Parameters.Add(p2);

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 http://msdn.microsoft.com/en-us/library/ms973871.aspx

Thanks
Cheers

Regards,
Raja, USA

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

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

0	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 oracleDBtype.varchar2 Please send me solution as soon as possible getting errors while i am using string datatypes thanku Lokeshbasana, if this helps please login to Mark As Answer. \| Alert Moderator

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
oracleDBtype.varchar2

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

thanku

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

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

0	Try following links, this might help http://forums.asp.net/p/1188228/2033433.aspx#2033433 http://trac.doctrine-project.org/ticket/1592 http://searchoracle.techtarget.com/generic/0,295582,sid41_gci1049479,00.html Thanks Regards, Raja, USA Lokeshbasana, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts