Microsoft Access Parameter Query Fails Occasionally

Question

I have a query that, basically, says this:

SELECT DISTINCT [DB_ID]
FROM [TableX]
WHERE ([ForeignKey]=@ForeignKey);

Once I have this, I return the first DB_ID (there should be only one).

The routine I've written for calling this is in C# so that I can get the database id for any table name, no matter what the DB_ID is named.

Most of the ForeignKey parameters are Integers, but some are String values. To add the parameter value, then, I use

Parameter.AddWithValue(ForeignKeyName, objValue);

This routine works fantastic with existing database ids; however, when a ForeignKey is not found, it returns 3 instead of a null.

I realize I could use the brute force technique and simply write two styles of SQL statements (one to accept an Integer, the other to accept a String) and avoid the parameter altogether, but I want to know and understand why this Parameterized routine is not working.

Could someone please explain to me why this does not work?

/// <summary>
/// Locates the Primary Key Value for a Table using the Table's Foreign Key
/// </summary>
/// <param name="tableName">Name of the Table to Delete from</param>
/// <param name="primaryKey">Name of the Primary Key in the Table</param>
/// <param name="foreignKey">Name of the Foreign Key in the Table</param>
/// <param name="id">Foreign Key Value to Search for in the Table</param>
/// <returns>Primary Key Database ID for this Table Record</returns>
List<int> tableSelectId(string tableName, string primaryKey, string foreignKey, object id) {
  string sqlText = string.Format("SELECT DISTINCT [{0}] " +
    "FROM [{1}] WHERE ([{2}]=@id);", primaryKey, tableName, foreignKey);
  DataTable table = new DataTable("IDs");
  OleDbDataAdapter da = new OleDbDataAdapter(sqlText, AccessConnection);
  da.SelectCommand.Parameters.AddWithValue("@id", id);
  try {
    da.Fill(table);
  } catch (OleDbException err) {
    clsLogger.LogException((Exception)err);
  }
  List<int> vals = new List<int>(table.Rows.Count);
  foreach (DataRow row in table.Rows) {
    vals.Add((int)row[0]);
  }
  return vals;
}

eof

Answer 1

Revisiting this: It turned out another developer was working on the same project and he had copied over a different database to work on which had an empty table.

One hour that table had records, the next hour it did not!

In short, the code snippet above works.