Trying to insert value in oracle db but getting "illegal variable name/number" issue

Question

I'm trying to insert values in oracle table from asp .net but getting issue "illegal variable name/number" issue at

cmd.ExecuteNonQuery();

while data passed to SaveFrmDetails are given below .Any help would be appreciated.

user = {Product_Id: "1", Tdcno: "tw2", Revision: "0", Revision_Date: "23-Nov-2017", P_Group: "Chain Link",Prod_Desc: "descr",N_I_Prd_Std:"india" ,Appln:"appllll",Frm_Supp:"Frmm",Created_Date: "23-nov-2017",Created_By:"Mohan"}

class property

public class User
{
    public decimal Product_Id { get; set; }
    public string Tdcno { get; set; }
    public decimal Revision { get; set; }
    public DateTime Revision_Date { get; set; }
    public string P_Group { get; set; }
    public string Prod_Desc { get; set; }
    public string N_I_Prd_Std { get; set; }
    public string Appln { get; set; }
    public string Frm_Supp { get; set; }
    public DateTime Created_Date { get; set; }
    public string Created_By { get; set; }
}

and the date part which i am sending in user object through ajax call is like this

 var monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun","Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
                var date = new Date();
                var val = date.getDate() + "-" + monthNames[date.getMonth()] + "-" + date.getFullYear();
 $("#Revision_Date").text(val);

value passed in SaveFrmDetails webmethod through ajax using user object

user.Revision_Date = $("[id*=Revision_Date]").text();

c# code

public static void SaveFrmDetails(User user)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["conndbprodnew"].ConnectionString;
 using (OracleConnection con = new OracleConnection(connectionString))
        {
            using (OracleCommand cmd = new OracleCommand("INSERT INTO TDC_PRODUCT1(PRODUCT_ID,TDC_NO, REVISION,REVISION_DATE,P_GROUP,PROD_DESC,N_I_PRD_STD,APPLN,FRM_SUPP,CREATED_DATE,CREATED_BY) VALUES (:Product_Id,:Tdc_No,:Revision,:Revision_Date,:P_Group,:Prod_Desc,:N_I_Prd_Std,:Appln,:Frm_Supp,:Created_Date,:Created_By)"))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("Product_Id", user.Product_Id);
                cmd.Parameters.AddWithValue("Tdc_No", user.Tdcno);
                cmd.Parameters.AddWithValue("Revision", user.Revision);
                cmd.Parameters.AddWithValue("Revision_Date", user.Revision_Date);
                cmd.Parameters.AddWithValue("P_Group", user.P_Group);
                cmd.Parameters.AddWithValue("Prod_Desc", user.Prod_Desc);
                cmd.Parameters.AddWithValue("N_I_Prd_Std", user.N_I_Prd_Std);
                cmd.Parameters.AddWithValue("Appln", user.Appln);
                cmd.Parameters.AddWithValue("Frm_Supp", user.Frm_Supp);
                cmd.Parameters.AddWithValue("Created_Date", user.Created_By);
                cmd.Parameters.AddWithValue("Created_By", user.Created_By);

                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

oracle table design

create table TDC_PRODUCT1
(
  PRODUCT_ID    NUMBER(10) not null,
  TDC_NO        VARCHAR2(10) not null,
  REVISION      NUMBER(10) not null,
  REVISION_DATE DATE,
  P_GROUP       VARCHAR2(100) not null,
  PROD_DESC     VARCHAR2(100) not null,
  N_I_PRD_STD   VARCHAR2(100) not null,
  APPLN         VARCHAR2(100) not null,
  FRM_SUPP      VARCHAR2(100) not null,
  CREATED_DATE  DATE,
  CREATED_BY    VARCHAR2(30) not null
)

score 0 · Answer 1 · answered Nov 23 '17 at 11:00

First of all, you should use placeholders. When parsing

INSERT INTO TDC_PRODUCT1 VALUES(Product_Id,Tdc_No, Revision,Revision_Date,P_Group,Prod_Desc,N_I_Prd_Std,Appln,Frm_Supp,Created_Date,Created_By)

Oracle cannot locate any place to put parameter value. Use :Product_Id, :Tdc_No and so on instead.

Next, it's bad practise to use such INSERT without column list. It means your application would be broken every time you add new column into the table.

score 0 · Accepted Answer · answered Nov 23 '17 at 11:10

0

Change

INSERT INTO TDC_PRODUCT1 VALUES(Product_Id,Tdc_No, Revision,Revision_Date,P_Group,Prod_Desc,N_I_Prd_Std,Appln,Frm_Supp,Created_Date,Created_By)

To

INSERT INTO TDC_PRODUCT1 (Product_Id,Tdc_No, Revision,Revision_Date,P_Group,Prod_Desc,N_I_Prd_Std,Appln,Frm_Supp,Created_Date,Created_By)
VALUES (:Product_Id,:Tdc_No,:Revision,:Revision_Date,:P_Group,:Prod_Desc,:N_I_Prd_Std,:Appln,:Frm_Supp,:Created_Date,:Created_By)

You (optionally) specify the column names before the VALUES keyword but if you are using bind variables then you need to prefix those variables with a colon : to indicate this.

answered Nov 23 '17 at 11:10

MT0

143,790
11
59
117

it is working fine but i am gettting error "a non-numeric character was found where a numeric was expected" .i search for this issue i found it date related issue and the property which i used for holding values i am adding that class details in my question and the date part how i used in my code i also adding it. – hari Nov 23 '17 at 11:57
@hari Do not pass strings where it is expecting dates - [use DateTime](https://stackoverflow.com/a/15575324/1509264) or another similar [answer](https://stackoverflow.com/a/6570665/1509264) – MT0 Nov 23 '17 at 12:23
ichanged the datatype of Revision_Date & Created_Date property in User Class and also insert the values in query as you mentioned in values wise as you mentioned in SaveFrmDetails method. Now cmd.ExecuteNonQuery(); method also got executed but data is not stored.i tried a lot but not getting any idea – hari Nov 24 '17 at 11:12
@hari Trying to debug this via comments is going to be very difficult - you would be better served posting a new question with the C# code you are now using and as many details of the issues you are having as you can and seeing if a C# expert (which I am not) can help you. – MT0 Nov 24 '17 at 11:26

Trying to insert value in oracle db but getting "illegal variable name/number" issue

2 Answers2