locked
Get error at ExecuteNonQuery() during INSERT data into table RRS feed

  • Question

  • User-721945135 posted

    Hi,

    In my program, during insert the data into table, I get this error: 'One or more errors occurred during processing of command. ORA-00936: missing expression' at ExecuteNonQuery().

    This is the table I try to insert the data.

    CREATE TABLE Product
    (
    name        varchar2(10) not null,
    category    varchar2(10) not null,
    facility    varchar2(10) not null,
    quantity    numeric(20,0) not null, 
    time_stamp  date not null
    )

    Below is the code for insert the data.

    public static string cs = "Provider=MSDAORA; Data Source=xxx; User ID=xxx; Password=xxx";
                        
    public void insertCom()
            {
                DataTable dt = new DataTable();
                dt = _Ds.Tables[0].Copy();
    
                OleDbConnection con = new OleDbConnection(cs);
                con.Open();
    
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    string name = dt.Rows[j]["name"].ToString();
                    string category = dt.Rows[j]["category"].ToString();
                    string facility = dt.Rows[j]["facility"].ToString();
                    int qty = int.Parse(dt.Rows[j]["quantity"].ToString());
                    string timestamp = dt.Rows[j]["time_stamp"].ToString();
    
                    string insert = "INSERT INTO Product (name, category, facility, quantity, time_stamp)" +
                                    " VALUES (@name, @category, @facility, @quantity, to_date('@timestamp', 'mm/dd/yyyy'))";
    
                    OleDbCommand cmd = new OleDbCommand(insert, con);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@category", category);
                    cmd.Parameters.AddWithValue("@facility", facility);
                    cmd.Parameters.AddWithValue("@quantity", qty);
                    cmd.Parameters.AddWithValue("@time_stamp", timestamp);
    
                    cmd.ExecuteNonQuery();  // <-- ERROR OCCUR HERE !!
                }
                con.Close();
            }

    I did search for the solution for this error, unfortunately still cannot be solve. I have no idea how to solve this.

    Tuesday, October 9, 2012 10:51 AM

Answers

  • User-721945135 posted

    Hi, I problem solved already.

    Now I can insert the data into table if I use below method... but if using the previous one problem still exist, I dont know why. Here is the solution:

    string insert = "INSERT INTO Product(name, category, facility, quantity, time_stamp)";
    insert += " VALUES (";
    insert += "'" + name + "'";
    insert += ", '" + category + "'";
    insert += ", '" + facility + "'";
    insert += ", '" + quantity + "'";
    insert += ", to_date('" + time_stamp + "', 'mm/dd/yyyy')";
    insert += ")";
    cmd = new OleDbCommand(insert, con);
    
    cmd.ExecuteNonQuery();

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 10, 2012 6:02 AM

All replies

  • User1854529396 posted

    Hi,

    Please use this code

    DateTime timestamp=Convert.ToDateTime(dt.Rows[j]["time_stamp"].ToString());
    
    string timestamp =string.Format("{0:mm/dd/yyyy}",timestamp);
    
    
    
                    string insert = "INSERT INTO Product (name, category, facility, quantity, time_stamp)" +
                                    " VALUES (@name, @category, @facility, @quantity, @timestamp)";

    i hope it help you.

    Tuesday, October 9, 2012 12:29 PM
  • User-1005100348 posted

    Hi,

    You can also try removing the single quotes around the parameter:

    string insert = "INSERT INTO Product (name, category, facility, quantity, time_stamp)" +
                    " VALUES (@name, @category, @facility, @quantity, to_date(@timestamp, 'mm/dd/yyyy'))";

    Hope this helps. 

    Tuesday, October 9, 2012 2:37 PM
  • User-721945135 posted

    Hi dinakaran, spapim,

    Thanks for reply. 

    I did try on the solution you give but still not able to insert the data and get the same error. I'm not sure where else go wrong.

    Tuesday, October 9, 2012 9:15 PM
  • User-721945135 posted

    Hi, I problem solved already.

    Now I can insert the data into table if I use below method... but if using the previous one problem still exist, I dont know why. Here is the solution:

    string insert = "INSERT INTO Product(name, category, facility, quantity, time_stamp)";
    insert += " VALUES (";
    insert += "'" + name + "'";
    insert += ", '" + category + "'";
    insert += ", '" + facility + "'";
    insert += ", '" + quantity + "'";
    insert += ", to_date('" + time_stamp + "', 'mm/dd/yyyy')";
    insert += ")";
    cmd = new OleDbCommand(insert, con);
    
    cmd.ExecuteNonQuery();

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 10, 2012 6:02 AM
  • User685609214 posted

    hi, i have another problem:

    IDbCommand cmd = null;

    bool wasClosed = cnn.State == ConnectionState.Closed;

    cmd = SetupCommand(cnn, transaction, sql, paramReader, obj, commandTimeout, commandType);

    if (wasClosed) cnn.Open();

    return cmd.ExecuteNonQuery(); <--here the error

    whe I evaluate this operation I get this code:

    insert into treserva(descripcion,descripcionDetallada,IdUsuarioQueReserva,IdUsuarioQueAutoriza,FechaCreacion,FechaReservaInicio,FechaReservaFin,Estado,Color) values('((E'rrr')::text)','((E'fffff')::text)',6,4,'((E'2013-06-04 00:00:00.000000')::timestamp)','((E'2013-06-04 13:37:48.000000')::timestamp)','((E'2013-06-04 15:37:48.000000')::timestamp)','solicitado','sdfsdf');

    and the error is: ((E'fffff')::text) only I should get fffff.

    what could I do?

    Jhombert

    Thank you.

     

    Tuesday, June 4, 2013 4:36 PM
  • User-721945135 posted

    Try this :

    insert into treserva(descripcion,descripcionDetallada,IdUsuarioQueReserva,IdUsuarioQueAutoriza,FechaCreacion,FechaReservaInicio,FechaReservaFin,Estado,Color) 
    values('rrr','ffff',6,4,to_date('2013-06-04 13:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2013-06-04 13:37:48','yyyy-mm-dd hh24:mi:ss'),to_date('2013-06-04 15:37:48','yyyy-mm-dd hh24:mi:ss'),'solicitado','sdfsdf')



    Wednesday, June 5, 2013 1:23 AM