Insert into .dbf with Conditional Subquery on VFPOLEDB from C#

Question

I ´ve the challenge to implement a query to insert into a dbf. I am working on C#. If I am using a query in a Sql works fine but no on VFPOLEDB. The things is that I have no idea to makes it work.

Ok. This is what I have implement to give a try to see if works. My query:

    string query = "INSERT INTO relojj (id, numdoc, fecha, maquina, tipo, veces, sucursal,                     transf, forma, audito, newfld ) " +
                                "VALUES (@id,@numdoc,@fecha,@maquina,@sucursal," +
                                "IIF((SELECT COUNT(id) FROM relojj WHERE numdoc=@numdoc and fecha=@fecha)%2=0 , 'E', 'S')," +
                                "(SELECT COUNT(id) FROM relojj WHERE numdoc=@numdoc and fecha=@fecha)," +
                                "@sucursal,@transf,@forma,@audito,@newfld)";

Then I have instance a command to insert those values into the dbf

    command.Parameters.Add("id", OleDbType.VarChar, 5).Value = "1";
    command.Parameters.Add("numdoc", OleDbType.Decimal).Value = 1;      
    command.Parameters.Add("fecha", OleDbType.Date).Value = fecha;
    command.Parameters.Add("maquina", OleDbType.VarChar, 10).Value = "Reloj Rio Grande";
    //command.Parameters.Add("tipo", OleDbType.VarChar, 10).Value = "tipo";
    //command.Parameters.Add("veces", OleDbType.Decimal).Value = 1;
   command.Parameters.Add("sucursal", OleDbType.VarChar, 10).Value = "sucursal";
   command.Parameters.Add("transf", OleDbType.Date).Value = transf;
   command.Parameters.Add("forma", OleDbType.VarChar, 10).Value = "R";
   command.Parameters.Add("audito", OleDbType.VarChar, 10).Value = "Alta Automatica";
   command.Parameters.Add("newfld", OleDbType.VarChar, 10).Value = "newfld";

But once I tried it it show me an error let me know that I have Sintaxis Error. If I have a normal query runs ok but Iff or even When case, no working. any idea. Thanks

Answer 1

VFPOLEDB is an OLEDB provider and it is not SQL server. It doesn't support named parameters, and that is the reason you are getting a syntax error. You need to replace all those named parameters with positional arguments (all parameters statring with @ to ?). Since it doesn't support named parameters but positional only, you need to supply the same parameter like @numdoc more than once and you need to do that in order (? are matched by their positions). Your query as is a little confusing and doesn't look right for SQL server or another backend either (you are inserting sucursal for tipo? as an example), assuming those were typos, your query would look like (instead of string concatenation, verbatim strings are better for readability):

string query = @"INSERT INTO relojj 
        (id, numdoc, fecha, maquina, tipo, veces, 
        sucursal, transf, forma, audito, newfld )
        VALUES 
        (?,?,?,?,?,?
        IIF((SELECT COUNT(id) FROM relojj WHERE numdoc=? and fecha=?)%2=0 , 'E', 'S'),
        (SELECT COUNT(id) FROM relojj WHERE numdoc=? and fecha=?),
        ?,?,?,?,?)";
                           

command.Parameters.Add("id", OleDbType.VarChar, 5).Value = "1";
command.Parameters.Add("numdoc", OleDbType.Decimal).Value = 1;
command.Parameters.Add("fecha", OleDbType.Date).Value = fecha;
command.Parameters.Add("maquina", OleDbType.VarChar, 10).Value = "Reloj Rio Grande";
command.Parameters.Add("tipo", OleDbType.VarChar, 10).Value = "tipo";
command.Parameters.Add("veces", OleDbType.Decimal).Value = 1;

command.Parameters.Add("numdoc1", OleDbType.Decimal).Value = 1;
command.Parameters.Add("fecha1", OleDbType.Date).Value = fecha;

command.Parameters.Add("numdoc2", OleDbType.Decimal).Value = 1;
command.Parameters.Add("fecha2", OleDbType.Date).Value = fecha;

command.Parameters.Add("sucursal", OleDbType.VarChar, 10).Value = "sucursal";
command.Parameters.Add("transf", OleDbType.Date).Value = transf;
command.Parameters.Add("forma", OleDbType.VarChar, 10).Value = "R";
command.Parameters.Add("audito", OleDbType.VarChar, 10).Value = "Alta Automatica";
command.Parameters.Add("newfld", OleDbType.VarChar, 10).Value = "newfld";

EDIT: Note that parameter names are only for you the developer, all parameters are matched by their order of position.

EDIT2: Correction of the syntax, doesn't mean that this would run in VFP. You have constructs like:

IIF(select ...) % 2 = 0

That kind of queries do not work in VFP. To workaround, you would do those two select separately and use the results in your insert. Thus it becomes:

string query = @"INSERT INTO relojj 
        (id, numdoc, fecha, maquina, tipo, veces, 
        sucursal, transf, forma, audito, newfld )
        VALUES 
        (?,?,?,?,?,?,?,?,?,?,?)";
                           

    var cmd1 = new OleDbCommand("SELECT COUNT(id) FROM relojj WHERE numdoc=? and fecha=?", < your connection >);
cmd1.Parameters.Add("numdoc", OleDbType.Decimal).Value = 1;
cmd1.Parameters.Add("fecha", OleDbType.Date).Value = fecha;
    var cnt = Convert.ToInt32(cmd1.ExecuteScalar());


command.Parameters.Add("id", OleDbType.VarChar, 5).Value = "1";
command.Parameters.Add("numdoc", OleDbType.Decimal).Value = 1;
command.Parameters.Add("fecha", OleDbType.Date).Value = fecha;
command.Parameters.Add("maquina", OleDbType.VarChar, 10).Value = "Reloj Rio Grande";
command.Parameters.Add("tipo", OleDbType.VarChar, 10).Value = cnt%2 == 0?"E":"S";
command.Parameters.Add("veces", OleDbType.Decimal).Value = cnt;
command.Parameters.Add("sucursal", OleDbType.VarChar, 10).Value = "sucursal";
command.Parameters.Add("transf", OleDbType.Date).Value = transf;
command.Parameters.Add("forma", OleDbType.VarChar, 10).Value = "R";
command.Parameters.Add("audito", OleDbType.VarChar, 10).Value = "Alta Automatica";
command.Parameters.Add("newfld", OleDbType.VarChar, 10).Value = "newfld";