Get error at ExecuteNonQuery() during INSERT data into table

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.

Answer 1

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.

Answer 2

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. 

Answer 3

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.

Answer 4

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();

 

Answer 5

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.

 

Answer 6

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')