Nov 14, 2021

C# error with FoxPro

While running a query via System.Data.OleDb.OleDbDataAdapter on a FoxPro dbf, I sometimes get an error "Column 'Q37P2' is not found.", where the column name appears to be incrementing each time the error occurs. This is happening on adapterObject.Fill(datatableObject); call. It took me a while to figure out this was caused by not providing the adapter with enough parameters as the SQL statement has ? question mark placeholders for parameters.

As the error message isn't useful for knowing the cause of this bug, I added validation to our GetDataTable calling method:
C# //Via: LukeH on StackOverflow.
if (System.Diagnostics.Debugger.IsAttached)
{
    if (parameters.Length != sSQL.Length - sSQL.Replace("?", "").Length)
    {
        StringBuilder sbError = new StringBuilder();
        sbError.Append("Parameters count is different then the number of parameter placeholders (? marks).");
        sbError.Append(Environment.NewLine);
        sbError.Append("? count: ");
        sbError.Append((sSQL.Length - sSQL.Replace("?", "").Length).ToString());
        sbError.Append(Environment.NewLine);
        sbError.Append("Params: ");
        sbError.Append(parameters.Length);
        sbError.Append(Environment.NewLine);
        sbError.Append("SQL: ");
        sbError.Append(sSQL);

        System.Diagnostics.Debugger.Break();
        throw new ArgumentException(sbError.ToString());
    }
}