Error building key for index ....path tag "nameIndex" DBF

Question

Hi guys I have the follow code

       string strCadenaConexion = @"X://Personal//Bases";  
        DBHelperVFPDBF db = new DBHelperVFPDBF(strCadenaConexion);

        foreach (var item in lista)
        {
         string valuesString = $"INSERT INTO relojjcopiaLuis" +
        $" (id, numdoc, fecha, maquina, tipo, veces,     sucursal, transf, forma, audito, newfld)" +
        $" VALUES ('{item.id}',{item.numdoc}," +
        $" DATETIME({item.fecha.Year},{item.fecha.Month},"+
        $" {item.fecha.Day},{item.fecha.Hour},{item.fecha.Minute},{item.fecha.Second})," +
        $" '{item.maquina}','{item.tipo}'," +  
        $" {item.veces},'{item.sucursal}',DATETIME({item.transf.Year},{item.transf.Month},"+
        $" {item.transf.Day},0,0,0)," +
        $" '{item.forma}','{item.audito}','{item.newfld}');";
                
        if (db.ProbarConexion())
        {               
         try
         {   
            using (OleDbCommand command = new OleDbCommand(valuesString))
            {
                db.EjecutarQuery(command);
                Log.Error($"Se pudo guardar la informacion {item.numdoc} {item.id} ");
            }                          
         }
         catch (Exception e)
         {
            Log.Error("No se pudo guardar la informacion " + e.Message);

        }          
       }
      
        await Task.Delay(delay/lista.Count); 
              
      }
        log.Information($"Se insertaron {lista.Count} nuevas fichadas al Fox")

If we check in the files managed by VFP, in my case, I refer to my table:

    relojj.dbf
    relojj.ftp
    relojj.cdx

If I delete the relojj.cdx file, I have no issues with inserting. However, with the relojj.cdx file, which contains the indices, I get the following error:

    Error building key for index "x:\personal\bases\relojjcopiaLuis.cdx" tag "Fecha".

The problem is in the cdx file, where the table indexes are stored. In this case, I have an index called "Fecha" with an expression that looks like this: TTOC(date) + STR(numdoc, 8, 0). This index and expression were created in Visual Fox Pro and are necessary. I cannot delete them. This C# project inserts data every hour, so it's not an option to insert data after deleting the .cdx file and then recreating the index as it is now. What's interesting and important to note is that if I remove this expression and leave a simple ASC as expression, there are no issues.

I read in some forums that it might be related to the number of characters, and in another, it might be due to the index reordering not completing because of date formats. However, the table shows the data as it was directly inserted from Visual Fox Pro. The latter makes more sense because if I remove the expression, as I mentioned earlier, there are no issues, as you can see in the field, the date is "18/10/2023 20:34:15".

Does anyone have any ideas on how to correct this?

How can I insert data to dbf with cdx create and having a tag with expression

Answer 1

The filenames you supplied does not match with the filename in the error message. That makes me think that you are skipping some details about your files. You are saying "relojj" but in code using "relojjcopiaLuis".

Anyway, could you try changing the code like this:

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

using (OleDbConnection cn = new OleDbConnection(@"Provider=VFPOLEDB;Data Source=x:\Personal\Bases"))
using (OleDbCommand command = new OleDbCommand(valuesString, cn))
{
    command.Parameters.Add("@id", OleDbType.VarChar);
    command.Parameters.Add("@numdoc", OleDbType.Integer);
    command.Parameters.Add("@fecha", OleDbType.DBTimeStamp);
    command.Parameters.Add("@maquina", OleDbType.VarChar);
    command.Parameters.Add("@tipo", OleDbType.VarChar);
    command.Parameters.Add("@veces", OleDbType.Integer);
    command.Parameters.Add("@sucursal", OleDbType.VarChar);
    command.Parameters.Add("@transf", OleDbType.Date);
    command.Parameters.Add("@forma", OleDbType.VarChar);
    command.Parameters.Add("@audito", OleDbType.VarChar);
    command.Parameters.Add("@newfld", OleDbType.VarChar);
    cn.Open();
    foreach (var item in lista)
    {
        command.Parameters["@id"].Value = item.id;
        command.Parameters["@numdoc"].Value = item.numdoc;
        command.Parameters["@fecha"].Value = item.fecha;
        command.Parameters["@maquina"].Value = item.maquina;
        command.Parameters["@tipo"].Value = item.tipo;
        command.Parameters["@veces"].Value = item.veces;
        command.Parameters["@sucursal"].Value = item.sucursal;
        command.Parameters["@transf"].Value = item.transf;
        command.Parameters["@forma"].Value = item.forma;
        command.Parameters["@audito"].Value = item.audito;
        command.Parameters["@newfld"].Value = item.newfld;

        try
        {
            command.ExecuteNonQuery();
            // Log error? Shouldn't it be log.Info?
            Log.Error($"Se pudo guardar la informacion {item.numdoc} {item.id} ");
        }
        catch (Exception e)
        {
            Log.Error("No se pudo guardar la informacion " + e.Message);
        }
    }
}

Answer 2

Well guys I couldnt find the way to sort this out so I managed the way to insert into a dbf. I make a copy from the original dbf files but just the .ftp and .dbf and I inserted my records on that table. Then from Visual Fox Pro I make a procedure that takes the original table.dbf and it makes an append from my copy table. I wish I could find the way to sort the cdx problem but nothing. Regards to all.