hari hari - 17 days ago 6
ASP.NET (C#) Question

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

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
)

MT0 MT0
Answer Source

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.