none
"ORA-01036: illegal variable name/number" returned when running OracleDataAdapter.Update() RRS feed

  • Question

  • Hi, I was using OracleDataAdapter.Update(Rows[]) to insert rows into the ORACLE database but I got "ORA-01036: illegal variable name/number" error at the .Update() line.

     

    The code is shown below:

     

    for (int rc = 0; rc < tempSQL.Rows.Count; rc++)

    {

    oraAdapter.InsertCommand = new OracleCommand("INSERT INTO " + tablename + " (:columnNames) VALUES (Stick out tonguearamValues)", oraConn);

    OracleParameter pv = new OracleParameter();

    pv.ParameterName = "Stick out tonguearamValues";

    OracleParameter cn = new OracleParameter();

    cn.ParameterName = ":columnNames";

    string strValue = ":";

    for (int cc = 0; cc < tempSQL.Columns.Count; cc++)

    {

    if (tempSQL.Rows[rc][cc].ToString() == "")

    {

    strValue = "NULL";

    }

    else

    {

    switch (tempSQL.Columns[cc].DataType.ToString())

    {

    case "System.DateTime":

    strValue = "to_date('" + tempSQL.Rows[rc][cc].ToString().Trim() + "', 'dd/mm/yyyy hh:miTongue Tieds')";

    break;

    case "System.Int32":

    strValue = tempSQL.Rows[rc][cc].ToString().Trim();

    break;

    default:

    strValue = "'" + tempSQL.Rows[rc][cc].ToString().Trim() + "'";

    break;

    }

    }

    if (cc == tempSQL.Columns.Count - 1)

    {

    pv.Value += strValue;

    cn.Value += " \'" + tempSQL.Columns[cc].ColumnName + "\'";

    }

    else

    {

    pv.Value += strValue + ", ";

    cn.Value += " \'" + tempSQL.Columns[cc].ColumnName + "\', ";

    }

    }

    oraAdapter.InsertCommand.Parameters.Add(pv);

    oraAdapter.InsertCommand.Parameters.Add(cn);

    result = oraAdapter.Update(new DataRow[] { tempSQL.Rows[rc] });

    }

    }

     

    Can any one help please?

     

     

    Thanks!

    Sunday, November 16, 2008 9:53 PM

All replies

  • AFAIK, you can't pass column names as paramters or multiple column values as a single parameter . If you want to implement this type of query then you're going to have to pass the SQL string as a parameter into a stored procedure and have the stored procedure execute the SQL statement dynamically. 

     

    Monday, November 17, 2008 1:42 PM
  • Thanks Paul for the reply, I now realise that you can't really pass column names as parameters. I've changed that.

     

    But I still have problems. I've changed the code so that it's now using an array of OracleParameters as the InsertCommand parameters, several of which are DateTime type.

     

    The ORACLE database I'm accessing is using dd-mm-yyyy date format, so in the code I've changed the date format and parse the default date format (dd/mm/yyyy hh:mmTongue Tieds) to the one that matches the ORACLE date type.

     

    The issue is that the ORACLE column only accepts short date format (dd-mm-yyyy) and does not accept any time info. I've tried to use the short date in my code but the DateTime object value still has the time in it (e.g. "30-11-2007 19:23:44"). And I think it's causing an error message when the OracleDataAdapter.Update() got executed.

     

    Any suggestions?

     

     

    Thanks,

    F.

     

    Tuesday, November 18, 2008 9:42 PM
  • Could you post the code with your changes so I can see what you are doing? I can't determine whether you are using a stored procedure or not.

     

    Also, if you're still receiving an error please post that as well.

    Wednesday, November 19, 2008 3:22 AM