How to upsert row into DB2 iSeries using parameters in C# ADO.NET? -

i'm trying write sql statement insert multiple rows in batch parameters table when row doesn't exist in target table.

i have problem how pass parameter markers sql query. when use code below got exception: "sql0584 null or parameter marker in values not allowed."

using (var conn = new idb2connection(_connectionstring)) {     await conn.openasync();     using (var tran = conn.begintransaction()) {         using (var cmd = conn.createcommand()) {             cmd.transaction = tran;             cmd.commandtext = @"                 merge tablexyz mt                  using (                     values(@column1, @column2)                 ) vt(column1, column2)                 on (                     mt.column1 = vt.column1 , mt.column2 = vt.column2                 )                 when not matched                     insert (column1, column2) values (vt.column1, vt.column2)             ";             cmd.deriveparameters();              foreach (var item in items) {                  cmd.parameters["@column1"].value = item.column1;                 cmd.parameters["@column2"].value = item.column2;                 cmd.addbatch();             }              await cmd.executenonqueryasync();         }         tran.commit();     } } 

any suggestions, please?

the question how pass parameter markers merge query. there no problem c# code , it's not helpful send answers how pass parameters in insert or update statements.

thanks.

thank you, @mustaccio!

the explicit datatype in values(...) statement helped.

cmd.commandtext = @"     merge tablexyz mt      using (         values(cast(@column1 bigint), cast(@column2 bigint))     ) vt(column1, column2)     on (         mt.column1 = vt.column1 , mt.column2 = vt.column2     )     when not matched         insert (column1, column2) values (vt.column1, vt.column2) ";