Unable to Insert record In oracle Database

[JawwadLone]

hi
i m trying to insert a record from ASP.net application into oracle.I m using Microsoft Enterprise Application Data Access Block. I have been getting this same error:

"ORA-01036: illegal variable name/number String

I have the pasted the ASP.net Application code and OracleProcedure code Below:

--------Application Code--------------------------

    Function addemployee(ByVal objemployeeDetails As employeeDetails)
        Dim db As Oracle.OracleDatabase = DatabaseFactory.CreateDatabase
        Dim dbcmd As Oracle.OracleCommandWrapper = db.GetStoredProcCommandWrapper("pack_employee.Addemployee")
        Try
            db.ClearParameterCache()
            dbcmd.AddInParameter("pr_name", DbType.String, objemployeeDetails.pr_name)
            dbcmd.AddInParameter("father", DbType.String, objemployeeDetails.father)
            dbcmd.AddInParameter("office_id", DbType.Int64, 1)  ' objemployeeDetails.office_id)
            dbcmd.AddInParameter("pr_alias", DbType.String, objemployeeDetails.pr_Alias)
            dbcmd.AddInParameter("pr_category", DbType.String, objemployeeDetails.category)
            dbcmd.AddInParameter("Status", DbType.String, objemployeeDetails.Status)
            dbcmd.AddInParameter("pr_class", DbType.String, objemployeeDetails.pr_class)
            dbcmd.AddInParameter("religion", DbType.String, objemployeeDetails.religion)
            dbcmd.AddInParameter("pr_cast", DbType.String, objemployeeDetails.cast)
            dbcmd.AddInParameter("gender", DbType.String, objemployeeDetails.gender)
            dbcmd.AddInParameter("dob", DbType.Date, objemployeeDetails.dob)
            dbcmd.AddInParameter("cnic", DbType.String, objemployeeDetails.cnic)
            dbcmd.AddInParameter("street", DbType.String, objemployeeDetails.street)
            dbcmd.AddInParameter("mouza", DbType.String, objemployeeDetails.mouza)
            dbcmd.AddInParameter("district", DbType.String, objemployeeDetails.district)
            dbcmd.AddInParameter("province", DbType.String, objemployeeDetails.province)
            dbcmd.AddInParameter("country", DbType.String, objemployeeDetails.country)
            dbcmd.AddInParameter("marital_status", DbType.String, objemployeeDetails.marital_status)
            dbcmd.AddInParameter("education", DbType.String, objemployeeDetails.education)
            dbcmd.AddInParameter("occupation", DbType.String, objemployeeDetails.occupation)
            dbcmd.AddInParameter("identification_mark1", DbType.String, objemployeeDetails.identification_mark1)
            dbcmd.AddInParameter("scar", DbType.String, objemployeeDetails.scar)
            dbcmd.AddInParameter("height", DbType.String, objemployeeDetails.height)
            dbcmd.AddInParameter("weight", DbType.String, objemployeeDetails.weight)
            dbcmd.AddInParameter("health", DbType.String, objemployeeDetails.health)
            dbcmd.AddInParameter("barrack_id", DbType.UInt16, 1)    ' objemployeeDetails.barrrak_id)
            dbcmd.AddInParameter("Identification_mark2 ", DbType.String, objemployeeDetails.Identification_mark2)
            dbcmd.AddInParameter("street2", DbType.String, objemployeeDetails.street2)
            dbcmd.AddInParameter("mouza2", DbType.String, objemployeeDetails.mouza2)
            dbcmd.AddInParameter("district2", DbType.String, objemployeeDetails.district2)
            dbcmd.AddInParameter("province2", DbType.String, objemployeeDetails.province2)
            dbcmd.AddInParameter("country2", DbType.String, objemployeeDetails.country2)
            db.ExecuteNonQuery(dbcmd)
        Catch ex As Exception

        End Try

    End Function

--------Oracle Procedure Code--------------------------

  procedure Addemployee(
  pr_name in varchar2, 
     father in varchar2, 
     office_id in number, 
     pr_Alias in varchar2, 
     pr_category in varchar2, 
     Status in varchar2, 
     pr_class in varchar2, 
     religion in varchar2, 
     pr_cast in varchar2,
     gender in varchar2, 
     dob in date,
     cnic in varchar2, 
     street in varchar2, 
     mouza in varchar2, 
     district in varchar2, 
     province in varchar2, 
     country in varchar2, 
     marital_status in varchar2, 
     education in varchar2, 
     occupation in varchar2, 
     identification_mark1 in varchar2, 
     scar in varchar2, 
     height in varchar2, 
     weight in varchar2, 
     health in varchar2, 
     barrack_id in number,
     Identification_mark2 in varchar2 ,
     street2 in varchar2, 
     mouza2 in varchar2, 
     district2 in varchar2, 
     province2 in varchar2, 
     country2 in varchar2)
is  
Begin
 INSERT INTO pr_main VALUES(pmis.seq_pr_main.nextval ,    pr_name ,      father ,      office_id ,      pr_Alias,      pr_category ,      Status ,      pr_class ,      religion ,      pr_cast,     gender ,      
to_date(dob,'DD/MM/yyyy'),      cnic ,      street ,      mouza ,      district ,      province ,      country ,      marital_status ,      education ,      occupation ,      identification_mark1 ,      scar ,      height ,      weight ,      health ,      barrack_id ,     Identification_mark2 ,     street2 ,      mouza2 ,      district2 ,      province2 ,      country2 );
 end Addemployee;

I m looking foward for a positive a response.

Regards,
Jawwd

 

[chiph]

Which value is it complaining about?
You might examine the .innerException property to see if it has more information for you.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[JawwadLone]

The innerException property show Nothing.

Regards,
Jawwad.

 

[cappmgr]

I have no answer, the way the sproc is written it's hard to tell. I would change my stored procedure.
Use %TYPE versus a datatype for your input and output parameters.
pr_name in varchar2 would be
pr_name in TableName.ColumnName%TYPE

list the columns versus the table only.
INSERT INTO Table_Name
(column1
,column2
,column3)
values
(value1
,value2
,value3);

This is for maintainability and clarity. If the table changes you will not have to change your sproc. Also catch the SQL Exception in you DAL. Have you used the sproc in a stub successfully?
Marty