parameterized insert query error

Question

I am getting following Oracle error when I am trying to insert using parameterized query:

ORA-01036: illegal variable name/number

My code is:

OracleTransaction myTrans;
using (myTrans = myConnection.BeginTransaction())
{
  try
  {
    OracleCommand cmd = new OracleCommand("INSERT INTO TABLE_NAME(GKEY,FILE_NAME,CONTAINER_NO) VALUES (1,@file_name,@container_no)", myConnection);
    cmd.Parameters.AddWithValue("@file_name", file_name);
    cmd.Parameters.AddWithValue("@container_no", container_no);
    cmd.Transaction = myTrans;
    cmd.ExecuteNonQuery();
  }
  catch (Exception ex)
  {
    myTrans.Rollback();
  }
  finally
  {
    myTrans.Commit();
  }
}

When I use without parametrized query it working without any errors, please help me to solve this. Thanks

EDIT: I think this question is not a duplicate question, the answer provided below worked for me. Here I used "@" sign that is the main cause of the error. Please re-consider. Thanks

@Jerodev Oracle does not support binding variables using parameterized query for insert? — imsome1, Sep 05 '17 at 07:36

score 4 · Accepted Answer · answered Sep 05 '17 at 07:54

I think the problem here might be related to parameter syntax. Try to use : instead @

OracleCommand cmd = new OracleCommand("INSERT INTO TABLE_NAME(GKEY,FILE_NAME,CONTAINER_NO) VALUES (1,:file_name,:container_no)", myConnection);
cmd.Parameters.AddWithValue("file_name", file_name);
cmd.Parameters.AddWithValue("container_no", container_no);

When using named parameters in an SQL statement called by an OracleCommand of CommandType.Text, you must precede the parameter name with a colon (:). However, in a stored procedure, or when referring to a named parameter elsewhere in your code (for example, when adding OracleParameter objects to the Parameters property), do not precede the named parameter with a colon (:)

https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters(v=vs.110).aspx

parameterized insert query error

1 Answers1