C#中使用VFPOLEDB带条件子查询插入DBF时语法错误求助

问题原因 #

VFPOLEDB的INSERT ... VALUES语法对嵌套子查询的表达式支持有限，直接在VALUES列表中使用带子查询的IIF（或CASE）会触发语法错误，而普通INSERT语句因无复杂嵌套表达式可正常执行。

解决方案1：在C#代码中提前计算参数值 #

先单独查询获取COUNT(id)的值，在代码里计算tipo和veces，再将这两个值作为普通参数传入INSERT语句，避开SQL中的复杂表达式：

步骤1：计算所需值 #

// 查询目标记录的count值
string countQuery = "SELECT COUNT(id) FROM relojj WHERE numdoc=@numdoc and fecha=@fecha";
OleDbCommand countCmd = new OleDbCommand(countQuery, connection);
countCmd.Parameters.Add("numdoc", OleDbType.Decimal).Value = 1;
countCmd.Parameters.Add("fecha", OleDbType.Date).Value = fecha;
int count = Convert.ToInt32(countCmd.ExecuteScalar());

// 计算tipo和veces字段值
string tipo = count % 2 == 0 ? "E" : "S";
int veces = count;

步骤2：修改INSERT语句和参数 #

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

OleDbCommand command = new OleDbCommand(query, connection);
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 = veces;
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";

command.ExecuteNonQuery();

解决方案2：改用INSERT ... SELECT语法 #

VFP的INSERT ... SELECT语法对复杂表达式和子查询支持更完善，将VALUES替换为SELECT语句，直接在SELECT中计算所需值：

string query = "INSERT INTO relojj (id, numdoc, fecha, maquina, tipo, veces, sucursal, transf, forma, audito, newfld ) " +
               "SELECT @id,@numdoc,@fecha,@maquina," +
               "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 FROM DUMMY";

注意：VFP的SELECT语句必须包含FROM子句，这里的DUMMY是VFP内置虚拟表；若环境中无此表，可替换为(SELECT * FROM relojj WHERE 1=0)，仅需保证返回空结构即可。

参数配置保持原写法，无需额外添加tipo和veces参数。

内容的提问来源于stack exchange，提问作者Luis Sanchez